꿈꾸는 개발자, DBA 커뮤니티 구루비

서브쿼리항목 조건절에 사용하는방법 질문드려요

by fhfhfh123 [Oracle 기초] [2023.10.25 14:53:58]

SELECT DS.DOCNO, DS.SHOWNO, SSTITLE, SSWRITEDATE, WDN.DOCNAME DOCID, MAININGCD, PHOTO 
		     , JUDGEGROUPNAME, USERNM SSWRITERNM, DS.GRADEID, WJ.GRADENM, DEPTNM SSDEPTID, DS.PROCSEQ
			 , HITCOUNT, MEMOCOUNT, LIKECNT, OPENYN
	         , (CASE WHEN MAININGCD=2 OR MAININGCD=3 THEN WJ.PROCESSTITLE ELSE PMC.MAINNM END) MAINNM
	         , DOCTEXT
	         , (SELECT COUNT(*) FROM WIZ_DOCFILE WHERE DOCNO=DS.DOCNO AND DELETEFLAG='N') FILECNT
	         , TO_NUMBER (SUBSTR (diff, 2, 9)) day0
		     , TO_NUMBER (SUBSTR (diff, 12, 2)) hour0
		     , TO_NUMBER (SUBSTR (diff, 15, 2)) minute0
		     , TO_NUMBER (SUBSTR (diff, 18, 2)) second0
		     , NAMEOPENYN, SSWRITERSQ, OUSERSQ,PU.DIVSIONNM, PU.DIVISION, EVACOST, CATEGORYNAME
		     , (CASE WHEN DS.PROCSEQ = 4 THEN CONCAT('실시담당자 : ', (SELECT USERNM FROM PUB_USER WHERE USERSQ = DS.EXECSQ)) ELSE '' END)AS execnm
		     , (SELECT GRADENM FROM WIZ_JUDGE wj2 WHERE WJ2.PROCSEQ = 3 AND WJ2.DOCNO = DS.DOCNO)AS NEXTSTATUS, DS.JUDGEDATE2
    	  FROM (
      			SELECT ROW_NUMBER() OVER(ORDER BY WD.sswritedate DESC, WD.DOCNO DESC) SEQ
      				 , WD.DOCNO, WD.SHOWNO, SSWRITERSQ, SSDEPTID, SSTITLE, SSWRITEDATE, DOCID, MAININGCD
		             , WD.GRADEID, WD.PROCSEQ, HITCOUNT, MEMOCOUNT, LIKECNT, OPENYN, NAMEOPENYN	
		             , NUMTODSINTERVAL (TO_DATE (TO_CHAR (WD.ENTRYDATE, 'YYYY-MON-DD HH24:MI:SS'), 'YYYY-MON-DD HH24:MI:SS') - SYSDATE, 'DAY') DIFF
		             , (SELECT COUNT(*) FROM WIZ_DOCOPENMEMBER WHERE DOCNO=WD.DOCNO AND (USERSQ= 'U000000002' /**P*/ OR USERSQ= 'D000000001' /**P*/)) AS OUSERSQ
		             , WD.UPDATEDATE,REGEXP_REPLACE(T.DOCTEXT,'<[^>]*>', ' ') AS DOCTEXT, WD.EVACOST, (SELECT CATEGORYNAME FROM WIZ_CATEGORY wc WHERE wc.CATEGORYID = wd.CATEGORYID)AS CATEGORYNAME
		             , WD2.USERSQ EXECSQ, (SELECT TO_CHAR(JUDGEDATE, 'YYYY-MM-DD') FROM WIZ_JUDGESUB wj2 WHERE WJ2.DOCNO = WD.DOCNO AND WJ2.PROCSEQ=5 AND WJ2.DELETEFLAG='N')AS JUDGEDATE2 
				  FROM WIZ_DOC WD
				  INNER JOIN PUB_USER PU ON PU.USERSQ=WD.SSWRITERSQ
				  INNER JOIN PUB_DEPT PD ON PD.DEPTID=WD.SSDEPTID
				  LEFT OUTER JOIN WIZ_DOCTEXT T ON T.DOCNO=WD.DOCNO AND DOCTEXTCD =0
				  LEFT JOIN WIZ_DOCEXECTOGETHER wd2 ON WD2.DOCNO = WD.DOCNO AND WD2.MTYPE = 'M'
			LEFT OUTER JOIN WIZ_JUDGE WJ ON WJ.DOCNO=wD.DOCNO AND WJ.PROCSEQ=WD.PROCSEQ 
		 WHERE WD.DELETEFLAG='N' AND WD.MAININGCD IN (2, 3, 4)
			-- AND   CONCAT(WD.sswritedate, ' 00:00:00')   BETWEEN CONCAT( '2023-01-01' /**P*/, ' 00:00:00') AND CONCAT( '2023-10-25' /**P*/, ' 23:59:59') 
		AND JUDGEDATE2 BETWEEN CONCAT( '2023-01-01' /**P*/, ' 00:00:00') AND CONCAT( '2023-10-25' /**P*/, ' 23:59:59')
		 ) DS	  
		 INNER JOIN WIZ_DOCNAME  WDN ON WDN.DOCID=DS.DOCID
		 INNER JOIN PUB_MAINCODE PMC ON PMC.GRPCD='WIZC06' AND PMC.MAINCD=DS.MAININGCD
		 LEFT OUTER JOIN WIZ_JUDGE WJ ON WJ.DOCNO=DS.DOCNO AND WJ.PROCSEQ=DS.PROCSEQ
		 INNER JOIN PUB_USER PU ON PU.USERSQ=DS.SSWRITERSQ
		 INNER JOIN PUB_DEPT PD ON PD.DEPTID=DS.SSDEPTID		
		   WHERE SEQ BETWEEN 1 AND 10 
		  ORDER BY SEQ ASC;

