라는 문제에 봉착하게 됐다..
말이 상당히 난해하다. 예제를 보도록 하자.
WITH tb1 AS (
SELECT 'A' AS c1
, '1' AS c2
FROM DUAL
UNION ALL
SELECT 'A' AS c1
, '1' AS c2
FROM DUAL
UNION ALL
SELECT 'B' AS c1
, '1' AS c2
FROM DUAL
UNION ALL
SELECT 'C' AS c1
, '1' AS c2
FROM DUAL
),
tb2 AS (
SELECT 'A' AS c1
, '곰' AS c2
FROM DUAL
UNION ALL
SELECT 'B' AS c1
, '개' AS c2
FROM DUAL
UNION ALL
SELECT 'C' AS c1
, '닭' AS c2
FROM DUAL
)
SELECT
(SELECT c2 FROM tb2 WHERE tb2.c1 = DECODE(tb1.c1, 'C', 'A', tb1.c1)) AS c2
, SUM(tb1.c2) as cnt
FROM tb1
GROUP BY DECODE(tb1.c1, 'C', 'A', tb1.c1)
SELECT 'A' AS c1
, '1' AS c2
FROM DUAL
UNION ALL
SELECT 'A' AS c1
, '1' AS c2
FROM DUAL
UNION ALL
SELECT 'B' AS c1
, '1' AS c2
FROM DUAL
UNION ALL
SELECT 'C' AS c1
, '1' AS c2
FROM DUAL
),
tb2 AS (
SELECT 'A' AS c1
, '곰' AS c2
FROM DUAL
UNION ALL
SELECT 'B' AS c1
, '개' AS c2
FROM DUAL
UNION ALL
SELECT 'C' AS c1
, '닭' AS c2
FROM DUAL
)
SELECT
(SELECT c2 FROM tb2 WHERE tb2.c1 = DECODE(tb1.c1, 'C', 'A', tb1.c1)) AS c2
, SUM(tb1.c2) as cnt
FROM tb1
GROUP BY DECODE(tb1.c1, 'C', 'A', tb1.c1)
내가 원하는 결과는
c2 cnt
곰 3
개 1
이렇게 인데.. 위에 쿼리를 돌리면 오류가 떨어진다.
ORA-00979: not a GROUP BY expression
그룹바이 표현식에 없단다.. 망할.. ㅡㅡ..
SELECT 절에 스칼라만 안들어가면 DECODE로 GROUP BY 해도 상관 없는데 이런 일이.. ㅠㅠ
물론 인라인뷰로 빼면은 해결이 되긴 한다.
SELECT (SELECT c2 FROM tb2 WHERE tb2.c1 = tb1.c2) AS c2
, cnt
FROM (
SELECT
DECODE(tb1.c1, 'C', 'A', tb1.c1) AS c2
, SUM(tb1.c2) as cnt
FROM tb1
GROUP BY DECODE(tb1.c1, 'C', 'A', tb1.c1)
) tb1
, cnt
FROM (
SELECT
DECODE(tb1.c1, 'C', 'A', tb1.c1) AS c2
, SUM(tb1.c2) as cnt
FROM tb1
GROUP BY DECODE(tb1.c1, 'C', 'A', tb1.c1)
) tb1
근데 이렇게 말고.. 그냥 저 상태에서 어찌 안 될까 -_-;;????
이걸 어찌하면 좋을까나 ㅠ.ㅠ~꺅꺅
-
지나가는 나그네 2010.05.24 13:28
제 생각인데요
group by 절에 해당칼럼명만 넣어도 될 듯 한데요..
GROUP BY DECODE(tb1.c1, 'C', 'A', tb1.c1)
-> GROUP BY tb1.c1
