At 04:46 a.m. 10/03/2015, Pat Coleman PColeman@... [firebird-support] wrote:

>Hi
>
>I am having trouble writing a query in iReport using Firebird SQL
>
>I can get the query to work in MS Access, but I�m not familiar with Firebird SQL, seems to be different .

Yes, Access "SQL" is not standard SQL.

>The access version of my query that works is
>
>
>SELECT CUSTOMFIELDVIEW.INFO AS OtherName, SO.NUM
>FROM CUSTOMFIELDVIEW INNER JOIN (SO INNER JOIN CUSTOMER ON SO.CUSTOMERID = CUSTOMER.ID) ON CUSTOMFIELDVIEW.RECORDID = CUSTOMER.ID
>WHERE (((SO.NUM)="763905") AND ((CUSTOMFIELDVIEW.CFID)=78));

Try this:

SELECT CUSTOMFIELDVIEW.INFO AS OtherName, SO.NUM
FROM CUSTOMFIELDVIEW
JOIN CUSTOMER ON CUSTOMFIELDVIEW.RECORDID = CUSTOMER.CUSTOMER.ID
JOIN SO ON SO.CUSTOMERID = CUSTOMER.CUSTOMER.ID
WHERE SO.NUM='763905' AND CUSTOMFIELDVIEW.CFID=78;

Take note that STRINGS in SQL are enclosed in single quotes (apostrophes) not double quotes. Double quotes have a totally different function in SQL.


Helen Borrie, Support Consultant, IBPhoenix (Pacific)
Author of "The Firebird Book" and "The Firebird Book Second Edition"
http://www.firebird-books.net
__________________________________________________________________