TO_NUMBER의 ORA-01722: 수치가 부적합합니다 원인 0 2 456

by 기린기린 [Oracle 에러코드] [2020.04.09 09:53:37]


안녕하세요.

 

TABLE1의 거래등급은 VARCHAR2 타입이고 모든 자료 상에는
'1', '2', '3', ... 'A', 'B', 'C' ... 같은 숫자변환 불가한 것도 있습니다.
하지만 COLUMN1 = 'M' 조건에서는 '1' 값 밖에 없는 상태입니다.
널 값도 없습니다.

 

WITH W_거래등급 AS
(SELECT DISTINCT 거래등급  FROM TABLE1 WHERE COLUMN1 = 'M')
SELECT *
  FROM W_거래등급 A
 WHERE 
 TO_NUMBER(A.거래등급) = 5

이렇게 하면 ORA-01722 에러가 납니다.

WITH W_거래등급 AS
(SELECT DISTINCT 거래등급  FROM TABLE1 WHERE COLUMN1 = 'M')
SELECT *
  FROM W_거래등급 A
 WHERE 
 TO_NUMBER(NVL(A.거래등급, 9999)) = 5

이렇게 NVL 처리 해주면 에러를 피해갑니다.


WITH W_거래등급 AS
(SELECT DISTINCT 거래등급  FROM TABLE1 WHERE COLUMN1 = 'M')
SELECT *
  FROM W_거래등급 A
 WHERE 
 TO_NUMBER(A.거래등급) = 1

'1' 밖에 없어서 그런지 위 조건도 에러를 피해갑니다.

 


하지만

WITH W_거래등급 AS
(SELECT '1' 거래등급, '2' D FROM DUAL)
SELECT *
  FROM W_거래등급 A
 WHERE 
 TO_NUMBER(A.거래등급) = 5
            
에러가 발생되야할 것 같은 듀얼로 만든 조건도 에러를 피해갑니다.

에러가 발생되는 이유가 무엇일까요?

 

감사합니다.

by 마농 [2020.04.09 10:30:56]

쿼리변환이 동작해서 그런 것 같습니다.
- 뷰 밖의 조건이 뷰 안으로 들어갔거나.
- 뷰 머징이 되어 원쿼리로 합쳐졌거나.
두개의 조건 중 'M' 조건이 먼저 처리되면 좋으련만,
5 조건이 먼저 수행된다면 에러가 날 수 있습니다.
두가지 방안이 있습니다.
1. WITH 문이 먼저 수행되도록 WITH 문 안에 힌트 추가
 - /*+ materialize */
2. 힌트를 통해 조건절 처리 순서를 지정
 - WITH 제거하여 원쿼리로 만들기
 - 조건 두개 주면서 조건 처리순서 힌트 제어
 - Distinct 대신 ROWNUM 사용.
3. 그런데...
굳이 숫자로 변환해서 비교할 필요가 있는지?
부등호 조건이면 몰라도 등호 조건이라면 5 대신 '5' 하면 되죠.
 

WITH w_거래등급 AS
(
SELECT /*+ materialize */
       DISTINCT 거래등급
  FROM table1
 WHERE column1 = 'M'
)
 SELECT *
   FROM w_거래등급 a
  WHERE TO_NUMBER(a.거래등급) = 5
;

SELECT /*+ ordered_predicates */
       거래등급
  FROM table1
 WHERE column1 = 'M'
   AND TO_NUMBER(거래등급) = 5
   AND ROWNUM = 1
;

SELECT 거래등급
  FROM table1
 WHERE column1 = 'M'
   AND 거래등급 = '5'
   AND ROWNUM = 1
;

 


by 기린기린 [2020.04.09 10:50:37]

WITH 뷰 조건 보다
밖의 조건이 먼저 수행될 수 있는 거군요.
정말 감사드립니다.
숫자변환은 사실 사용자가 'N'이 아닌 '0N'으로 거래등급 코드를 별도로 등록할 수 있다는 가정때문에 했었습니다.
그럴 가능성이 없어서 말씀대로 캐릭터비교로
TO_NUMBER를 제거하는 방향으로 결정했습니다.
감사합니다.

댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입