grid quick search GROUP_CONCAT

Scriptcase 9 Bugs
#1

Hello,

I receive a error message if i define a field in sql statement with GROUP_CONCAT and quicksearch

<!DOCTYPE HTML PUBLIC “-//W3C//DTD HTML 4.01 Transitional//EN”
“http://www.w3.org/TR/1999/REC-html401-19991224/loose.dtd”>
<HTML DIR=‘LTR’>
<HEAD>
<TITLE></TITLE>
<META http-equiv=“Content-Type” content=“text/html; charset=utf-8” />
<META http-equiv=“Expires” content=“Fri, Jan 01 1900 00:00:00 GMT”>
<META http-equiv=“Last-Modified” content=“Sun, 19 May 2019 01:24:39” GMT">
<META http-equiv=“Cache-Control” content=“no-store, no-cache, must-revalidate”>
<META http-equiv=“Cache-Control” content=“post-check=0, pre-check=0”>
<META http-equiv=“Pragma” content=“no-cache”>
<link rel=“shortcut icon” href="…/_lib/img/grp__NM__ico__NM__favicon.ico">
<link rel=“stylesheet” type=“text/css” href="…/_lib/css/Rhino/Rhino_error.css" />
<link rel=“stylesheet” type=“text/css” href="…/_lib/css/Rhino/Rhino_errorLTR.css" />
</HEAD>
<BODY>
<TABLE class=“scErrorTable” cellspacing=“0” cellpadding=“0” align=“center”>
<TR>
<TD class=“scErrorTitle” align=“left”>Fehler</TD>
</TR>
<TR>
<TD class=“scErrorMessage” align=“center”>Fehler beim Datenbankzugriff:<BR>[SIZE=14px]Invalid use of group function[/SIZE]<BR>select count(*) from Contacts INNER JOIN Contacts_SecurityGroups ON Contacts.ContactID = Contacts_SecurityGroups.ContactID LEFT JOIN Addresses ON Contacts.ContactID = Addresses.ContactID where (Contacts.CreatedBy like ‘%Inhaber%’ or Contacts.ChangedBy like ‘%Inhaber%’ or Contacts.BusinessContact like ‘%Inhaber%’ or Contacts.Firstname like ‘%Inhaber%’ or Contacts.Lastname like ‘%Inhaber%’ or Contacts.Phone like ‘%Inhaber%’ or Contacts.Email like ‘%Inhaber%’ or Contacts.Comments like ‘%Inhaber%’ or Addresses.Address like ‘%Inhaber%’ or [SIZE=14px]GROUP_CONCAT(Contacts_SecurityGroups.Security_Group_ID [/SIZE]SEPARATOR ‘;’) in (‘Inhaber’)) group by Contacts.ContactID</TD>
</TR>
</TABLE></BODY>
</HTML>

#2

Hello, @salvatore

We really appreciate your contact.

Please, tell me the steps you’re doing to get to this behavior, so then I could try to replicate it in my environment.

#3

for example grid SQL

SELECT
Contacts.ContactID,
GROUP_CONCAT(Contacts_SecurityGroups.Security_Group_ID SEPARATOR “;”) AS SecurityGroupID
FROM Contacts

INNER JOIN Contacts_SecurityGroups ON Contacts.ContactID = Contacts_SecurityGroups.ContactID

GROUP BY Contacts.ContactID

in the grid is working fine to show all groupid’s in one field, thats works by sql statement with delimiter ; for this field.

if i add the field for quicksearch and then search over quicksearch i receive the error.

#4

Any news or solutions?

#5

On my computer i dont have this QSEARCH-error using a grid with groupconcat/join. I can add or remove fields from Qsearch.

Try a simpe SQL to see if you have the error. Then add the INNER JOIN and test. Finally make the GROUP BY and test.

Another thing to try is to use the GROUP BY within SC and not in the SQL.