SELECT a.COMPANY, a.RESNO,
(MOD(11 - MOD(CAST(SUBSTR(a.RESNO,01,1) AS INTEGER)*2
+CAST(SUBSTR(a.RESNO,02,1) AS INTEGER)*3
+CAST(SUBSTR(a.RESNO,03,1) AS INTEGER)*4
+CAST(SUBSTR(a.RESNO,04,1) AS INTEGER)*5
+CAST(SUBSTR(a.RESNO,05,1) AS INTEGER)*6
+CAST(SUBSTR(a.RESNO,06,1) AS INTEGER)*7
+CAST(SUBSTR(a.RESNO,07,1) AS INTEGER)*8
+CAST(SUBSTR(a.RESNO,08,1) AS INTEGER)*9
+CAST(SUBSTR(a.RESNO,09,1) AS INTEGER)*2
+CAST(SUBSTR(a.RESNO,10,1) AS INTEGER)*3
+CAST(SUBSTR(a.RESNO,11,1) AS INTEGER)*4
+CAST(SUBSTR(a.RESNO,12,1) AS INTEGER)*5),11),10) as CHECK_DIGIT
FROM ( SELECT COMPANY, RESNO
FROM AAA
WHERE LENGTH(LTRIM(RTRIM(RESNO))) = 13
AND SUBSTR(RESNO,01,1) BETWEEN '0' AND '9'
AND SUBSTR(RESNO,02,1) BETWEEN '0' AND '9'
AND SUBSTR(RESNO,03,1) BETWEEN '0' AND '9'
AND SUBSTR(RESNO,04,1) BETWEEN '0' AND '9'
AND SUBSTR(RESNO,05,1) BETWEEN '0' AND '9'
AND SUBSTR(RESNO,06,1) BETWEEN '0' AND '9'
AND SUBSTR(RESNO,07,1) BETWEEN '0' AND '9'
AND SUBSTR(RESNO,08,1) BETWEEN '0' AND '9'
AND SUBSTR(RESNO,09,1) BETWEEN '0' AND '9'
AND SUBSTR(RESNO,10,1) BETWEEN '0' AND '9'
AND SUBSTR(RESNO,11,1) BETWEEN '0' AND '9'
AND SUBSTR(RESNO,12,1) BETWEEN '0' AND '9'
AND SUBSTR(RESNO,13,1) BETWEEN '0' AND '9'
) a
WHERE ((CAST(SUBSTR(a.RESNO,03,2) AS INTEGER) >= 1 AND CAST(SUBSTR(a.RESNO,03,2) AS INTEGER) <= 12) AND
(CAST(SUBSTR(a.RESNO,05,2) AS INTEGER) >= 1 AND CAST(SUBSTR(a.RESNO,05,2) AS INTEGER) <= 31) )
AND (MOD(11-MOD(CAST(SUBSTR(a.RESNO,01,1) AS INTEGER)*2
+CAST(SUBSTR(a.RESNO,02,1) AS INTEGER)*3
+CAST(SUBSTR(a.RESNO,03,1) AS INTEGER)*4
+CAST(SUBSTR(a.RESNO,04,1) AS INTEGER)*5
+CAST(SUBSTR(a.RESNO,05,1) AS INTEGER)*6
+CAST(SUBSTR(a.RESNO,06,1) AS INTEGER)*7
+CAST(SUBSTR(a.RESNO,07,1) AS INTEGER)*8
+CAST(SUBSTR(a.RESNO,08,1) AS INTEGER)*9
+CAST(SUBSTR(a.RESNO,09,1) AS INTEGER)*2
+CAST(SUBSTR(a.RESNO,10,1) AS INTEGER)*3
+CAST(SUBSTR(a.RESNO,11,1) AS INTEGER)*4
+CAST(SUBSTR(a.RESNO,12,1) AS INTEGER)*5),11),10) <> CAST(SUBSTR(a.RESNO,13,1) AS INTEGER)
이렇게 하면 ora-000909 인수 개수가 부적합하다고 합니다
어찌하면 좋을까요?? 부탁 합니다
|