Bug with complex SQL?

[pwatel]

Delphi 2009 (IBDAC 3 with the fix you sent me)
see Ticket ID: 5065
Department: Dac Team
firebird 2.1.2 dialect 1 charset = NONE (I know it is dumb but legacy)
therefore useunicode=false

hello again
this query works perfectly in Maestro SQL factory and EMS firebird manager but crashes with your components
it seems it does not like extract ...

SELECT I.MAGAZINECODE,EXTRACT(MONTH FROM I.INVOICINGDATE) as IMONTH,
SUM(A.FINALPRICE) AS EUROS,
SUM(A.PAGEFACTOR) AS PAGEFACTOR
FROM ISSUES I INNER JOIN SALESADDS A ON I.MAGAZINECODE=A.MAGAZINECODE AND
I.ISSUENUMBER=A.ISSUENUMBER
WHERE (A.Deleted = 0) AND
(EXTRACT(YEAR FROM I.INVOICINGDATE)=2009)
GROUP BY I.MAGAZINECODE,
EXTRACT(MONTH FROM I.INVOICINGDATE)
ORDER BY EXTRACT(MONTH FROM I.INVOICINGDATE), I.MAGAZINECODE

I get
Dynamic SQL Error
SQL error code = -104
Token unknown - line 1, column 23
.
1) wrong fib client dll ?
2) I did not select the right options ?
3) bug ?
4) ???

Thanks regards
Philippe Watel

[Plash]

Probably, this problem caused the DefaultValues option set to True (the previous bug with database with the NONE charset was caused by the DefaultValues option).

Try to remove tables and fields from your query that are not required to reproduce the problem. Then send us a complete small sample that demonstrates the problem.

[pwatel]

hello
I did turn off Default value but on a new Query we get the same result
on a new complex SQL (which runs fine with EMS DB manager and the like) it crashes
I tested it with also a UTF-8 database and it crashes as well
so it has nothing to do whith NONE charset

SELECT DISTINCT S.COMPANYKEY, S.SUBSCRIBERKEY,
S.LASTNAME || ', ' || S.FIRSTNAME AS FULLNAME, S.TITLE, M.NAME || ' - '||
EXTRACT(MONTH FROM K.ENDDATE) ||'/'|| EXTRACT(YEAR FROM K.ENDDATE) ||' '||
' (' ||CAST(CAST(K.COST AS INTEGER) AS CHAR(3)) ||') '||
COALESCE(K.STATUS, 'No Status')
AS SUBSCRIPTIONINFO FROM SUBSCRIBERS S
INNER JOIN SUBSCRIPTIONS K ON (S.SUBSCRIBERKEY=K.SUBSCRIBERKEY)
INNER JOIN MAGAZINES M ON (M.MAGAZINECODE=K.MAGAZINECODE)
WHERE (S.COMPANYKEY = :COMPANYKEY) AND
(K.STOPSENDING = 0) AND
(K.ENDDATE > '2009-06-30')
ORDER BY S.LASTNAME, K.MAGAZINECODE, K.ENDDATE

with EXTRACT it crashes althought the SQL is different it indicates the
same column value (23) and error message
Dynamic SQL Error
SQL error code = -104
Token unknown - line 1, column 23
.
you guys do not like EXTRACT function ????? or what
I hope you can find an answer to this bug because I am stuck!!
thanks
PW

[Plash]

Does the error occurs if you leave one table in the FROM list and reduce the number of fields in the SELECT clause?

[pwatel]

I will send a small demo with a database in the support section