Proper syntax to use in flyspeed
I have used this code (see below) in report builder, but it will not work in flyspeed? Do I need to use a different syntax like MySQL? I can't figure out why it works in report builder, but no flyspeed. Does flyspeed not use all sql functions? Need help.
SELECT
CASE
WHEN ACTNUMBER_1 = '000' THEN 'EIM1'
WHEN ACTNUMBER_1 = '200' THEN 'EIM2'
WHEN ACTNUMBER_1 = '300' THEN 'EIM3'
ELSE ' '
END AS Plant,
CONVERT(DATE,CAST(YEAR(Trx_Date)
AS VARCHAR(4))+'-'+ CAST(MONTH(Trx_Date)
AS VARCHAR(2))+'-01') AS DATE,
SUM(Amount) As Dollars
FROM GPGL$
GROUP BY ACTNUMBER_1, CONVERT(DATE,CAST(YEAR(Trx_Date)
AS VARCHAR(4))+'-'+ CAST(MONTH(Trx_Date)
AS VARCHAR(2))+'-01')
ORDER BY ACTNUMBER_1, CONVERT(DATE,CAST(YEAR(Trx_Date)
AS VARCHAR(4))+'-'+ CAST(MONTH(Trx_Date)
AS VARCHAR(2))+'-01')
SELECT
CASE
WHEN ACTNUMBER_1 = '000' THEN 'EIM1'
WHEN ACTNUMBER_1 = '200' THEN 'EIM2'
WHEN ACTNUMBER_1 = '300' THEN 'EIM3'
ELSE ' '
END AS Plant,
CONVERT(DATE,CAST(YEAR(Trx_Date)
AS VARCHAR(4))+'-'+ CAST(MONTH(Trx_Date)
AS VARCHAR(2))+'-01') AS DATE,
SUM(Amount) As Dollars
FROM GPGL$
GROUP BY ACTNUMBER_1, CONVERT(DATE,CAST(YEAR(Trx_Date)
AS VARCHAR(4))+'-'+ CAST(MONTH(Trx_Date)
AS VARCHAR(2))+'-01')
ORDER BY ACTNUMBER_1, CONVERT(DATE,CAST(YEAR(Trx_Date)
AS VARCHAR(4))+'-'+ CAST(MONTH(Trx_Date)
AS VARCHAR(2))+'-01')
First, please let me know (1) what version of FlySpeed SQL Query you are using now? Or you use not the "SQL Query" tool, but some other tool (Data Export, Db Migrate)?
What you mean by "use a different syntax"? I don't know (2) what syntax you are using now. Also please let me know for (3) what database server your query is written for.
Thank you in advance for detailed explanation of your problem.