데이터베이스 질문과 답변
by 열심열심 [2019.06.26 11:00:03]
타입은 varchar2(4000) 이고 데이터 값은 무조건 문자형입니다.
JOIN 전 한 테이블 쿼리시 아무 문제가 없습니다.
EX)
select col1 from table1 where col1='abc'
조인 후에 하면 01722 뜹니다. 그리고 더 이상한건 조인해도 어떤 문자는 되고 어떤 문자는 01722떨어집니다.
ex1) 조건 값이 똑같은 문자이나 대문자일땐 에러 소문자일땐 문제없이 돌아갑니다.
ex2) G-xxx는 문제없이 되나 R-xxx는 01722 떨어집니다. r-xxx는 또 됩니다.
to_char 형변환해도 똑같이 에러떨어지고
아무리 생각해도 이해가 되지 않네요..
올려주신 쿼리만 봐서는 알 수 없습니다.
해당 컬럼 때문에 나는게 아닐 수 있습니다.
전체 쿼리중 숫자로 형변환이 발생되는 다른 부분이 있을 듯 합니다.
조인 시에 형변환이 이뤄지나요?
수천개 다 뒤져본결과 0,12 같은 값이 있어 제외 시키고 해도 똑같네요
현재 이상한 값은 a,bc'cd 같은 값이 있어 replace하여 없애였고
추가로 ab+cd 같은 값이 있습니다. 이 부분이 영향이 있을까요?
그런 부분들 찾아서 다 replace해도 대문자만 들어가면 안되네요..
확인 중인 컬럼이 문제의 원인이 아닐 수 있습니다.
다른 부분에서 나는 에러일 수 있습니다.
1. 비교식의 양쪽 타입이 다른 경우 타입을 통일시키기 위한 형변환이 발생됩니다.
2. 함수의 인자로 숫자가 필요한데 문자를 입력한 경우에도 형변환이 발생됩니다.
문제 컬럼은 order by이도 01722 에러 발생하는거 보면 컬럼 문제는 맞는 거 같은데
숫자형데이터를 강제로 문자로 바꾸면 발생하지 않을까요?
똑같은 쿼리인데 join시 에러 no join시 노 에러
대문자만 에러 소문자는 노 에러인데
대문자시에만 형변환이 발생되는건지..
해당 컬럼이 아닐 수 있습니다.
다른 컬럼이 문제일 수 있습니다.
묵시적 형변환이 일어날만한 부분을 찾아 보세요.
다른 컬럼이 varchar 문자형인데 숫자가 들어간 부분을 찾으면 되는건가요?
묵시적 형변환이 발생할 만한 부분
1. 비교식의 양쪽 타입이 다른 경우 타입을 통일시키기 위한 형변환이 발생됩니다.
2. 함수의 인자로 숫자가 필요한데 문자를 입력한 경우에도 형변환이 발생됩니다.
현재 테스트중인데 참 이건 뭔 상황인지.
현재 select col1, col2,col3,col4 from (
select a.col1,b.col2,a.col3.b.col4 from tableA a inner join tableB on a.col1=b.col1
where col4='문자'); 인데 컬럼이 수십개라 하나하나씩 하다가
select * from (
select a.col1,b.col2,a.col3.b.col4 from tableA a inner join tableB on a.col1=b.col1
where col4='문자')
이런 식으로 변경 후에는 아무 문제 없이 되네요.
a테이블 b테이블 값은 다르지만 컬럼명은 똑같은거 있습니다. 그래서 인라인뷰에 명시도 해놨고
조건절에도 명시 한 상태인데.. 아무리 생각해도 이유를 모르겠네요
쿼리가 달라지면서 실행계획이 달라질 수 있습니다.
실행계획이 달라지면서 해당 오류가 발생되는 부분이 제거될 수도 있습니다.
예를 들면.
A 에 10건(1~10)과 B 의 5건(1~5)과 조인하여 5건을 반환할때
A 의 9번 자료가 오류자료라고 가정히면.
A -> B 방향으로 조인되는 경우 9번째 조인 하다가 에러 발생 될 수 있고
B- > A 방향으로 조인되는 경우 오류가 안날 수 있습니다.
오류는 명확합니다. 쿼리에서 묵시적 형변환이 발생될만한 부분을 찾아야 합니다.
조건절이든 함수 사용 부분이든
그 다음 여기서 사용된 컬럼의 값을 확인해야죠.
쿼리가 달라진건 모두 명시한 컬럼을 *로 바꾼거 밖에 없는데 달라질수가있나요?
select 컬럼10개( select 컬럼10개 from t)와
select * from (select 컬럼10개 from t)
컬럼 10개는 동일한데 형변환이 위는 이루어지고 아래는 안이루어지고 할 수가 있나요?
보이는 컬럼10개는 동일한데..
그건 확인을 해봐야 알 수 있는 문제이구요.
이건 아닐거다 라는 선입견으로 접근하면 못찾습니다.
모든 가능성을 열어 두고 접근해야 합니다.
저는 쿼리 일부만 보니 정확한 판단은 어렵습니다.
실제로 수행해 볼 수도 없는 입장이구요.
직접 찾으셔야 합니다.
저는 그냥 일반적인 접근법을 설명해 드릴 수 있을 뿐입니다.
몇시간 째 일일히 컬럼 값 다 확인 해 본 결과
딱2개의 컬럼이 숫자 문자 혼용되어 있는데 그 컬럼 select절에서 빼도 똑같이 문제가 발생하네요.
그리고 쿼리는 단순해서 딱 저 상태입니다.
그리고 인라인뷰 안에서 join 결과에서도 문제없이 되네요. select 컬럼 from (이안에서는 문제없이 동작)
쿼리가 위와 같나요?
- 함수 사용 부분은 전혀 없는지?
- 조건절은 딱 두개 뿐인지?
함수는 to_char날짜 변환 뿐이고 조건절도 하나뿐이에요. 원래는 많은데 하나로 줄인 상태이구요.
조건절 아예 안주면 그냥 문제 없이 됩니다. 그리고 조건절다 하나씩은 안해봤지만
다른 조건절들은 똑같이해도 아무문제 없이 돌아가기도 합니다.
그냥 * 쓰면 되지만 그러자니 원인을 모르니 찝찝하네요.
함수 시용 부분과 조건 비교부분에서 사용되는 컬럼의 타입 확인해 보세요.
함수 사용 부분 쿼리 올려 주세요.
원인을 찾은것 같습니다.
날짜가 한곳에 0999-01-01 이런식으로 들어가 있었습니다. 그 값을 지우니 문제없이 돌아가네요
근데 YY-MM-DD 형식은 맞으니까 문제 없어야 되지 않나요?
*시엔 문제 없는 것도 이상하고..
함수 사용 구문을 보여주세요.
to_char(date컬럼, 'YYYY-MM-DD) 입니다.
저컬럼에 잘 못들어간 값은 0999/06/26 00:00:00 입니다.
"date컬럼" 의 데이터 타입이 DATE 가 아닌 VARCHAR2 가 아닐지 의심됩니다.
데이터타입은 date맞습니다.
오라클에서 0999/06/26을 0을 날려버리는게 아닌가 의심됩니다.
to_date(to_char('컬럼','YYYY-MM-DD'))에서는 에러없이 돌아가긴 하거든요 값이 뒷자리만 나오긴 하지만
99-06-26으로
Date Type 확실한가요?
테이블 DESC 로 확인하셨나요?
정상적인 값은 어떻게 보이나요?
to_char 만 사용하신 건가요? to_date 도 복합적으로 같이 쓴건가요?
정확한 사용 구문을 올려 주신 것 맞나요?
to_char만 하였고 desc로 확인하였습니다.
동일하게 1999-06-26 넣으면 에러가 없고
처음처럼 0999-06-26을 넣으면 에러가 발생합니다. 시간포함
to_date는 사용하지 않았습니다.
0999일시 에러 안나는지 확인하려고 사용했는데 에러 나지 않았습니다.
전체 쿼리를 잠깐이라도 올려주시는 게 정확할 거 같네요. 각 컬럼 데이터형이랑..
컬럼이 date형이 맞다면 to_char(컬럼,'yyyy-mm-dd')는 문제 없었을 거고요.
다른쪽 문제일듯해요.
0999-06-26을 1999-06-26으로 바꾸어서 잘 되었던 것은 단순히 상위 몇 건만 조회되어서 보여줬기 때문일 것 같은데요. select 절이 단순 컬럼 나열이라면 select절을 count(1)로 바꾸어보면
전체 데이터 조회하면서 오류 났을 것 같습니다.
컬럼나열한 부분을 *로 바꾼 것 역시 전체 order by나 다른 조건에 의해 실행계획이 바뀌면서 먼저 조회되는 row에 데이터 convert 실패할만한 건이 없었기 때문이 아닐까 생각되고요.
마농님 말씀대로 묵시적 형변환 부분이 없나 체크해보세요.
문자형컬럼 = 1
이런식으로 조건절이 있으면
내부적으로는 to_number(문자형컬럼) = 1로 쿼리가 바뀝니다.
전체쿼리는 위에 있는거랑 똑같아요 정말 단순한 쿼리이고
999년 데이터가 들어가면서 문제된게 맞는건 확인했습니다.
해당컬럼을 제외하거나 컬럼포함시 0999년도를 삭제 하면 아무문제없이 돌아가니깐요.
소문자에서 됐던건 해당 컬럼에 그 값이 없어서 된것으로 추정됩니다. 대문자 값은 있었거든요
) 버튼을 클릭하여 작성 하시면 됩니다.