By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,998 Members | 2,815 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,998 IT Pros & Developers. It's quick & easy.

please convert this query to postgresql which is already in oracle

P: 1
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.
Jan 5 '11 #1
Share this question for a faster answer!
Share on Google+

Post your reply

Sign in to post your reply or Sign up for a free account.