Group functions in Browse SQL & Search

[vario]

I have a problem with using the search field on forms with browse SQL containing group functions. This is not a big problem, and it may be nothing can be done except to exclude group functions found in browse SQL when composing the query?

e.g

browse SQL is

Code: Select all

select supplier_id, sup_name, sup_tel, sum(pur_stock), sum(if(date_format(pur_date,'%Y%m') = date_format(curdate(),'%Y%m'), pur_cost*pur_qty, 0)) from supplier
left join purchase on (supplier_id = pur_supplier_id and pur_vehicle_id is null)
group by supplier_id
order by sup_name

If I enter anything into the search field I get two entries in zzzzsys_debug:

Code: Select all

[0] : ===PDO MESSAGE===

SQLSTATE[HY000]: General error: 1111 Invalid use of group function

===SQL===========

SELECT COUNT(*) FROM (SELECT supplier_id,sup_name,sup_tel,sum(pur_stock),sum(if(date_format(pur_date,'%Y%m') = date_format(curdate(),'%Y%m'), pur_cost*pur_qty, 0))
 from supplier left join purchase on (supplier_id = pur_supplier_id and pur_vehicle_id is null)
 WHERE 1 AND ( (CONVERT(sup_name USING utf8) LIKE "%test%" OR CONVERT(sup_tel USING utf8) LIKE "%test%" OR CONVERT(sum(pur_stock) USING utf8) LIKE "%test%" OR CONVERT(sum(if(date_format(pur_date,'%Y%m') = date_format(curdate(),'%Y%m'), pur_cost*pur_qty, 0)) USING utf8) LIKE "%test%") )
 group by supplier_id
 order by sup_name) nuTCount

===BACK TRACE====

/var/www/forte/core/nuform.php - line 1240 (nuRunQuery)

/var/www/forte/core/nuform.php - line 452 (nuBrowseRows)

/var/www/forte/core/nuapi.php - line 92 (nuGetFormObject)

Code: Select all

[0] : ===PDO MESSAGE===

SQLSTATE[HY000]: General error: 1111 Invalid use of group function

===SQL===========

SELECT supplier_id,sup_name,sup_tel,sum(pur_stock),sum(if(date_format(pur_date,'%Y%m') = date_format(curdate(),'%Y%m'), pur_cost*pur_qty, 0))
 from supplier left join purchase on (supplier_id = pur_supplier_id and pur_vehicle_id is null)
 WHERE 1 AND ( (CONVERT(sup_name USING utf8) LIKE "%test%" OR CONVERT(sup_tel USING utf8) LIKE "%test%" OR CONVERT(sum(pur_stock) USING utf8) LIKE "%test%" OR CONVERT(sum(if(date_format(pur_date,'%Y%m') = date_format(curdate(),'%Y%m'), pur_cost*pur_qty, 0)) USING utf8) LIKE "%test%") )
 group by supplier_id
 order by sup_name LIMIT 0, 80

===BACK TRACE====

/var/www/forte/core/nuform.php - line 1246 (nuRunQuery)

/var/www/forte/core/nuform.php - line 452 (nuBrowseRows)

/var/www/forte/core/nuapi.php - line 92 (nuGetFormObject)

[kev1n]

Does it help to encapsule the SQL within a subquery?

Code: Select all

SELECT * FROM (
    SELECT 
        supplier_id, 
        sup_name, 
        sup_tel, 
        SUM(pur_stock), 
        SUM(IF(DATE_FORMAT(pur_date, '%Y%m') = DATE_FORMAT(CURDATE(), '%Y%m'), pur_cost * pur_qty, 0))
    FROM 
        supplier
    LEFT JOIN 
        purchase ON (supplier_id = pur_supplier_id AND pur_vehicle_id IS NULL)
    GROUP BY 
        supplier_id
) AS T 
ORDER BY 
    sup_name

[vario]

Encapsulated query doesn't seem to work as I get no rows on the browse screen. The query looks OK when run against the database, so column names must be getting mangled?

[kev1n]

Can you paste the generated SQL (Options Menu -> Form Info) here?

[steven]

vario,

Perhaps you could create a VIEW for the Browse SQL and filter on that.

Steven

[vario]

The SQL from Options->Form Info is:

Code: Select all

SELECT supplier_id,sup_name,sup_tel,sum(pur_stock),sum(if(date_format(pur_date,'%Y%m') = date_format(curdate(),'%Y%m'), pur_cost*pur_qty, 0))
 FROM supplier LEFT JOIN purchase ON (supplier_id = pur_supplier_id AND pur_vehicle_id IS NULL)
 WHERE 1 AND ( (CONVERT(sup_name USING utf8) LIKE "%Carlyle%" OR CONVERT(sup_tel USING utf8) LIKE "%Carlyle%" OR CONVERT(sum(pur_stock) USING utf8) LIKE "%Carlyle%" OR CONVERT(sum(if(date_format(pur_date,'%Y%m') = date_format(curdate(),'%Y%m'), pur_cost*pur_qty, 0)) USING utf8) LIKE "%Carlyle%") )
 GROUP BY supplier_id
 ORDER BY sup_name

I will try the view perhaps sometime but this isn't a big problem so low on my list of things to do!

Neil.