i have a query which is in oracle pls convert and give me in postgresql format.
SELECT MST.TW_TRAN_NO ,NVL(DEPT_RCPT_NO,'--'),CONSUMER_NO,NVL(CONSUMER_NAME,'---'),
UPPER( DECODE (PAY_MODE,'C','Cash','D','DD','CD','CASH/DD','Q','Chq','R','Card')),
TOT_AMT,DEPT_SHORT_DESC,TRAN_STATUS,NVL(USER_CHARG ES,0),shift_code,
NVL(DECODE(PAY_MODE,'R',(NVL(CHQDD_CARD_AMT,0)-NVL(TOT_AMT,0)-
NVL(USER_CHARGES,0))),0)
FROM TW_TRANS MST,TW_DEPT_MST, TW_TRANS_PAY_DTLS DTL WHERE
MST.TW_TRAN_NO=DTL.TW_TRAN_NO(+) AND
MST.DEPT_CODE=TW_DEPT_MST.DEPT_CODE
AND MST.TW_TRAN_DATE =TO_DATE('26-11-2010','dd-mm-yyyy')
AND STAFF_CODE='OPR237'
AND CENTRE_CODE='AKPM'
AND TRAN_STATUS='N' ORDER BY MST.TW_TRAN_N
i tried but the result is not correct in postgresql:
The query i tried is :
SELECT MST.TW_TRAN_NO,COALESCE(DEPT_RCPT_NO,'--'),CONSUMER_NO,COALESCE(CONSUMER_NAME,'---'),TOT_AMT,DEPT_SHORT_DESC,TRAN_STATUS,COALESCE(US ER_CHARGES,0),shift_code,
coalesce(case when PAY_MODE = 'R' then (coalesce(CHQDD_CARD_AMT,0)-coalesce(TOT_AMT,0)- coalesce(USER_CHARGES,0)) end ,0),
UPPER(
CASE WHEN PAY_MODE='C' THEN 'CASH'
WHEN PAY_MODE='D' THEN 'DD'
WHEN PAY_MODE='CD' THEN 'CASH/DD'
WHEN PAY_MODE='Q' THEN 'CHQ'
WHEN PAY_MODE='R' THEN 'CARD'
END)
FROM CSC_TRANS MST,CSC_DEPT_MST, CSC_TRANS_PAY_DTLS DTL WHERE MST.TW_TRAN_NO=DTL.TW_TRAN_NO outer join a on AND MST.DEPT_CODE=CSC_DEPT_MST.DEPT_CODE
AND MST.TW_TRAN_DATE =TO_DATE('2010-11-26','YYYY-MM-DD') AND STAFF_CODE='OPR237' AND CENTRE_CODE='AKPM' AND TRAN_STATUS='N' ORDER BY MST.TW_TRAN_NO
Thanks & Regards,
Phani Kumar.