Firebird - How to use "(? is null)" for selecting blank parameters

Question

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/langrefupd25-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.

SELECT C.COSTS_ID,
       C.AREA_ID,
       S.SUB_NUMBER,
       S.SUB_NAME,
       TP.PHASE_CODE,
       TP.PHASE_DESC,
       TI.ITEM_NUMBER,
       TI.ITEM_DESC,
       TI.ORDER_UNIT,
       C.UNIT_COST,
       TI.TLPE_ITEMS_ID
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

Any help is greatly appreciated.

I read somewhere that all options in a drop down list can be shown when a blank parameter is selected by doing a WHERE FIELD LIKE ‘%’ || ? || ‘%’ but so far I haven't been able to get that to work, I keep getting an "unexpected end of command" error. — user2452582
You are using Firebird 2.5? If you are, which driver (+ version) are you using? It might be that the driver itself doesn't support the SQL_NULL datatype that is required for using ? IS NULL without casts — Mark Rotteveel

Mark Rotteveel Mark Rotteveel · Accepted Answer · 2013-06-05T16:10:58

If you are not using Firebird 2.5 (but version 2.0 or higher), or if you are using a driver that doesn't support the SQL_NULL datatype introduced in Firebird 2.5, then you need to use an explicit CAST, eg;

SELECT *
FROM TLPE_ITEMS TI
WHERE TI.ITEM_NUMBER = ? OR CAST(? AS INTEGER) IS NULL

This will identify the second parameter as an INTEGER to the driver (and to Firebird), allowing you to set it to NULL.

Now the faq you reference mentions cast the value to appropriate datatype, what they mean is that you should not cast to a data type that might result to conversion errors if it isn't null.

In my example I cast to INTEGER, but if the values are actually strings and you use say "IX0109302" as a value, you will get a conversion error as it isn't an appropriate INTEGER. To prevent that, you would need to cast to a (VAR)CHAR of sufficient length (otherwise you get a truncation error).

If you are using Firebird 1.5 or earlier this trick will not work, see CORE-778, in that case you might get away with something like TI.ITEM_NUMBER = ? OR 'T' = ?, where you set the second parameter to either 'T' (true) or 'F' (false) to signal whether you want everything or not; this means that you need to move the NULL detection to your calling code.

Firebird - How to use "(? is null)" for selecting blank parameters

1 Answers