반응형
라는 문제에 봉착하게 됐다..
말이 상당히 난해하다. 예제를 보도록 하자.
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
근데 이렇게 말고.. 그냥 저 상태에서 어찌 안 될까 -_-;;????
이걸 어찌하면 좋을까나 ㅠ.ㅠ~꺅꺅
반응형
제 생각인데요
group by 절에 해당칼럼명만 넣어도 될 듯 한데요..
GROUP BY DECODE(tb1.c1, 'C', 'A', tb1.c1)
-> GROUP BY tb1.c1
답글
ㅎㅎ 그렇게 하면 결과가
곰 2
개 1
곰 1
이렇게 나온답니다. 인라인뷰나 서브쿼리를 잘 써서 하면 해결이 되는 문제이지만 기존에 있는 쿼리가 2,3백 라인이 넘어가는 상황에서 수정을 할려면.. 상당히 승질나지요. 거기다가 다이나믹 쿼리면..ㅡㅡ ㅎㅎ 그래서 최소한의 수정으로 어찌 안 되나 푸념해 본 거랍니다~.