저 결과에서 GSSNKN = '8' 인것은 제외하려고 GSSNKN <> '8'을쓰게되면결과가'2'인것만 나옵니다....공백...2....이렇게 다 나와야 하는데
쿼리문은 아래처러 되어있는데 간단히 생각했는데 원하는 결과가 안나오네요..어떻게 추가 해야 '8' 레코드가 안나오고 나머지는 다 나올까요.?
SELECT ROWNUM,A.GOGOCD, A.GOGONM , GSSNKN, NVL(A.GOUNIT,0) GOUNIT,
NVL(F_GoodSendQty(:P_JUMPO_CD,:P_BALJU_DT, A.GOGOCD,:P_PYUN_NO),0) AS BE_QTY,
NVL(B.GSSHQT, 0) YEYAK, NVL(B.GSSNQ1,0) GSSNQ1 ,
NVL(B.GSSNQ2,0) AS GSSNQ2 , A.GOMIDD,
DECODE(A.GOESYN,'1','필수','2','선택','기타') PYOJUN,
A.GOTXKN,
DECODE((NVL(A.GOOTAM,1) + NVL(A.GOOTVT,0)),0,0,DECODE(A.GOTXKN,'1', (TRUNC(ABS((((NVL(A.GOINAM,0) + NVL(A.GOINVT,0)) * 1.1 ) /(NVL(A.GOOTAM,1) +NVL(A.GOOTVT,0))) -1)*1000))/10)) IIG1,
DECODE((NVL(A.GOOTAM,1) + NVL(A.GOOTVT,0)),0,0,DECODE(A.GOTXKN,'2', (TRUNC(ABS((NVL(A.GOINAM,0) + NVL(A.GOINVT,0)) / (NVL(A.GOOTAM,0) +NVL(A.GOOTVT,0)) -1 )* 1000))/10)) IIG2,
(A.GOOTAM + A.GOOTVT) MEAGA, A.GORECD, A.GORANK,
DECODE(A.GOREYN,0,'가능','1','불가') GOREYN ,
DECODE(A.GOSETY,'1','행사 :'||' ' || A.GODCST ||'~'||A.GODCET, ' ' ) HEANG,
A.GOSEYN,
TO_CHAR(NVL(PSPAQT,0),'999,999') AS JAEGO_QTY,
SUBSTR(
( SELECT CMCDT1
FROM SCCOMMTB Z
WHERE 1=1
AND Z.CMSHCD = :P_JUMPO_CD
AND Z.CMLRCD = '01'
AND Z.CMMDCD = '21'
AND Z.CMSMCD = A.GOCUST
) ,:P_YOIL_B,1) AS PYUN_1,
SUBSTR(
( SELECT CMCDT2
FROM SCCOMMTB Z
WHERE 1=1
AND Z.CMSHCD = :P_JUMPO_CD
AND Z.CMLRCD = '01'
AND Z.CMMDCD = '21'
AND Z.CMSMCD = A.GOCUST
) ,:P_YOIL_B,1) AS PYUN_2 ,
SUBSTR(
( SELECT CMCDT3
FROM SCCOMMTB Z
WHERE 1=1
AND Z.CMSHCD = :P_JUMPO_CD
AND Z.CMLRCD = '01'
AND Z.CMMDCD = '21'
AND Z.CMSMCD = A.GOCUST
) ,:P_YOIL_B,1) AS PYUN_3
FROM (SELECT *
FROM SCGOODTB_E A,
SCPRSTTB C
WHERE GOSHCD = :P_JUMPO_CD
AND (GOSNDT IS NULL OR GOSNDT > :P_BALJU_DT)
AND :P_BALJU_DT BETWEEN GODCST AND GODCET
AND PSSHCD(+) = GOSHCD
AND PSGOCD(+) = GOGOCD
AND EXISTS ( SELECT 'X'
FROM SCCOMMTB Z
WHERE 1=1
AND Z.CMSHCD = :P_JUMPO_CD
AND Z.CMLRCD = '01'
AND Z.CMMDCD = '21'
AND Z.CMSMCD = A.GOCUST
AND SUBSTR(Z.CMCDT1,:P_YOIL_B,1) <> '9' )
) A,
SCGOSNTB B, SCGOODTB C
WHERE B.GSSHCD(+) = :P_JUMPO_CD
AND B.GSSNDD(+) = :P_BALJU_DT
AND B.GSCANO(+) = :P_PYUN_NO
AND B.GSSHCD(+) = A.GOSHCD
AND B.GSGOCD(+) = A.GOGOCD
AND GSSNKN <> '8' -- 이렇게 사용하면 원하는 결과 안나옴
AND A.GOGOCD = C.GOGOCD
AND C.GOSEYN = '0'
AND C.GOSNYN = '0'
AND C.GOCMYN = '0'
AND (C.GOMAKN = '2' OR C.GOMAKN = '3')
ORDER BY A.GOGOCD |