CodeGear の Blackfish SQL で、IFNULL() 関数を使おうとしたら、Microsoft SQL Server の ISNULL() 関数との違いにはまりました。その解決策の一つのメモ。

*

欲しい関数は、次の様なものです。

IxNULL(A, B) と呼び出したとき、A が null であれば B を、null で無ければ A を返す。

*

これに該当する関数は、SQL Server では ISNULL、Blackfish SQL では IFNULL です。

Microsoft SQL Server の Transact-SQL の ISNULL についてのリファレンスはこちら。
CodeGear Blackfish SQL の IFNULL についてのリファレンスはこちら。

*

さて、ここからがはまったところです。

私は、普段 SQL Server での開発が多いため、ISNULL を利用します。
今回の開発では Blackfish SQL を使用しているので、リファレンスから IFNULL を使おうとしたのです。

しかし、「オブジェクト参照が～」とか「Timestamp 型を cast できない～」など言われ、動きません。

記述はリファレンスの通りにしたはずです。

・・・

IFNULL(expression, value) と書かれています。

・・・

！！「value」！！なんです！！

つまり、式(expression)は記述できないのですね。

私がやろうと思っていたこととは、次のような内容でした。

SELECT * FROM [テーブル名] T
WHERE T.[xx]={FN IFNULL(:XX_VALUE, T.[xx])}

検索機能などの実装時、全検索も必要な場合によく使うコードだと思います。

パラメータが無い場合(:XX_VALUE が NULL の場合)は、「T.[XX]=T.[XX]」となることで WHERE 句の縛りが無くなるわけですね。

しかし、Blackfish SQL の場合、上記コードは動作しません。

IFNULL 関数の第二引数が「式」だからです。

*

と言うことで、CASE 式でこの問題は解決することにしました。
上記の SQL を、次の様に書き換えます。

SELECT * FROM [テーブル名] T
WHERE T.[xx]=
CASE {FN IFNULL(:XX_VALUE, -1)}
WHEN -1 THEN T.[xx]
ELSE :XX_VALUE
END

この例では、T.[XX] は外部の主キーで開始値 0 の AUTO INCREMENT 型だったので、存在しない値(-1)を IFNULL 関数が返すようにしました。

*

ちなみに、WHERE 句を次の様に書き換えても、動作しません。

WHERE T.[xx]=
CASE :XX_VALUE
WHEN NULL THEN T.[xx]
ELSE :XX_VALUE
END

*

Blackfish SQL は非常に軽いデータベースで良いのですが、SQL や管理ツール含めて、まだまだ使いづらい現実がありますね。

もっと情報が欲しいところです。