|
안녕하세요
오라클쿼리에서 MS-SQL로 쿼리를 바꾸고 있는데
쿼리는
SELECT 'T' AS TYPE,
ISNULL (SUM (a.s1) + SUM (a.s2) + SUM (a.s3) + SUM (a.s4), 0) AS q1,
ISNULL (SUM (a.s1), 0) AS s1, ISNULL (SUM (a.s2), 0) AS s2,
ISNULL (SUM (a.s3), 0) AS s3, ISNULL (SUM (a.s4), 0) AS s4,
ISNULL (SUM ((SELECT COUNT (1)
FROM tb_qst_updt
WHERE updt_type_code = '001'
AND qst_seq = a.qst_seq
AND frst_register_id = 'k11')
),
0
) AS s5,
ISNULL (SUM ((SELECT COUNT (1)
FROM tb_qst_updt
WHERE updt_type_code = '002'
AND qst_seq = a.qst_seq
AND frst_register_id = 'k11')
),
0
) AS s6,
ISNULL (SUM ((SELECT COUNT (1)
FROM tb_qst_updt
WHERE updt_type_code = '003'
AND qst_seq = a.qst_seq
AND frst_register_id = 'k11')
),
0
) AS s7,
ISNULL (SUM ((SELECT COUNT (1)
FROM tb_qst_updt
WHERE updt_type_code = '004'
AND qst_seq = a.qst_seq
AND frst_register_id = 'k11')
),
0
) AS s8
FROM (SELECT a.qst_seq,
SUM (CASE
WHEN a.updt_type_code = '001'
THEN 1
ELSE 0
END) AS s1,
SUM (CASE
WHEN a.updt_type_code = '002'
THEN 1
ELSE 0
END) AS s2,
SUM (CASE
WHEN a.updt_type_code = '003'
THEN 1
ELSE 0
END) AS s3,
SUM (CASE
WHEN a.updt_type_code = '004'
THEN 1
ELSE 0
END) AS s4
FROM (SELECT a.qst_seq,
(SELECT TOP 1
updt_type_code
FROM tb_qst_updt
WHERE qst_seq = a.qst_seq
) AS updt_type_code
FROM tb_qst a
WHERE 1 = 1
AND qst_multi_yn = 'N'
AND qst_bank_yn = 'Y'
AND EXISTS (
SELECT 1
FROM tb_qst_updt
WHERE qst_seq = a.qst_seq
AND updt_type_code = a.updt_type_code
AND frst_register_id = 'k11')) a
GROUP BY a.qst_seq) a
UNION ALL
SELECT 'S' AS TYPE,
ISNULL (SUM (a.s1) + SUM (a.s2) + SUM (a.s3) + SUM (a.s4), 0) AS q1,
ISNULL (SUM (a.s1), 0) AS s1, ISNULL (SUM (a.s2), 0) AS s2,
ISNULL (SUM (a.s3), 0) AS s3, ISNULL (SUM (a.s4), 0) AS s4,
ISNULL (SUM ((SELECT COUNT (1)
FROM tb_qst_updt
WHERE updt_type_code = '001'
AND qst_seq = a.qst_seq
AND frst_register_id = 'k11')
),
0
) AS s5,
ISNULL (SUM ((SELECT COUNT (1)
FROM tb_qst_updt
WHERE updt_type_code = '002'
AND qst_seq = a.qst_seq
AND frst_register_id = 'k11')
),
0
) AS s6,
ISNULL (SUM ((SELECT COUNT (1)
FROM tb_qst_updt
WHERE updt_type_code = '003'
AND qst_seq = a.qst_seq
AND frst_register_id = 'k11')
),
0
) AS s7,
ISNULL (SUM ((SELECT COUNT (1)
FROM tb_qst_updt
WHERE updt_type_code = '004'
AND qst_seq = a.qst_seq
AND frst_register_id = 'k11')
),
0
) AS s8
FROM (SELECT a.qst_seq,
SUM (CASE
WHEN a.updt_type_code = '001'
THEN 1
ELSE 0
END) AS s1,
SUM (CASE
WHEN a.updt_type_code = '002'
THEN 1
ELSE 0
END) AS s2,
SUM (CASE
WHEN a.updt_type_code = '003'
THEN 1
ELSE 0
END) AS s3,
SUM (CASE
WHEN a.updt_type_code = '004'
THEN 1
ELSE 0
END) AS s4
FROM (SELECT a.qst_seq,
(SELECT TOP 1
updt_type_code
FROM tb_qst_updt
WHERE qst_seq = a.qst_seq
) AS updt_type_code
FROM tb_qst a, tb_opertn b
WHERE 1 = 1
AND a.opertn_ser = b.opertn_ser
AND a.qst_multi_yn = 'N'
AND a.qst_bank_yn = 'Y'
AND EXISTS (
SELECT 1
FROM tb_qst_updt
WHERE qst_seq = a.qst_seq
AND updt_type_code = a.updt_type_code
AND frst_register_id = 'k11')
AND b.opertn_year = '2015'
AND a.opertn_ser = 4
AND a.class_code = 'KC') a
GROUP BY a.qst_seq) a
이런식으로 되어있습니다.
돌려보니 에러나는 부분은
메시지 130, 수준 15, 상태 1, 줄 10
집계 또는 하위 쿼리가 포함된 식에서는 집계 함수를 수행할 수 없습니다.
ISNULL (SUM ((SELECT COUNT (1)
FROM tb_qst_updt
WHERE updt_type_code = '001'
AND qst_seq = a.qst_seq
AND frst_register_id = 'k11')
),
0
) AS s5,
이부분에서 에러가 나더군요
오라클은 SUM 안에 count를 계산할수 있지만
MS-SQL 은 안되네여TT
어떤식으로 고쳐야 할지 한 수 부탁드립니다.
|