이쿼리에서 서브쿼리로 조회한 judgedate2를 조건절에 사용하는법을 알고싶어요

by 우주민 [2023.10.25 15:18:37]

음????

DS.JUDGEDATE2 로 사용하시면 될꺼 같은데요?

WHERE 절 다음 라인으로

AND DS.JUDGEDATE2 = 조건

형식으로....?

by fhfhfh123 [2023.10.25 15:40:12]

말씀하신대로도 해보고

SELECT DS.DOCNO, DS.SHOWNO, SSTITLE, SSWRITEDATE, WDN.DOCNAME DOCID, MAININGCD, PHOTO 
     , JUDGEGROUPNAME, USERNM SSWRITERNM, DS.GRADEID, WJ.GRADENM, DEPTNM SSDEPTID, DS.PROCSEQ
     , HITCOUNT, MEMOCOUNT, LIKECNT, OPENYN
     , (CASE WHEN MAININGCD=2 OR MAININGCD=3 THEN WJ.PROCESSTITLE ELSE PMC.MAINNM END) MAINNM
     , DOCTEXT
     , (SELECT COUNT(*) FROM WIZ_DOCFILE WHERE DOCNO=DS.DOCNO AND DELETEFLAG='N') FILECNT
     , TO_NUMBER (SUBSTR (diff, 2, 9)) day0
     , TO_NUMBER (SUBSTR (diff, 12, 2)) hour0
     , TO_NUMBER (SUBSTR (diff, 15, 2)) minute0
     , TO_NUMBER (SUBSTR (diff, 18, 2)) second0
     , NAMEOPENYN, SSWRITERSQ, OUSERSQ,PU.DIVSIONNM, PU.DIVISION, EVACOST, CATEGORYNAME
     , (CASE WHEN DS.PROCSEQ = 4 THEN CONCAT('실시담당자 : ', (SELECT USERNM FROM PUB_USER WHERE USERSQ = DS.EXECSQ)) ELSE '' END) AS execnm
     , (SELECT GRADENM FROM WIZ_JUDGE wj2 WHERE WJ2.PROCSEQ = 3 AND WJ2.DOCNO = DS.DOCNO) AS NEXTSTATUS, JUDGEDATE2
