Oracle Technology Network (OTN) Japan - 掲示板 : TO_DATE関数の不思議なエラーに困っ ...

HD

投稿数: 19
登録日時: 03/07/17

TO_DATE関数の不思議なエラーに困っています（ORA-01843,ORA-01839）
投稿時刻: 2008/07/25 16:19

返信

以下のようなTO_DATE関数の不思議なエラーに困っています、どなたか解決策をご教授いただけるととても助かります。
宜しくお願い致します。

次のようにNUMBER型でキー年月日を持っているテーブルがあります。

CREATE TABLE HEADERTABLE
(
...
HEADER_YY NUMBER(2) NOT NULL,
HEADER_MM NUMBER(2) NOT NULL,
HEADER_DD NUMBER(2) NOT NULL,
...
)

このテーブルのキー年月日をTO_DATEして、WHERE句で使用すると、
「ORA-01843: 指定した月が無効です。」
が発生するのでデータを精査してみましたが全て異常はありません。
試しに次のようなSQLを発行してみると・・

？SELECT句でTO_DATE

SELECT
TO_DATE( TO_CHAR(HEADER_YY,'00') || TO_CHAR(HEADER_MM,'00') || TO_CHAR(HEADER_DD,'00'), 'RRMMDD' ) HEADER_DATE
FROM
HEADERTABLE
WHERE
HEADER_YY = 1
AND HEADER_MM = 1
AND HEADER_DD = 1

→ 問題なく抽出できる

？インラインクエリーでTO_DATE

SELECT COUNT(*)
FROM
(
SELECT
TO_DATE( TO_CHAR(HEADER_YY,'00') || TO_CHAR(HEADER_MM,'00') || TO_CHAR(HEADER_DD,'00'), 'RRMMDD' ) HEADER_DATE
FROM
HEADERTABLE
WHERE
HEADER_YY = 1
AND HEADER_MM = 1
AND HEADER_DD = 1
)
WHERE
HEADER_DATE = SYSDATE

→ 「ORA-01843: 指定した月が無効です。」が発生する

全く原因が判らずとても困っています。
Oracle9i Enterprise Edition Release 9.2.0.8.0 です。
宜しくお願い致します。

うぇいく

投稿数: 5,722
登録日時: 99/12/20

Re: TO_DATE関数の不思議なエラーに困っています（ORA-01843,ORA-01839）
投稿時刻: 2008/07/25 16:35

HD さんへの返信です。

返信

ちょっと気になるのは、(3,4,5)をつなげた文字列は、'030405'ではなく、' 03 04 05'になっているんじゃないかというぐらいでしょうか。
# 符号用に桁がとられます。

'00'から、'FM00'にしたら、かわりませんかね。
# 未確認　かつ、未確実。
-- CMN v0.50fβ うぇいく --

HD

投稿数: 19
登録日時: 03/07/17

Re: TO_DATE関数の不思議なエラーに困っています（ORA-01843,ORA-01839）
投稿時刻: 2008/07/25 16:48

うぇいくさんへの返信です。

返信

うぇいく様
ご返信ありがとうございます。
ただ、符号用の桁の問題でもないようで・・

？のSELECT句でTO_DATEせずに、
TO_CHAR(HEADER_YY,'00') || TO_CHAR(HEADER_MM,'00') || TO_CHAR(HEADER_DD,'00')
とした場合と、
TO_CHAR(HEADER_YY,'FM00') || TO_CHAR(HEADER_MM,'FM00') || TO_CHAR(HEADER_DD,'FM00')
とした場合の結果はともに全て、

010101

となります。そしてTO_DATEも問題ありません。
これをそのままインラインクエリーやWHERE句で使用するとエラーになってしまうのです・゜゜・(×_×)・゜゜・

ikms_kd

投稿数: 37
登録日時: 03/05/22

Re: TO_DATE関数の不思議なエラーに困っています（ORA-01843,ORA-01839）
投稿時刻: 2008/07/25 16:55

HD さんへの返信です。

