데이터베이스 질문과 답변
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 함수는 왜 사용했을까요?
- 빼는게 좋을 듯 하네요.
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 ;
) 버튼을 클릭하여 작성 하시면 됩니다.