FROM (
    SELECT ROW_NUMBER() OVER(ORDER BY WD.sswritedate DESC, WD.DOCNO DESC) SEQ
         , WD.DOCNO, WD.SHOWNO, SSWRITERSQ, SSDEPTID, SSTITLE, SSWRITEDATE, DOCID, MAININGCD
         , WD.GRADEID, WD.PROCSEQ, HITCOUNT, MEMOCOUNT, LIKECNT, OPENYN, NAMEOPENYN    
         , NUMTODSINTERVAL (TO_DATE (TO_CHAR (WD.ENTRYDATE, 'YYYY-MON-DD HH24:MI:SS'), 'YYYY-MON-DD HH24:MI:SS') - SYSDATE, 'DAY') DIFF
         , (SELECT COUNT(*) FROM WIZ_DOCOPENMEMBER WHERE DOCNO=WD.DOCNO AND (USERSQ= 'U000000002' /**P*/ OR USERSQ= 'D000000001' /**P*/)) AS OUSERSQ
         , WD.UPDATEDATE,REGEXP_REPLACE(T.DOCTEXT,'<[^>]*>', ' ') AS DOCTEXT, WD.EVACOST, (SELECT CATEGORYNAME FROM WIZ_CATEGORY wc WHERE wc.CATEGORYID = wd.CATEGORYID) AS CATEGORYNAME
         , WD2.USERSQ EXECSQ, (SELECT TO_CHAR(JUDGEDATE, 'YYYY-MM-DD') FROM WIZ_JUDGESUB wj2 WHERE WJ2.DOCNO = WD.DOCNO AND WJ2.PROCSEQ=5 AND WJ2.DELETEFLAG='N') AS JUDGEDATE2
    FROM WIZ_DOC WD
    INNER JOIN PUB_USER PU ON PU.USERSQ=WD.SSWRITERSQ
    INNER JOIN PUB_DEPT PD ON PD.DEPTID=WD.SSDEPTID
    LEFT OUTER JOIN WIZ_DOCTEXT T ON T.DOCNO=WD.DOCNO AND DOCTEXTCD =0
    LEFT JOIN WIZ_DOCEXECTOGETHER wd2 ON WD2.DOCNO = WD.DOCNO AND WD2.MTYPE = 'M'
    LEFT OUTER JOIN WIZ_JUDGE WJ ON WJ.DOCNO=WD.DOCNO AND WJ.PROCSEQ=WD.PROCSEQ 
 WHERE WD.DELETEFLAG='N' AND WD.MAININGCD IN (2, 3, 4)
 AND JUDGEDATE2 BETWEEN TO_DATE('2023-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS') AND TO_DATE('2023-10-25 23:59:59', 'YYYY-MM-DD HH24:MI:SS')
 ) DS      
 INNER JOIN WIZ_DOCNAME  WDN ON WDN.DOCID=DS.DOCID
 INNER JOIN PUB_MAINCODE PMC ON PMC.GRPCD='WIZC06' AND PMC.MAINCD=DS.MAININGCD
 LEFT OUTER JOIN WIZ_JUDGE WJ ON WJ.DOCNO=DS.DOCNO AND WJ.PROCSEQ=DS.PROCSEQ
 INNER JOIN PUB_USER PU ON PU.USERSQ=DS.SSWRITERSQ
 INNER JOIN PUB_DEPT PD ON PD.DEPTID=DS.SSDEPTID        
   WHERE SEQ BETWEEN 1 AND 10 
  ORDER BY SEQ ASC;

이렇게도 해보고 결국은 안돼서

SELECT DS.DOCNO, DS.SHOWNO, SSTITLE, SSWRITEDATE, WDN.DOCNAME DOCID, MAININGCD, PHOTO 
     , JUDGEGROUPNAME, USERNM SSWRITERNM, DS.GRADEID, WJ.GRADENM, DEPTNM SSDEPTID, DS.PROCSEQ
     , HITCOUNT, MEMOCOUNT, LIKECNT, OPENYN
     , (CASE WHEN MAININGCD=2 OR MAININGCD=3 THEN WJ.PROCESSTITLE ELSE PMC.MAINNM END) MAINNM
     , DOCTEXT
     , (SELECT COUNT(*) FROM WIZ_DOCFILE WHERE DOCNO=DS.DOCNO AND DELETEFLAG='N') FILECNT
     , TO_NUMBER (SUBSTR (diff, 2, 9)) day0
     , TO_NUMBER (SUBSTR (diff, 12, 2)) hour0
     , TO_NUMBER (SUBSTR (diff, 15, 2)) minute0
     , TO_NUMBER (SUBSTR (diff, 18, 2)) second0
     , NAMEOPENYN, SSWRITERSQ, OUSERSQ,PU.DIVSIONNM, PU.DIVISION, EVACOST, CATEGORYNAME
     , (CASE WHEN DS.PROCSEQ = 4 THEN CONCAT('실시담당자 : ', (SELECT USERNM FROM PUB_USER WHERE USERSQ = DS.EXECSQ)) ELSE '' END) AS execnm
     , (SELECT GRADENM FROM WIZ_JUDGE wj2 WHERE WJ2.PROCSEQ = 3 AND WJ2.DOCNO = DS.DOCNO) AS NEXTSTATUS, JUDGEDATE2
