database.sarang.net
UserID
Passwd
Database
DBMS
MySQL
PostgreSQL
Firebird
ㆍOracle
Informix
Sybase
MS-SQL
DB2
Cache
CUBRID
LDAP
ALTIBASE
Tibero
DB 문서들
스터디
Community
공지사항
자유게시판
구인|구직
DSN 갤러리
도움주신분들
Admin
운영게시판
최근게시물
Oracle Q&A 41116 게시물 읽기
No. 41116
쿼리 결과물에서 특정한 DATA 제외
작성자
한상원(ennedi)
작성일
2016-04-06 17:28
조회수
7,336

저 결과에서 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

이 글에 대한 댓글이 총 2건 있습니다.

   AND NVL(GSSNKN, '0') <> '8'

마농(manon94)님이 2016-04-06 17:58에 작성한 댓글입니다.

마농님 감사합니다.

매번 도움만 받고 있습니다. 좋은하루 보내세요~!!!!

한상원(ennedi)님이 2016-04-07 08:57에 작성한 댓글입니다.
[Top]
No.
제목
작성자
작성일
조회
41119나머지 수량 처리 [1]
바람이
2016-04-12
7302
41118lob 특정문자열 사이 insert [1]
정표
2016-04-11
7192
41117coulumn cursor질문 [1]
이정재
2016-04-07
7358
41116쿼리 결과물에서 특정한 DATA 제외 [2]
한상원
2016-04-06
7336
41115Oracle SID, USER, DB 간의 관계?? [1]
알파공
2016-04-01
7263
41114index range scan을 unique scan으로 풀리도록 할 수 있나요? [1]
2016-04-01
7100
41113linux 관련 엄청 어려운 문제 [2]
초보DBA
2016-03-31
7267
Valid XHTML 1.0!
All about the DATABASE... Copyleft 1999-2023 DSN, All rights reserved.
작업시간: 0.060초, 이곳 서비스는
	PostgreSQL v16.1로 자료를 관리합니다