Calculated field error in select statement about gssql HOT 5 CLOSED

My field alias in the select is only used for display in column titles and so can't be used in calculated fields. I don't think that mySQL allows for this syntax either. I would try using a sub-query for this field (which is supported). You could also try to just use (count(d.device_id) - min(a.number_of_active_devices) ) as sss
See issue #11

from gssql.

Thank you for getting back. Really appreciate it.

Your GS example works fine but it doesn't work on my file :

"Select d.account_name, d.account_id,count(d.device_id) as devicetotal,a.account_name,a.account_id,min(a.number_of_active_devices) as accounttotal, (count(d.device_id) - min(a.number_of_active_devices)) as sss
from d full join a on d.account_id=a.account_id group by d.account_name, d.account_id,a.account_name,a.account_id"

it gives the following error

Error: Invalid select field: (count(d.device_id) - min(a.number_of_active_devices)) (line 2835).

from gssql.

sentolga,

In my testing I have found several issues in and around JOIN and GROUP BY. For now doing a calculation on a aggregate field in a GROUP BY is failing. See: #54 , #55 and #56

from gssql.

Original issue raised was not being able to use a field alias in a calculation. This is not support in gsSQL() (or in mySQL as well). However, in this case, the fix should have been doing a calculated field that involved two aggregate functions in a GROUP BY statement. This calculation was not supported at that time. Now a basic calculation using aggregate fields are supported.

from gssql.

Thank you so much fo your effort !

from gssql.