데이터베이스 질문과 답변
SELECT A.STDPD,A.VORNR,C.NO_DOC,A.NO_SEQ,
C.MAX_LIMITS,
C.KMAX_LIMITS,
C.MIN_LIMITS,
C.KMIN_LIMITS,
C.TA_VALUE,
AVG(TO_NUMBER(A.FINAL_VALUE)) AVG,
C.TXT_ITEM FROM TB_01 A, TB_02 B, TB_03 C
WHERE A.MANDT = '700'
AND A.MANDT = B.MANDT AND A.AUFNR NOT LIKE 'SA%'
AND A.AUFNR = B.AUFNR AND A.VORNR = B.VORNR
AND A.NO_SEQ = B.NO_SEQ
AND A.FINAL_VALUE <> ' '
AND B.RESULT_VALUE <> ' '
AND B.RESULT_VALUE <> 'OK'
AND B.RESULT_VALUE <> 'NG'
AND B.RESULT_YN <> 'X'
AND C.MAX_LIMITS <> ' ' AND C.MIN_LIMITS <> ' '
AND C.MAX_LIMITS <> 'OK' AND C.MIN_LIMITS <> 'OK'
AND C.KCPK_YN = 'Y'
AND B.SUB_SEQ = (SELECT MAX(X.SUB_SEQ) FROM Z2SCMES05 X
WHERE X.MANDT = A.MANDT
AND X.AUFNR = A.AUFNR AND X.VORNR = A.VORNR
AND X.NO_SEQ = A.NO_SEQ)
AND A.MANDT = C.MANDT AND A.STDPD = C.STDPD
AND A.VORNR = C.VORNR AND A.NO_SEQ = C.NO_SEQ
AND C.NO_DOC = (SELECT MAX(Y.NO_DOC)
FROM Z2SCMES11 Y
WHERE Y.MANDT = A.MANDT
AND Y.STDPD = A.STDPD
AND Y.VORNR = A.VORNR
AND Y.STATUS = '4'
AND Y.USE_SERNR <> '9999')
AND B.WRK_DATE BETWEEN TO_CHAR(TO_DATE('20190522', 'YYYYMMDD')-30,'YYYYMMDD') AND '20190522'
GROUP BY A.STDPD,A.VORNR,C.NO_DOC,A.NO_SEQ,
C.MAX_LIMITS,
C.KMAX_LIMITS,
C.MIN_LIMITS,
C.KMIN_LIMITS,
C.TA_VALUE,
C.TXT_ITEM
안녕하세요 프로시저를 이용하여 데이터를 집계하는 쿼리가 있는데
위 쿼리에서 수치가 부적합합니다(AVG(TO_NUMBER(A.FINAL_VALUE)) AVG) 라는 오류가 계속 리턴이 되어 도움을 얻고자 질문 남겨봅니다.
오류가 나는 부분 : AVG(TO_NUMBER(A.FINAL_VALUE)) AVG,
일단 어떠한 조건들에 의해서 SELECT절에 값이 잘못 나오는 거 같아 집계함수(AVG)를 제거하고 FINAL_VALUE라는 컬럼을 GROUP BY 절에 이동시켜서
조회를 하고 에러가 날 만한 데이터를 수정을 하였는데도 계속 에러가 나네요..
조건절에서 AND C.NO_DOC = (SELECT MAX(Y.NO_DOC) 서브쿼리문을 주석처리 하거나 C.TXT_ITEM 해당 컬럼을 주석처리하여 실행시키면 데이터가 또 정상적으로 출력이 됩니다..
수치가 부적합합니다 라는 오류의 원인을 찾고자 한다면 어떠한 방법으로 찾을 수 있는지 조그만한 도움주시면 감사하겠습니다.
group by, avg 제거하시고 dump 찍어보시면 -> DUMP(A.FINAL_VALUE)
to_number 하기 부적합한 문자를 찾을 수 있지 않을까 싶습니다.
죄송합니다 제가 dump를 처음 사용해보아서
dump를 사용하여 조회를 해보았는데 데이터가 어떻게 나와야 숫자인지, 문자인지를 잘 모르겠네요 ㅠㅠ
| Typ=1 Len=8: 50,53,48,46,52,54,54,55 |
데이터들은 이런식으로 조회가 되고 있습니다.
DUMP는 문자값의 아스키코드를 찍어줍니다.
작성해주신 결과를 보니까 '250.4667'과 같은 마침표가 들어간 형태로 저장이 되어있는 듯 한데..
숫자가 소수점까지 있는 형태가 맞나요?
맞다면 TO_NUMBER(A.FINAL_VALUE, '999999999D999999') 이런식으로 해보세요~ (자릿수는 모자르지 않게 맞춰주셔야 합니다.)
[아스키코드 참고용]
SELECT DUMP('.0123456789'), DUMP('250.4667')
FROM dual
숫자는 소수점까지 포함되어 있고 정수, 음수 형태로 나오고 있습니다!
TO_NUMBER(A.FINAL_VALUE, '999999999D999999') 은 SELECT절에 넣어보라는 말씀이시죠?
넵 해보세요~
데이터가 뭔가 이상하게 조회가 되나봅니다..
문자형이 도저히 안보이네요 ㅠㅠ
원래 쿼리를 변경해보시라고 말씀드린거예요.
AVG(TO_NUMBER(A.FINAL_VALUE)) -> AVG(TO_NUMBER(A.FINAL_VALUE, '999999999D999999')) 이렇게요
TO_NUMBER 할때 문자에 마이너스(-) 나 마침표(.) 가 있으면 숫자형으로 변환을 못하고 오류가 나는데
TO_NUMBER 파라미터에 '999999999D999999'를 넣어주면 위의 경우에도 정상적으로 숫자형으로 변환이 가능하기 때문입니다.
목적이 숫자형으로 변환해서 평균값 구하려고 하신거 아닌가요?
도움 많이 주셔서 정말 감사합니다 ㅠㅠ
원 쿼리에 수정을 해도 수치가 부적합합니다 라는 오류가 발생하네요
뭔가 다른 컬럼쪽에 문제가 있는 거 같은데 한번 확인해봐야 될 거 같아요..
많은 도움 주셔서 감사합니다.
하위레벨부터 차근차근 찾아보시면 금방 찾으실겁니다.
화이팅입니다 ㅋㅋ
숫자가 아닌값들이 들어있어서 그럴겁니다
정규식이용 하시면 금방 찾을수 있습니다
답변 주셔서 감사합니다!
정규식을 사용하여 숫자가 아닌 것들도 조회도 해봤는데 FINAL_VALUE에 숫자가 나오네요
AND REGEXP_LIKE(FINAL_VALUE, '[^[:digit:]]')
위와 같이 조건을 추가했었습니다.
문자열 조건 주신것중에 숫자컬럼이 있는지 확인해보세요.
묵시적형변환 순서는 date > number > varchar 순입니다.
제 생각에는 number 컬럼에 varchar 형태의 값을 조건으로 주어(ex : 'OK') varchar 타입이 묵시적으로 to_number로 변경하는 과정에서 오류가 나는거 같습니다.
답변 주셔서 감사합니다
조건에 쓰인 컬럼은 다 VARCHAR2 형태로 구성되어 있습니다 !
SELECT * FROM a WHERE TRANSLATE(final_value, 'a0123456789.-', 'a') IS NOT NULL ;
SELECT * FROM a WHERE NOT REGEXP_LIKE(final_value, '^-?[0-9]+(\.[0-9]+)?$') ;
SELECT *
FROM a
, XMLTABLE('if (. castable as xs:decimal) then 1 else 0' PASSING final_value COLUMNS x NUMBER PATH '.')
WHERE x = 0
;
알려주신데로 문자가 나오도록 조건을 넣고 실행을 하였는데
문자형으로 이루어진 데이터가 하나도 안나오네요.. 답변 주셔서 감사합니다!
오류 원인이 final_value 가 맞나요?
다른거 다 빼고 다음 쿼리만 실행했을 때 오류 나나요?
SELECT AVG(final_value) FROM a;
gropby 절을 없애고 SELECT AVG(TO_NUMBER(A.FINAL_VALUE)) AVG 조회했을땐 조회가 됩니다
하나 group by 절을 추가 시키고 C.TXT_ITEM 컬럼을 추가 시키면 그 후부턴 수치가 부적합 합니다 라는 오류가 발생하고 있습니다.
GROUP BY 절에서 C_TXT_ITEM 컬럼을 주석처리 하고 조회를 하면 조회가 됩니다
그렇다면? 오류 원인이 final_value 가 아닌 거네요?
다른 곳에서 오류 원인을 찾아봐야 할 듯 하네요.
조인 컬럼들 중에 숫자 컬럼과 문자 컬럼을 조인하는 경우는 없는지 확인.
감사합니다 마농님 한번 천천히 다시 한번 찾아봐야 겠네요..
) 버튼을 클릭하여 작성 하시면 됩니다.