我用的数据库是ORACLE 10G开发VB.NET,DEBUG的时候把SQL语句贴出来去SQL PLUS里边查。
没有错误,但是运行起来就会报ORA-00918的错……
这是为什么哦?
SQL语句是这样的:
SELECT T1.ANKEN_NO
,T1.ANKEN_NM
,T12.MENU_CD
,T12.MENU_NM
,T13.KOJI_KBN
,T13.KOJI_KBN_NM
,CASE
(CASE T1.ANKEN_KBN
WHEN '03' THEN T4.KOKU_NAI_GAI_KBN
WHEN '04' THEN T7.KOKU_NAI_GAI_KBN
ELSE NULL
END)
WHEN '0' THEN T14.TODO_CD
WHEN '1' THEN T15.COUNTRY_CD
END AS TIIKI_CD
,CASE
(CASE T1.ANKEN_KBN
WHEN '03' THEN T4.KOKU_NAI_GAI_KBN
WHEN '04' THEN T7.KOKU_NAI_GAI_KBN
ELSE NULL
END)
WHEN '0' THEN T14.TODO_NM
WHEN '1' THEN T15.COUNTRY_NM
END AS TIIKI_NM
,T8.KOJI_NM_RYK
,T8.JS_ANKEN_FLG
,CASE T1.ANKEN_END WHEN '1' THEN
(CASE WHEN T16.KBN_CD = '272' AND T16.DELETE_FLG = '0' AND T16.KBN_VAL = '1'
THEN T16.KBN_NM
END)
WHEN '0' THEN
(CASE WHEN T16.KBN_CD = '272' AND T16.DELETE_FLG = '0' AND T16.KBN_VAL = '0'
THEN T16.KBN_NM
END)
ELSE
NULL
END AS ANKEN_END_NM
,T9.HAIHU_TANTO_NM
,T11.TORI_NM_KJ
,CASE T1.ANKEN_KBN
WHEN '03' THEN T5.NOKI
WHEN '04' THEN NULL
ELSE NULL
END AS NOKI
,CASE T1.ANKEN_KBN
WHEN '03' THEN T4.KANMIN_KBN
WHEN '04' THEN T7.KANMIN_KBN
ELSE NULL
END AS KANMIN_KBN
,CASE T1.ANKEN_KBN
WHEN '03' THEN T4.KOKU_NAI_GAI_KBN
WHEN '04' THEN T7.KOKU_NAI_GAI_KBN
ELSE NULL
END AS KOKU_NAI_GAI_KBN
,CASE T1.ANKEN_KBN
WHEN '03' THEN T4.MOTO_SITA_KBN
WHEN '04' THEN T7.MOTO_SITA_KBN
ELSE NULL
END AS MOTO_SITA_KBN
,CASE T1.ANKEN_KBN
WHEN '03' THEN T5.KEIYAKU_GK
WHEN '04' THEN NULL
ELSE NULL
END AS JYUTYU_KG
,CASE T1.ANKEN_KBN
WHEN '03' THEN (T5.ORDER_KAI_NM || T5.ORDER_STN_NM)
WHEN '04' THEN (T7.ORDER_KAI_NM || T7.ORDER_STN_NM)
ELSE NULL
END AS TYUMONNUSI
,CASE T1.ANKEN_KBN
WHEN '03' THEN (T5.JUYOKA_NM || T5.JUYO_STN_NM)
WHEN '04' THEN (T7.JUYOKA_NM || T7.JUYO_STN_NM)
ELSE NULL
END AS JYUYOKA
FROM
TRN_ANK_ALL T1
,TRN_ANK_JUT T2
,TRN_ANK_JUT_RRK T3
,TRN_ANK_JUT_KHN T4
,TRN_ANK_JUT_KIYK T5
,TRN_ANK_CLM T6
,TRN_ANK_CLM_RRK T7
,TRN_KS_ANK T8
,TRN_KS_ANK_HAIHU T9
,TRN_KS_ANK_KYA_KIHON T10
,MST_TORIHIKI_EIGYO T11
,MST_MENU T12
,MST_KOJI_KBN T13
,MST_TODOFUKEN T14
,MST_COUNTRY T15
,MST_KBN T16
WHERE T1.ANKEN_NO LIKE '%%'
AND T1.KARI_HON_KBN = '1'
AND T1.ANKEN_KBN IN ('03','04')
AND T1.ANKEN_NM LIKE '%%'
AND T1.ANKEN_NO = (CASE T1.ANKEN_KBN WHEN '03' THEN T2.ANKEN_NO
WHEN '04' THEN T6.ANKEN_NO END)
AND T3.ANKEN_NO = T2.ANKEN_NO
AND T3.RIREKI_NO = T2.LAST_RNO
AND T4.ANKEN_NO = T3.ANKEN_NO
AND T4.KIHON_RIREKI_NO = T3.RIREKI_NO
AND T5.ANKEN_NO = T3.ANKEN_NO
AND T5.KEIYAKU_RNO = T3.RIREKI_NO
AND T7.ANKEN_NO = T1.ANKEN_NO
AND T7.RIREKI_NO = T6.LAST_RNO
AND T8.ANKEN_NO = T1.ANKEN_NO
AND T9.ANKEN_NO = T1.ANKEN_NO
AND T10.ANKEN_NO = T1.ANKEN_NO
AND T11.HON_TORI_CD = T10.KYA_HON_CD
AND T11.SITEN_CD = T10.KYA_SITEN_CD
AND T12.MENU_CD = T1.MENU_CD
AND T12.DELETE_FLG = '0'
AND T13.KOJI_KBN = T4.KOJI_KBN
AND T13.DELETE_FLG = '0'
AND T14.TODO_CD = T4.NONYU_AREA_CD
AND T14.DELETE_FLG = '0'
AND T15.COUNTRY_CD = T4.NONYU_COUNTRY
AND T15.DELETE_FLG = '0'
没有错误,但是运行起来就会报ORA-00918的错……
这是为什么哦?
SQL语句是这样的:
SELECT T1.ANKEN_NO
,T1.ANKEN_NM
,T12.MENU_CD
,T12.MENU_NM
,T13.KOJI_KBN
,T13.KOJI_KBN_NM
,CASE
(CASE T1.ANKEN_KBN
WHEN '03' THEN T4.KOKU_NAI_GAI_KBN
WHEN '04' THEN T7.KOKU_NAI_GAI_KBN
ELSE NULL
END)
WHEN '0' THEN T14.TODO_CD
WHEN '1' THEN T15.COUNTRY_CD
END AS TIIKI_CD
,CASE
(CASE T1.ANKEN_KBN
WHEN '03' THEN T4.KOKU_NAI_GAI_KBN
WHEN '04' THEN T7.KOKU_NAI_GAI_KBN
ELSE NULL
END)
WHEN '0' THEN T14.TODO_NM
WHEN '1' THEN T15.COUNTRY_NM
END AS TIIKI_NM
,T8.KOJI_NM_RYK
,T8.JS_ANKEN_FLG
,CASE T1.ANKEN_END WHEN '1' THEN
(CASE WHEN T16.KBN_CD = '272' AND T16.DELETE_FLG = '0' AND T16.KBN_VAL = '1'
THEN T16.KBN_NM
END)
WHEN '0' THEN
(CASE WHEN T16.KBN_CD = '272' AND T16.DELETE_FLG = '0' AND T16.KBN_VAL = '0'
THEN T16.KBN_NM
END)
ELSE
NULL
END AS ANKEN_END_NM
,T9.HAIHU_TANTO_NM
,T11.TORI_NM_KJ
,CASE T1.ANKEN_KBN
WHEN '03' THEN T5.NOKI
WHEN '04' THEN NULL
ELSE NULL
END AS NOKI
,CASE T1.ANKEN_KBN
WHEN '03' THEN T4.KANMIN_KBN
WHEN '04' THEN T7.KANMIN_KBN
ELSE NULL
END AS KANMIN_KBN
,CASE T1.ANKEN_KBN
WHEN '03' THEN T4.KOKU_NAI_GAI_KBN
WHEN '04' THEN T7.KOKU_NAI_GAI_KBN
ELSE NULL
END AS KOKU_NAI_GAI_KBN
,CASE T1.ANKEN_KBN
WHEN '03' THEN T4.MOTO_SITA_KBN
WHEN '04' THEN T7.MOTO_SITA_KBN
ELSE NULL
END AS MOTO_SITA_KBN
,CASE T1.ANKEN_KBN
WHEN '03' THEN T5.KEIYAKU_GK
WHEN '04' THEN NULL
ELSE NULL
END AS JYUTYU_KG
,CASE T1.ANKEN_KBN
WHEN '03' THEN (T5.ORDER_KAI_NM || T5.ORDER_STN_NM)
WHEN '04' THEN (T7.ORDER_KAI_NM || T7.ORDER_STN_NM)
ELSE NULL
END AS TYUMONNUSI
,CASE T1.ANKEN_KBN
WHEN '03' THEN (T5.JUYOKA_NM || T5.JUYO_STN_NM)
WHEN '04' THEN (T7.JUYOKA_NM || T7.JUYO_STN_NM)
ELSE NULL
END AS JYUYOKA
FROM
TRN_ANK_ALL T1
,TRN_ANK_JUT T2
,TRN_ANK_JUT_RRK T3
,TRN_ANK_JUT_KHN T4
,TRN_ANK_JUT_KIYK T5
,TRN_ANK_CLM T6
,TRN_ANK_CLM_RRK T7
,TRN_KS_ANK T8
,TRN_KS_ANK_HAIHU T9
,TRN_KS_ANK_KYA_KIHON T10
,MST_TORIHIKI_EIGYO T11
,MST_MENU T12
,MST_KOJI_KBN T13
,MST_TODOFUKEN T14
,MST_COUNTRY T15
,MST_KBN T16
WHERE T1.ANKEN_NO LIKE '%%'
AND T1.KARI_HON_KBN = '1'
AND T1.ANKEN_KBN IN ('03','04')
AND T1.ANKEN_NM LIKE '%%'
AND T1.ANKEN_NO = (CASE T1.ANKEN_KBN WHEN '03' THEN T2.ANKEN_NO
WHEN '04' THEN T6.ANKEN_NO END)
AND T3.ANKEN_NO = T2.ANKEN_NO
AND T3.RIREKI_NO = T2.LAST_RNO
AND T4.ANKEN_NO = T3.ANKEN_NO
AND T4.KIHON_RIREKI_NO = T3.RIREKI_NO
AND T5.ANKEN_NO = T3.ANKEN_NO
AND T5.KEIYAKU_RNO = T3.RIREKI_NO
AND T7.ANKEN_NO = T1.ANKEN_NO
AND T7.RIREKI_NO = T6.LAST_RNO
AND T8.ANKEN_NO = T1.ANKEN_NO
AND T9.ANKEN_NO = T1.ANKEN_NO
AND T10.ANKEN_NO = T1.ANKEN_NO
AND T11.HON_TORI_CD = T10.KYA_HON_CD
AND T11.SITEN_CD = T10.KYA_SITEN_CD
AND T12.MENU_CD = T1.MENU_CD
AND T12.DELETE_FLG = '0'
AND T13.KOJI_KBN = T4.KOJI_KBN
AND T13.DELETE_FLG = '0'
AND T14.TODO_CD = T4.NONYU_AREA_CD
AND T14.DELETE_FLG = '0'
AND T15.COUNTRY_CD = T4.NONYU_COUNTRY
AND T15.DELETE_FLG = '0'
我刚刚也遇到了这个问题,后来一检查才发现字段重复了。
解决方法
建议写表的字段的时候最好加上表名.字段名
以后就不会出现这样的错误了