by 날아라갑을 [SQL Query] GROUPBY 그룹 정렬 [2016.11.29 16:18:26]
오라클 툴에서는 실행이 잘되지만 .. 이클립스 xml쪽에서는 >> GROUP BY 표현식이 아닙니다. 에러메시지를 보내네요 ;;
SELECT FLV.MEANING GUBUN,
DECODE(#P_CO_TYPE#, 'E', ESTABLISHMENT_NAME, 'C', COMPANY_NAME) E_NAME,
YEAR,
OUT_TYPE,
DETAIL_TYPE,
TAX_TYPE,
SUM(QUARTER_AMOUNT1) QUARTER_AMOUNT1,
SUM(QUARTER_AMOUNT2) QUARTER_AMOUNT2,
SUM(HALF_AMOUNT1) HALF_AMOUNT1,
SUM(QUARTER_AMOUNT3) QUARTER_AMOUNT3,
SUM(QUARTER_AMOUNT4) QUARTER_AMOUNT4,
SUM(HALF_AMOUNT2) HALF_AMOUNT2,
SUM(TOTAL_AMOUNT) TOTAL_AMOUNT
FROM LNG.KTX_EST_SUP_AMT_V KES
,LNG.KAP_LOOKUP_VALUES FLV
WHERE KES.ORG_ID = #P_ORG_ID#
AND (TO_CHAR(KES.ESTABLISHMENT_REG_NO) = #P_ESTABLISHMENT_REG_NO# OR KES.COMPANY_CODE = #COMPANY_CODE# )
AND KES.YEAR = #P_YEAR#
AND KES.ORG_ID = FLV.ORG_ID(+)
AND FLV.LOOKUP_TYPE(+) = 'KTX_CO_TYPE'
AND FLV.LOOKUP_CODE(+) = #P_CO_TYPE#
GROUP BY FLV.MEANING, DECODE(#P_CO_TYPE#, 'E', ESTABLISHMENT_NAME, 'C', COMPANY_NAME), YEAR, OUT_TYPE, DETAIL_TYPE, TAX_TYPE
ORDER BY KES.OUT_TYPE, KES.DETAIL_TYPE, KES.TAX_TYPE
변수 때문인 듯 하네요.
- 인라인뷰를 이용해 오류를 회피해 보세요.
TO_CHAR 함수는 왜 사용했을까요?
- 빼는게 좋을 듯 하네요.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 | SELECT GUBUN , E_NAME , YEAR , OUT_TYPE , DETAIL_TYPE , TAX_TYPE , SUM (QUARTER_AMOUNT1) QUARTER_AMOUNT1 , SUM (QUARTER_AMOUNT2) QUARTER_AMOUNT2 , SUM (HALF_AMOUNT1) HALF_AMOUNT1 , SUM (QUARTER_AMOUNT3) QUARTER_AMOUNT3 , SUM (QUARTER_AMOUNT4) QUARTER_AMOUNT4 , SUM (HALF_AMOUNT2) HALF_AMOUNT2 , SUM (TOTAL_AMOUNT) TOTAL_AMOUNT FROM ( SELECT FLV.MEANING GUBUN , DECODE(#P_CO_TYPE#, 'E' , ESTABLISHMENT_NAME, 'C' , COMPANY_NAME) E_NAME , YEAR , OUT_TYPE , DETAIL_TYPE , TAX_TYPE , QUARTER_AMOUNT1 , QUARTER_AMOUNT2 , HALF_AMOUNT1 , QUARTER_AMOUNT3 , QUARTER_AMOUNT4 , HALF_AMOUNT2 , TOTAL_AMOUNT FROM LNG.KTX_EST_SUP_AMT_V KES , LNG.KAP_LOOKUP_VALUES FLV WHERE KES.ORG_ID = #P_ORG_ID# AND (KES.ESTABLISHMENT_REG_NO = #P_ESTABLISHMENT_REG_NO# OR KES.COMPANY_CODE = #COMPANY_CODE#) AND KES. YEAR = #P_YEAR# AND KES.ORG_ID = FLV.ORG_ID(+) AND FLV.LOOKUP_TYPE(+) = 'KTX_CO_TYPE' AND FLV.LOOKUP_CODE(+) = #P_CO_TYPE# ) GROUP BY GUBUN, E_NAME, YEAR , OUT_TYPE, DETAIL_TYPE, TAX_TYPE ORDER BY OUT_TYPE, DETAIL_TYPE, TAX_TYPE ; |