쿼리 질문이요 0 3 534

by 탑개발자 [SQL Query] [2018.05.23 18:00:33]


아래 처럼 쿼리를 짜서 개발 서버에서 돌아갔는데 실서버에 적용하니까 에러가 나네요

ORA-00904: "H"."E_SEQ": invalid identifier 에런 인데 너무 안쪽에 선언되어 있어서 위에 꺼를 인식 못하는거 같은데

해결 방안이 있는지 조언좀해주세요.


SELECT COUNT (E_SEQ) AS TOTCNT
  FROM (SELECT *
          FROM TBLK_NEW_EDUCATION
         WHERE 1 = 1 AND E_IS_MAIN = 'Y') H
 WHERE 0 <
          (         WITH DEPT_TEST AS (SELECT XMLAGG (XMLELEMENT (
                                                           a,
                                                           ',' || EC_EDU_TARGET) ORDER BY
                                                                                    EC_EDU_TARGET).EXTRACT (
                                                   '//text()').getStringVal ()
                                                   AS EC_EDU_TARGET
                                           FROM TBLK_NEW_EDUCATION_COURSE
                                          WHERE     E_SEQ = H.E_SEQ
                                                AND EC_TO_TARGET = 'TOT')
         
         
          SELECT COUNT (*)
             FROM ( 
             SELECT EC_EDU_TARGET
                       FROM (    SELECT REGEXP_SUBSTR (EC_EDU_TARGET,
                                                       '[^,]+',
                                                       1,
                                                       LEVEL)
                                           AS EC_EDU_TARGET
                                   FROM (DEPT_TEST)
                             CONNECT BY INSTR (EC_EDU_TARGET,
                                               ',',
                                               1,
                                               LEVEL - 1) > 0)
                   GROUP BY EC_EDU_TARGET)
            WHERE EC_EDU_TARGET IN (1, 2))

by 마농 [2018.05.23 18:30:08]

안해도 될법한 작업을 너무 많이 하네요.
데이터를 묶었다가 다시 쪼깨네요. 그러면 아무짓도 안한거랑 같은 결과죠.
h 에 e_seq 유니크 한가요?


by 탑개발자 [2018.05.23 19:53:16]

네 유니크한 키 입니다


by 마농 [2018.05.23 22:04:21]
SELECT COUNT(DISTINCT h.e_seq) totcnt
  FROM tblk_new_education h
 INNER JOIN tblk_new_education_course b
    ON b.e_seq = h.e_seq
 WHERE h.e_is_main = 'Y'
   AND b.ec_to_target = 'TOT'
   AND b.ec_edu_target IN (1, 2)
;

SELECT COUNT(*) totcnt
  FROM tblk_new_education h
 WHERE h.e_is_main = 'Y'
   AND EXISTS (SELECT 1
                 FROM tblk_new_education_course b
                WHERE b.e_seq = h.e_seq
                  AND b.ec_to_target = 'TOT'
                  AND b.ec_edu_target IN (1, 2)
               )
;

 

댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입