[pgsql-jp: 30866] Re: to_numberでFormat エラーになります

[T.Suzuki]

鈴木＠KEGと申します。

NULL値 と '' （0桁の文字列）は、違うという事をまず始めに。

birthdayがNULL値の場合、substr(birthday, 5, 2)ではNULLを返しますが、
birthdayが '' (0桁の文字列)の場合、'' を返します。

Wrote: ごとうさん
-省略-
> > > AND customer_base_mst.st_cd in ('xxxxx')
> > > AND (to_number(substr(birthday,5,2),'99') = 9 );

従って、上記のSQL文では、to_number()に '' が渡される可能性があります。
谷口、石田さんが実験された通り、to_number()の引数には NULLは問題ない
ですが、'' ではエラーとなります。

ちなみに、NULLと '' (0桁の文字列)を手軽に見分ける方法として、
私は length()で調べてます。

 SELECT  length(birthday);
 length
-------
        0
(1 row)

0と表示されるレコードがあれば、'' (0桁の文字列)があるのが解ります。

Wrote: 石田さん
> > substr(birthday, 5, 2) が空白などを返さないかを調べて
> > みてはどうでしょうか？
というのは、NULL値ではなく、「'' が無いですか？」という事だと思います。

> データベースを確認したところ、確かにNullが入っているデータもあるのですが、
> テスト環境にもNull値があるのです。（というより、テスト環境のほうが圧倒的に
多
> いです。）
> しかし、テスト環境では、エラーにはならず、正しい件数が返ってきます。

予測ですが、運用環境のbirthdayに '' が入っているのでは？
#データを移行する際や、アプリケーションの問題でNULLじゃなく、
# '' を入力してハマル事が良くあります。

余計なお世話かもしれませんが、to_numberは私もハマッタ経験があるので、
参考までに。

(to_number(substr(birthday,5,2),'99') = 9 )
　↓
(date_part('month', to_date(birthday, 'YYYYMMDD')) = '9')

のような感じでやれば、birthday が NULLだろうと '' だろうとSQLがエラー
になる事はないかと思います。
 -----------------------------------------
      鈴木 徹 (SUZUKI Toru)
      KENWOOD ENGINEERING CORPORATION
      E-mail:t_suzuki ＠ kenwood-eng.co.jp
 -----------------------------------------