Grid with Union & Sum.

Applications Grid / Reports
#1

Hi,

I have this working code running in phpmyadmin:-


SELECT * FROM (
SELECT
 field01, SUM(field02) AS tmpfield
FROM
 `file01`
WHERE
 field04 = 1 AND field05 < 2 AND field02 > 0
GROUP BY
 field01
UNION
SELECT
 field01, SUM(field03) AS tmpfield
FROM
 `file01`
WHERE
 field04 = 1 AND field05 < 2 AND field02 = 0
GROUP BY
 field01
) tmpfile
GROUP BY
 field01
ORDER BY
 tmpfield DESC
Limit
 var01

It shown the result as expected.

My Question is, this syntax seems not working in scriptcase grid.

This is for ranking, the var01 (10) is a variable preset in a control file.

Any suggestion?

Regards,
CK

#2

Re: Grid with Union & Sum.

If you need just the data, create procedure in MySql, that returns what u need, inside SC you will be very limited in terms of what is supported into the default form data select (I did find long time ago, that unions is one thing they don’t really support).

#3

Re: Grid with Union & Sum.

Try to create a simple view in your database.