oracle 을 impala로 변경하고 싶습니다
-
게시물 수정 , 삭제는 로그인 필요
oracle sql 을 impala sql로 변경하고 싶습니다
오라클 안에서는 (+)를이용 해서 inner join이랑 outer join이랑 같이 사용이 가능한데
임팔라에서는 어떻게 해야 하는지 잘 모르겠습니다
쿼리는 아래와 같습니다
SELECT '202310' WORK_YM
, CASE WHEN FAC_ID IN ('M14B') THEN 'M14' ELSE FAC_ID END AS FAC_ID
, FAB_DEVICE_CD, STD_OPER_ID, SUM(AMT)/SUM(QTY) AS WF_PER_MATL_AMT
FROM (
SELECT WORK_YM, A.FAC_CD AS FAC_ID, B.FAB_DEVICE_CD, OPER_CD AS STD_OPER_ID
, SUM(PROC_QTY) AS QTY
, SUM(CASE WHEN AMT_UNIT='CNY' THEN PROC_AMT * CNY_RATE ELSE PROC_AMT END) AS AMT
FROM GMS.GMS_COI_EQPMAT A,GMS.GMS_STD_PRDMAS B, DMC_CODE02 C
,GMS.GMS_STD_FACSTEP D
WHERE A.WORK_YM BETWEEN TO_CHAR(ADD_MONTHS(TO_DATE('202310', 'YYYYMM'),-2),'YYYYMM') AND '202310' --상단조건
-- AND A.FAC_CD=A.FAB_CD
AND A.WORK_YM = C.WORK_MM
AND A.COST_PROD_ID = B.PROD_ID(+)
AND CASE WHEN A.FAC_CD IN ('M14B') THEN 'M14' ELSE A.FAC_CD END = D.FAC_CD
AND A.OPER_CD = D.STEP_CD
AND CASE WHEN A.FAC_CD IN ('M14B') THEN 'M14' ELSE A.FAC_CD END IN ('M14') --상단조건
AND B.FAMILY_CD IN ('DRAM') --추가 2022.10.27
AND B.TECH_CD IN ('RG') --추가 2022.10.27
GROUP BY WORK_YM, A.FAC_CD, B.FAB_DEVICE_CD, OPER_CD
)
WHERE 1=1
AND AMT>0 AND QTY>0
GROUP BY CASE WHEN FAC_ID IN ('M14B') THEN 'M14' ELSE FAC_ID END, FAB_DEVICE_CD, STD_OPER_ID
#oracle dmf