개발 이야기 거의 하는 방

'ORA-01722: 수치가 부적합합니다'

Oracle 쿼리 실행하다보면 자주 발생하는 에러중 하나입니다.

집계함수를 사용하던 중에 발생한 오류를 해결한 과정입니다.

'date_column'에 'yyyy-mm-dd'형태의 일자가 있고, 'a_columnm'에 날짜별 데이터가 있다고 가정했을때,

날짜별로 'a_columnm'이 갖는 값들에 대해 count를 하려합니다.

SELECT TO_CHAR(date_column, 'YYYY-MM') AS month,
       a_column,
       COUNT(*) AS count_per_value
FROM your_table_name
GROUP BY TO_CHAR(date_column, 'YYYY-MM'), a_column
ORDER BY month, a_column;

위 쿼리만 봤을 때 문제없이 실행되어야 하지만 'ORA-01722: 수치가 부적합합니다' 에러가 발생합니다.

TO_CHAR() 함수가 실행될 때 데이터 형식에 관련된 문제라고 판단되어 쿼리를 아래와 같이 실행하였습니다.

SELECT TO_CHAR(TO_DATE(date_column), 'YYYY-MM') AS month,
       a_column,
       COUNT(*) AS count_per_value
FROM your_table_name
GROUP BY TO_CHAR(TO_DATE(date_column), 'YYYY-MM'), a_column
ORDER BY month, a_column;

'date_column'의 형 변환을 TO_DATE(date_column) 처럼 해준 뒤에 실행하니 정상적으로 실행되었습니다.

다시 확인해보니 'date_column'의 형식이 character형식이었네요.

이처럼 oracle은 데이터 형시에 더 민감한 모습을 보여줍니다.

허무하게 해결되었네요 :)

 프로시저 내에서 union 되는 쿼리를 임시테이블(with)로 작성하여 처리하던 중에 위와 같은 에러가 발생했습니다.

문제가 발생한 쿼리 구조는 아래와 같습니다.

;WITH '임시테이블' AS (
SELECT 
    --top 100 percent
    A.aaa
    A.bbb
    A.ccc
FROM	
	AAA AS A
WHERE	...			   

UNION ALL

SELECT
    --top 100 percent
    B.aaa
    B.bbb
    B.ccc
FROM	
	BBB AS B
WHERE	...

ORDER BY B.aaa DESC, B.ccc DESC
)
select * from '임시테이블'

 Union 한 두 select 쿼리 하단에 order by 구문으로 인해 발생한 에러입니다.

쿼리에 select 다음에 주석처리 되있는 'top 100 percent'를 작성해주면 위 에러는 해결이 됩니다.

 * 참고로 top N percent 퀴리는 예를 들어 select 쿼리 결과 row수가 400개 일 때 top 20 percent 쿼리를 작성해주면 400개의 20%인 80개의 결과. top 20으로 작성하면 20개의 결과만 출력됩니다

  MSSQL에서 임시테이블(with)쿼리를 작성하다보면 위와 같은 에러가 종종 발생합니다.

해결은 허무할 만큼 에러 메시지에 간단하게 나와있습니다

'With'앞에 세미콜론(;)만 붙여주면 됩니다.

-- WITH 앞에 세미콜론을 붙여주세요 --
;WITH '임시테이블명' AS (
			SELECT *
			  FROM	A   
			
			UNION ALL
			
			SELECT *			 
			  FROM	B
			 )
     select * from '임시테이블명'

SELECT TOP 100, 컬럼1, 컬럼2, * FROM 테이블이름;
SELECT TOP 1000 * FROM 테이블이름;

 mysql에선 'limit n'으로 쿼리를 작성하지만 mssql은 select 바로 다음에 'TOP N(숫자)'으로 쿼리를 작성해주면 상위 N개의 결과값이 검색된다.

 생각나서 찾아보니 하위 N개에 대한 쿼리는 없는 듯... order by desc/asc로 처리해주면 된다.