返信

TO_CHARで0埋めすると後ろにスペースが付加されることを知らずにやっていて
ひどい思いをした過去を思い出しました・・

念のため今やってみましたが、やはりスペースが付加されていました。


 select TO_CHAR(1,'00') || TO_CHAR(1,'00') || TO_CHAR(1,'00') from dual
 union all
 select TO_CHAR(1,'FM00') || TO_CHAR(1,'FM00') || TO_CHAR(1,'FM00') from dual;

TO_CHAR(1
---------
 01 01 01
010101

それとも0付加されるかどうかは環境などによるものなのでしょうか？
浅学にして知りません・・申し訳ありません。

インラインクエリでなければできるということなので、本質的な解決ではないかもしれませんが
0埋めにTO_CHARではなくLPADを使ってはどうでしょうか。
(当時、自分はそうやって回避しました)

HD

投稿数: 19
登録日時: 03/07/17

Re: TO_DATE関数の不思議なエラーに困っています（ORA-01843,ORA-01839）
投稿時刻: 2008/07/25 17:41

ikms_kd さんへの返信です。

返信

ikms_kd 様
ご返信ありがとうございます。
なるほど、実際こちらでも同じSQLではスペースが付加されました。
ただ・・
(1)でも(2)でも、またWHERE句でも、HEADER_YY等のカラム名ではなく直接固定値を指定して実行すると、エラーにならず正常に処理されるのです。
以下前掲と違うパターンで試した結果です。

(1)(2)及びWHERE句にて下記パターンは全て'010101'（スペース無し）となり正常に処理されます。
TRIM(TO_CHAR(1,'FM00'))
LPAD(TRIM(TO_CHAR(1)),2,'0')
LPAD(TRIM(TO_CHAR(1,'FM00')),2,'0')

(1)で下記パターンは全て'010101'（スペース無し）、(2)とWHERE句ではエラー発生。
TRIM(TO_CHAR(HEADER_YY,'FM00'))
LPAD(TRIM(TO_CHAR(HEADER_YY)),2,'0')
LPAD(TRIM(TO_CHAR(HEADER_YY,'FM00')),2,'0')

固定値の1とは扱いが異なるのでしょうか・・

HD

投稿数: 19
登録日時: 03/07/17

Re: TO_DATE関数の不思議なエラーに困っています（ORA-01843,ORA-01839）
投稿時刻: 2008/07/25 17:54

HD さんへの返信です。

返信

追記です。
(2)のWHERE句を削除して実行すると、エラーは発生しません。

補: 最初の投稿の1つ目の？が(1)、2つ目の？が(2)です。
　　(文字化けして編集不可になってしまいました)

うぇいく

投稿数: 5,722
登録日時: 99/12/20

Re: TO_DATE関数の不思議なエラーに困っています（ORA-01843,ORA-01839）
投稿時刻: 2008/07/25 17:58

HD さんへの返信です。

返信

同じクエリーで・・・というのが謎ですね。

inline-viewが、SQL文の展開(最適化?)の仮定で、展開される場合があります。
そう考えると、内部のwhereによる01/01/01による絞込みと、外部のsysdateとの比較が同列となり、テーブル内の全てのレコードに対してto_dateがかかる可能性がある・・・・と考えています。
# 確証は無いです。

上記を仮定して、以下の２種類を確認してみはどうでしょうか。
１．inline-viewの部分のみで、かつ、WHERE句を除いた形で実行。
２．inline-viewの、WHERE句を除いた形で、全体を実行。

あともう１つ、展開が不可能となるようなSQL文があるとよいのですが・・・思いつきませんでした。
# 実行計画でわかるんでしたっけ・・・？
-- CMN v0.50fβ うぇいく --

HD

投稿数: 19
登録日時: 03/07/17

Re: TO_DATE関数の不思議なエラーに困っています（ORA-01843,ORA-01839）
投稿時刻: 2008/07/25 18:24

うぇいくさんへの返信です。

返信

うぇいく様
ご返信ありがとうございます。

１．inline-viewの部分のみで、かつ、WHERE句を除いた形で実行。

SELECT
TO_DATE( TO_CHAR(HEADER_YY,'00') || TO_CHAR(HEADER_MM,'00') || TO_CHAR(HEADER_DD,'00'), 'RRMMDD' ) HEADER_DATE
FROM
HEADERTABLE

→ 正常に全件抽出

２．inline-viewの、WHERE句を除いた形で、全体を実行。

SELECT COUNT(*)
FROM
(
SELECT
TO_DATE( TO_CHAR(HEADER_YY,'00') || TO_CHAR(HEADER_MM,'00') || TO_CHAR(HEADER_DD,'00'), 'RRMMDD' ) HEADER_DATE
FROM
HEADERTABLE
)
WHERE
HEADER_DATE = SYSDATE

→ 「ORA-01843: 指定した月が無効です。」が発生する

・前投稿のWHERE句を削除して実行したSQL

SELECT COUNT(*)
FROM
(
SELECT
TO_DATE( TO_CHAR(HEADER_YY,'00') || TO_CHAR(HEADER_MM,'00') || TO_CHAR(HEADER_DD,'00'), 'RRMMDD' ) HEADER_DATE
FROM
HEADERTABLE
WHERE
HEADER_YY = 1
AND HEADER_MM = 1
AND HEADER_DD = 1
)

→ 正常に処理できる

本当に謎です・゜゜・(×_×)・゜゜・

内部のwhereによる01/01/01による絞込みと、外部のsysdateとの比較が同列となり、テーブル内の全てのレコードに対してto_dateがかかる可能性がある・・
こちらの方向からもう少し考えてみようと思います。

茶太郎

投稿数: 11,815
登録日時: 99/03/15

Re: TO_DATE関数の不思議なエラーに困っています（ORA-01843,ORA-01839）
投稿時刻: 2008/07/25 19:12

HD さんへの返信です。

返信

エラーになりませんね。
ちなみに9.2.0.7.0です。
10gR2でも問題ないです。
下記SQLでエラーになりますか？


SQL> with headertable as (select
  2  1 header_yy
  3  ,1 header_mm
  4  ,1 header_dd
  5  from dual)
  6  select count(*)
  7  from(
  8  select 
  9  to_date( to_char(header_yy,'00') || to_char(header_mm,'00') || to_char(header_dd,'00'), 'rrmmdd
' ) header_date 
 10  from headertable
 11  )
 12  where header_date = sysdate;

  COUNT(*)
----------
         0

ちなみに下記でもエラーになります？
SELECT *
FROM
(
SELECT
TO_DATE( TO_CHAR(HEADER_YY,'00') || TO_CHAR(HEADER_MM,'00') || TO_CHAR(HEADER_DD,'00'), 'RRMMDD' ) HEADER_DATE
FROM
HEADERTABLE
)
WHERE
HEADER_DATE = SYSDATE

ならないとすれば不正なデータがあるように思えますが。

jiropochi

投稿数: 5,205
登録日時: 00/04/03

Re: TO_DATE関数の不思議なエラーに困っています（ORA-01843,ORA-01839）
投稿時刻: 2008/07/26 4:29

HD さんへの返信です。

返信

fxをつけた場合1のSQLはどうなりますか？

SELECT
TO_DATE( TO_CHAR(HEADER_YY,'00') || TO_CHAR(HEADER_MM,'00') || TO_CHAR(HEADER_DD,'00'), 'fxRRMMDD' ) HEADER_DATE
FROM
HEADERTABLE

また2のSQLにfmをつけた場合にもエラーになりましたか？

SELECT COUNT(*)
FROM
(
SELECT
TO_DATE( TO_CHAR(HEADER_YY,'fm00') || TO_CHAR(HEADER_MM,'fm00') || TO_CHAR(HEADER_DD,'fm00'), 'RRMMDD' ) HEADER_DATE
FROM
HEADERTABLE
)
WHERE
HEADER_DATE = SYSDATE

さらに2のSQLを以下のようにした場合はどうなりますか？

SELECT COUNT(*)
FROM
HEADERTABLE
WHERE TO_DATE( TO_CHAR(HEADER_YY,'00') || TO_CHAR(HEADER_MM,'00') || TO_CHAR(HEADER_DD,'00'), 'RRMMDD' ) = SYSDATE

HD

投稿数: 19
登録日時: 03/07/17

Re: TO_DATE関数の不思議なエラーに困っています（ORA-01843,ORA-01839）
投稿時刻: 2008/07/28 9:10

茶太郎さんへの返信です。

返信

茶太郎　様
ご返信ありがとうございます。

SELECT *
FROM
(
SELECT
TO_DATE( TO_CHAR(HEADER_YY,'00') || TO_CHAR(HEADER_MM,'00') || TO_CHAR(HEADER_DD,'00'), 'RRMMDD' ) HEADER_DATE
FROM
HEADERTABLE
)
WHERE
HEADER_DATE = SYSDATE

でも同じくエラーになります。
（WHERE句を削除して実行すると、エラーは発生しません）

HD

投稿数: 19
登録日時: 03/07/17

Re: TO_DATE関数の不思議なエラーに困っています（ORA-01843,ORA-01839）
投稿時刻: 2008/07/28 9:20

jiropochi さんへの返信です。

返信

jiropochi　様
ご返信ありがとうございます。

fxをつけた場合1のSQL -> 正常
また2のSQLにfmをつけた場合 -> エラー
さらに2のSQLを以下のようにした場合 -> エラー

以上のように、このテーブルのキー年月日をTO_DATEするのは大丈夫なのですが、
このテーブルのキー年月日をTO_DATEしたものをWHERE句で使用するとエラーになってしまいます。

水色ききょう

投稿数: 181
登録日時: 07/12/26

Re: TO_DATE関数の不思議なエラーに困っています（ORA-01843,ORA-01839）
投稿時刻: 2008/07/28 9:37

HD さんへの返信です。

返信

to_dateしてdate型同士を比較するのではなく、
to_charして文字列として比較すれば多分解決できるでしょう。

us-otnの過去ログに同じのがあるかもしれませんねぇ
http://forums.oracle.com/forums/search.jspa?threadID=&q=ORA-01843+TO_DATE&objID=f75&dateRange=all&userID=&numResults=30

HD

投稿数: 19
登録日時: 03/07/17

Re: TO_DATE関数の不思議なエラーに困っています（ORA-01843,ORA-01839）
投稿時刻: 2008/07/28 9:50

水色ききょうさんへの返信です。

返信

水色ききょう　様
ご返信ありがとうございます。

実際のSQLでは、条件が「TO_DATE値 = SYSDATE」ではなく「TO_DATE値 between パラメータDate型A and Date型B」なので、
RRとの文字列比較を避けたいのです。

ありがとうございます、us-otnの過去ログも含めてもう少し調べてみます。

HD

投稿数: 19
登録日時: 03/07/17

Re: TO_DATE関数の不思議なエラーに困っています（ORA-01843,ORA-01839）
投稿時刻: 2008/07/28 17:33

HD さんへの返信です。

返信

皆様
いろいろと貴重なご意見をありがとうございました。
解決しました。

茶太郎さまの言うとおり不正データがありました（当方ツールで確認していたため最初の500件が不正でなければエラーとならず見落としていましたm(__)m）。
おそらく原因は、
うぇいく様の「内部のwhereによる01/01/01による絞込みと、外部のsysdateとの比較が同列となり、テーブル内の全てのレコードに対してto_dateがかかる可能性がある・・
」
だと思われます。
不正データを内部および外部の単独where句で絞り込むことができず（全レコードにto_dateがかかる）、いろいろ試した結果、
case内でto_date関数を使用して回避できました。

以上、ありがとうございました。


ウェブサイトのご使用条件 \| 個人情報保護基本方針/情報保護基本方針