안녕하세요. 제목처럼 ORCLE -> MSSQL로 전환하고 있습니다.
SELECT COUNT(1) AS MAX_ORD , MAX(CASE WHEN (IN_DAERIJA_ID IS NULL AND B.GYEOLJAEJA_ID = IN_GIANJA_ID AND B.GYEOLJAEJA_ID_GB = IN_GIANJA_ID_GB) OR (IN_DAERIJA_ID IS NOT NULL AND (B.GYEOLJAEJA_ID, B.GYEOLJAEJA_ID_GB) = ( SELECT GYEOLJAEJA_ID , GYEOLJAEJA_ID_GB FROM EA_USER_SEOLJEONG_M WHERE DAERIJA_ID = IN_GIANJA_ID AND DAERIJA_ID_GB = IN_GIANJA_ID_GB AND DAERIJA_YN = '1' )) THEN ORD ELSE 1 END) AS ORD , SUM(CASE WHEN GYEOLJAE_ST_GB = '1' THEN 1 ELSE 0 END) AS GYEOLJAE_CNT INTO V_MAX_ORD, V_ORD, V_GYEOLJAE_CNT FROM EA_GYEOLJAE_M A JOIN EA_GYEOLJAE_D B ON A.GYEOLJAE_NO = B.GYEOLJAE_NO WHERE A.GYEOLJAE_NO = IN_GYEOLJAE_NO;
이게 ORCLE 로직입니다.
현재 바꾼게 해당 사진과 아래의 소스입니다
SELECT @V_MAX_ORD = COUNT(1) , @V_ORD = MAX(CASE WHEN (@IN_DAERIJA_ID IS NULL AND B.GYEOLJAEJA_ID = @IN_GIANJA_ID AND B.GYEOLJAEJA_ID_GB = @IN_GIANJA_ID_GB) OR (@IN_DAERIJA_ID IS NOT NULL AND B.GYEOLJAEJA_ID = ( SELECT GYEOLJAEJA_ID FROM EA_USER_SEOLJEONG_M WHERE DAERIJA_ID = @IN_GIANJA_ID AND DAERIJA_ID_GB = @IN_GIANJA_ID_GB AND DAERIJA_YN = '1' ) AND @IN_DAERIJA_ID IS NOT NULL AND B.GYEOLJAEJA_ID_GB = ( SELECT GYEOLJAEJA_ID_GB FROM EA_USER_SEOLJEONG_M WHERE DAERIJA_ID = @IN_GIANJA_ID AND DAERIJA_ID_GB = @IN_GIANJA_ID_GB AND DAERIJA_YN = '1' )) THEN ORD ELSE 1 END) , @V_GYEOLJAE_CNT = SUM(CASE WHEN GYEOLJAE_ST_GB = '1' THEN 1 ELSE 0 END) FROM EA_GYEOLJAE_M A JOIN EA_GYEOLJAE_D B ON A.GYEOLJAE_NO = B.GYEOLJAE_NO WHERE A.GYEOLJAE_NO = @IN_GYEOLJAE_NO;
잘 안되는게 아래 사진 블럭처리 된 부분입니다.
SUM 함수나 COUNT 같은 함수를 사용할땐 오류가 없는데
빨간색 부분을 추가하면 집계 또는 하위 쿼리가 포함된 식에서는 집계 함수를 수행할 수 없습니다. 라고 오류가 발생합니다.
어떤식으로 바꿔야할 지 도움 주시면 감사하겠습니다.
소스가 지저분해 죄송합니다
-- Outer Join 으로 풀어보는건 어떤지요? -- SELECT @V_MAX_ORD = COUNT(1) , @V_ORD = MAX(CASE WHEN (@IN_DAERIJA_ID IS NULL AND B.GYEOLJAEJA_ID = @IN_GIANJA_ID AND B.GYEOLJAEJA_ID_GB = @IN_GIANJA_ID_GB) THEN 1 WHEN (@IN_DAERIJA_ID IS NOT NULL AND C.GYEOLJAEJA_ID IS NOT NULL) THEN 1 ELSE 0 END) , @V_GYEOLJAE_CNT = COUNT(CASE WHEN GYEOLJAE_ST_GB = '1' THEN 1 END) FROM EA_GYEOLJAE_M A JOIN EA_GYEOLJAE_D B ON A.GYEOLJAE_NO = B.GYEOLJAE_NO LEFT OUTER JOIN EA_USER_SEOLJEONG_M C ON C.GYEOLJAEJA_ID = B.GYEOLJAEJA_ID AND C.GYEOLJAEJA_ID_GB = B.GYEOLJAEJA_ID_GB AND C.DAERIJA_ID = @IN_GIANJA_ID AND C.DAERIJA_ID_GB = @IN_GIANJA_ID_GB AND C.DAERIJA_YN = '1' WHERE A.GYEOLJAE_NO = @IN_GYEOLJAE_NO ;