I am working with an Excel Report linked to a Firebird 2.0 DB and I have various parameters linked to cell references that correspond to drop down lists.
If a parameter is left blank, I want to select all the possible options. I am trying to accomplish this by putting ... WHERE... (? is null), as described in http://www.firebirdsql.org/refdocs/l...5-sqlnull.html , but I get an "Invalid Data Type" error.
I found some Firebird documentation (http://www.firebirdfaq.org/faq92/ ) where it talks about this error, but it states that "The solution is to cast the value to appropriate datatype, so that all queries return the same datatype for each column." and I'm not quite sure what that means in my situation.
I read somewhere that all options in a drop down list can be shown when a blank parameter is selected by doing a
FROM TLPE_ITEMS TI
INNER JOIN TLPE_PHASES TP ON TI.TLPE_PHASES_ID = TP.TLPE_PHASES_ID
LEFT OUTER JOIN COSTS C ON C.TLPE_ITEMS_ID = TI.TLPE_ITEMS_ID
LEFT OUTER JOIN AREA A ON C.AREA_ID = A.AREA_ID
LEFT OUTER JOIN SUPPLIER S ON C.SUB_NUMBER = S.SUB_NUMBER
WHERE ((C.AREA_ID = 1 OR C.AREA_ID = ?) OR **(? IS NULL))**
AND ((S.SUB_NUMBER = ?) OR **(? IS NULL))**
AND ((TI.ITEM_NUMBER = ?) OR **(? IS NULL))**
AND ((TP.PHASE_CODE STARTING WITH ?) OR **(? IS NULL))**
ORDER BY TP.PHASE_CODE
but so far I haven't been able to get that to work either, I keep getting an "unexpected end of command" error
WHERE FIELD LIKE ‘%’ || ? || ‘%’
Any help is greatly appreciated.