火鸟 - 如何使用"(? is null)"选择空白参数

Question

我正在使用一个链接到Firebird 2.0 DB的Excel报告，我有各种参数链接到对应于下拉列表的单元格引用。

如果一个参数为空，我想选择所有可能的选项。我正试图通过把…在那里……(?，如http://www.firebirdsql.org/refdocs/langrefupd25-sqlnull.html中所述，但我得到一个"无效的数据类型"错误。

我找到了一些Firebird文档(http://www.firebirdfaq.org/faq92/)，其中讨论了这个错误，但它指出"解决方案是将值转换为适当的数据类型，以便所有查询为每列返回相同的数据类型。"我不太确定这在我的情况下意味着什么。

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

Answer 1

如果您没有使用Firebird 2.5(但版本2.0或更高)，或者如果您使用的驱动程序不支持Firebird 2.5中引入的SQL_NULL数据类型，那么您需要使用显式CAST，例如;

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

这将把第二个参数标识为驱动程序(和Firebird)的INTEGER，允许您将其设置为NULL。

现在您引用的faq提到将值强制转换为适当的数据类型，它们的意思是您不应该强制转换为可能导致转换错误的数据类型，如果它不是null。

在我的例子中，我转换为INTEGER，但如果值实际上是字符串，你使用说"IX0109302"作为一个值，你会得到一个转换错误，因为它不是一个合适的INTEGER。为了防止这种情况，您需要强制转换为足够长度的(VAR)CHAR(否则您会得到截断错误)。

如果你使用的是Firebird 1.5或更早的版本，这个技巧将不起作用，请参阅CORE-778，在这种情况下，你可能会得到像TI.ITEM_NUMBER = ? OR 'T' = ?这样的东西，在那里你将第二个参数设置为'T' (true)或'F' (false)来表示你是否想要一切;这意味着您需要将NULL检测移动到您的调用代码中。