FROM (
    SELECT ROW_NUMBER() OVER(ORDER BY WD.sswritedate DESC, WD.DOCNO DESC) SEQ
         , WD.DOCNO, WD.SHOWNO, SSWRITERSQ, SSDEPTID, SSTITLE, SSWRITEDATE, DOCID, MAININGCD
         , WD.GRADEID, WD.PROCSEQ, HITCOUNT, MEMOCOUNT, LIKECNT, OPENYN, NAMEOPENYN    
         , NUMTODSINTERVAL (TO_DATE (TO_CHAR (WD.ENTRYDATE, 'YYYY-MON-DD HH24:MI:SS'), 'YYYY-MON-DD HH24:MI:SS') - SYSDATE, 'DAY') DIFF
         , (SELECT COUNT(*) FROM WIZ_DOCOPENMEMBER WHERE DOCNO=WD.DOCNO AND (USERSQ= 'U000000002' /**P*/ OR USERSQ= 'D000000001' /**P*/)) AS OUSERSQ
         , WD.UPDATEDATE,REGEXP_REPLACE(T.DOCTEXT,'<[^>]*>', ' ') AS DOCTEXT, WD.EVACOST, (SELECT CATEGORYNAME FROM WIZ_CATEGORY wc WHERE wc.CATEGORYID = wd.CATEGORYID) AS CATEGORYNAME
         , WD2.USERSQ EXECSQ, (SELECT TO_CHAR(JUDGEDATE, 'YYYY-MM-DD') FROM WIZ_JUDGESUB wj2 WHERE WJ2.DOCNO = WD.DOCNO AND WJ2.PROCSEQ=5 AND WJ2.DELETEFLAG='N') AS JUDGEDATE2
    FROM WIZ_DOC WD
    INNER JOIN PUB_USER PU ON PU.USERSQ=WD.SSWRITERSQ
    INNER JOIN PUB_DEPT PD ON PD.DEPTID=WD.SSDEPTID
    LEFT OUTER JOIN WIZ_DOCTEXT T ON T.DOCNO=WD.DOCNO AND DOCTEXTCD =0
    LEFT JOIN WIZ_DOCEXECTOGETHER wd2 ON WD2.DOCNO = WD.DOCNO AND WD2.MTYPE = 'M'
    LEFT OUTER JOIN WIZ_JUDGE WJ ON WJ.DOCNO=WD.DOCNO AND WJ.PROCSEQ=WD.PROCSEQ 
 WHERE WD.DELETEFLAG='N' AND WD.MAININGCD IN (2, 3, 4)
 AND ((SELECT TO_DATE((SELECT TO_CHAR(JUDGEDATE, 'YYYY-MM-DD') FROM WIZ_JUDGESUB wj2 WHERE WJ2.DOCNO = WD.DOCNO AND WJ2.PROCSEQ=5 AND WJ2.DELETEFLAG='N'), 'YYYY-MM-DD HH24:MI:SS') FROM dual) BETWEEN TO_DATE('2023-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS') AND TO_DATE('2023-10-25 23:59:59', 'YYYY-MM-DD HH24:MI:SS'))
 ) DS      
 INNER JOIN WIZ_DOCNAME  WDN ON WDN.DOCID=DS.DOCID
 INNER JOIN PUB_MAINCODE PMC ON PMC.GRPCD='WIZC06' AND PMC.MAINCD=DS.MAININGCD
 LEFT OUTER JOIN WIZ_JUDGE WJ ON WJ.DOCNO=DS.DOCNO AND WJ.PROCSEQ=DS.PROCSEQ
 INNER JOIN PUB_USER PU ON PU.USERSQ=DS.SSWRITERSQ
 INNER JOIN PUB_DEPT PD ON PD.DEPTID=DS.SSDEPTID        
   WHERE SEQ BETWEEN 1 AND 10 
  ORDER BY SEQ ASC;

이렇게 했네요...

by 마농 [2023.10.25 17:35:54]

스칼라서브쿼리를 조인으로 바꾸는 방법이 있습니다.

댓글등록

SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code(

) 버튼을 클릭하여 작성 하시면 됩니다.

로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입

데이터베이스 질문과 답변