查询数据时出现Message: ORA-01722:的问题

[daisylove]

SELECT
A.NSKA_JSK_NO,
A.RB_NO,
A.NSKA_JSK_NO_EDA,
A.RB_K,
A.HMK_CD,
A.NSGT_CD,
A.LOT_NO,
A.SUK_CD,
A.NSKA_GN_CD,
A.NSKA_GN_RIYU_CD,
A.NSKA_DT,
A.NSKA_SURYO,
A.REG_DT,
B.SYYK_SUK_CD,
C.NSKA_YOTEI_DT,
C.TOKUI_SK_REFER_CD,
C.NNY_SK_REFER_CD,
C.NNY_SK_CORP_MEI,
C.SIR_SK_REFER_CD,
C.JYO_KA_REFER_CD,
C.NNY_SK_TDFK_MEI,
C.NNY_SK_SKGCS_MEI,
C.NNY_SK_OAZA_MEI,
C.NNY_SK_AZATYO_MEI,
C.NNY_SK_BANCHI_MEI,
C.NI_JTN_REFER_CD,
C.SAN_JTN_REFER_CD,
D.EIGYO_BMN_CD,
D.HBI_TRH_JOKN_NO,
D.HBI_TRH_JOKN_NO_EDA,
F.REG_P_CD,
G.HMK_GRP_CD,
H.SUK_MEI,
I1.CORP_MEI,
I2.CORP_MEI,
I3.CORP_MEI,
I4.CORP_MEI,
I5.CORP_MEI,
J.KNZN_KSU,
K.HJU
FROM AL1040 A, AL1030 B, AL1010 C, AS0410 D, AS0310 E, AS0110 F, AX0380 G, AX0420 H, AX0860 I1, AX0860 I2, AX0860 I3, AX0860 I4, AX0860 I5, AST020 J, AST040 K
WHERE
A.KAISYA_CD = 'YT ' AND
A.REG_DT >= '2003-02-27 00:00:00.0' AND
A.REG_DT <= '2003-02-27 23:59:59.0' AND
A.SSZ_SBT_K = '02' AND
( A.NSKA_GN_CD = '110' OR A.NSKA_GN_CD = '910' ) AND
A.KAISYA_CD = B.KAISYA_CD AND
A.SSK_CD = B.SSK_CD AND
A.IO_K = B.IO_K AND
A.NSKA_JSK_NO = B.NSKA_JSK_NO AND
A.SSZ_SBT_K = B.SSZ_SBT_K AND
A.KAISYA_CD = C.KAISYA_CD AND
A.SSK_CD = C.SSK_CD AND
A.IO_K = C.IO_K AND
A.NSKA_SSZ_NO = C.NSKA_SSZ_NO AND
A.SSZ_SBT_K = C.SSZ_SBT_K AND
A.KAISYA_CD = D.KAISYA_CD AND
A.NSKA_JSK_NO = D.URIAG_NO AND
A.RB_NO = D.RB_NO AND
( A.KAISYA_CD = E.KAISYA_CD AND
A.JCU_HTY_NO = E.JCU_NO AND
A.KAISYA_CD = F.KAISYA_CD AND
E.HBI_TRH_JOKN_NO = F.HBI_TRH_JOKN_NO AND
E.HBI_TRH_JOKN_KJN_DT >= F.YUKK_STR AND
E.HBI_TRH_JOKN_KJN_DT <= F.YUKK_END AND
F.ACK_K = '1' ) AND
C.KAISYA_CD = I1.KAISYA_CD (+) AND
C.TOKUI_SK_REFER_CD = I1.REFER_CD (+) AND
C.KAISYA_CD = I2.KAISYA_CD (+) AND
C.NI_JTN_REFER_CD = I2.REFER_CD (+) AND
C.KAISYA_CD = I3.KAISYA_CD (+) AND
C.SAN_JTN_REFER_CD = I3.REFER_CD (+) AND
C.KAISYA_CD = I4.KAISYA_CD (+) AND
C.JYO_KA_REFER_CD = I4.REFER_CD (+) AND
C.KAISYA_CD = I5.KAISYA_CD (+) AND
C.SIR_SK_REFER_CD = I5.REFER_CD (+) AND
A.KAISYA_CD = G.KAISYA_CD (+) AND
A.HMK_CD = G.HMK_CD (+) AND
G.HMK_GRP_YOTO_K = '11' AND
A.KAISYA_CD = H.KAISYA_CD (+) AND
A.SUK_CD = H.SUK_CD (+) AND
( A.KAISYA_CD = J.KAISYA_CD (+) AND
SUBSTR( A.HMK_CD ,1,2) = J.TNK_MANA_K (+) AND
SUBSTR( A.HMK_CD ,3,3) = J.IROBN (+) AND
SUBSTR( A.HMK_CD ,6,3) = J.ATUMI (+) ) AND
( A.KAISYA_CD = K.KAISYA_CD (+) AND
SUBSTR( A.HMK_CD ,1,2) = K.TNK_MANA_K (+) )

以上语句在查出20条数据后，报SQLState: 42000,ErrorCode: 1722,Message: ORA-01722
请高手指点！

[cenlmmx]

ORA-01722 invalid number

Cause: The attempted conversion of a character string to a number failed because the character string was not a valid numeric literal. Only numeric fields or character fields containing numeric data may be used in arithmetic functions or expressions. Only numeric fields may be added to or subtracted from dates.

Action: Check the character strings in the function or expression. Check that they contain only numbers, a sign, a decimal point, and the character "E" or "e" and retry the operation.
在扫描数据的时候，一般会自动会根据where条件的数据类型进行转换匹配,但如果不能转换就只有报错.检查你的数据.

[daisylove]

看了
[0] KAISYA_CD : 'YT ' CHAR(3)
[1] REG_DT : '2003-02-27 00:00:00.0'DATE
[2] REG_DT : '2003-02-27 23:59:59.0'DATE
[3] SSZ_SBT_K : '02' CHAR(2)
[4] NSKA_GN_CD : '110' CHAR(3)
[5] NSKA_GN_CD : '910' CHAR(3)
[6] ACK_K : '1'CHAR(1)
[7] HMK_GRP_YOTO_K : '11' CHAR(2)
以上是对应条件的数据类型，如果说进行数据转换匹配的话，为什么还能检出数据呢？

[cenlmmx]

举个简单例子:
where substr(endday,4,2)=month //month是number型
而表中存在endday='TEST'的几条记录,由于to_number('TEST')是违反规则的，所以就报错退出了.

[daisylove]

SUBSTR( A.HMK_CD ,6,3) = J.ATUMI
A.HMK_CD CHAR(28)
J.ATUMI NUMBER(3)

明白了，是这里的问题。把J.ATUMI toChar()一下是不是就可以了

[bobfang]

是的