Home » SQL & PL/SQL » SQL & PL/SQL » CASE STMT VS DECODE (ORACLE 10G)
CASE STMT VS DECODE [message #577952] Fri, 22 February 2013 01:18 Go to next message
faijurias@gmail.com
Messages: 4
Registered: February 2013
Junior Member
Hi Expert

Can you please look into the below CASE stmt . Here the query Running long time pls help to any other method to achive this goal.


CASE
                   WHEN pm.plevel = 'plc' and MTYPE = 'added' and
                        MSUBTYPE is NULL then
                    'PLCY_PREM_ADD'
                   WHEN pm.plevel = 'plc' and
                        MTYPE = 'returned' and MSUBTYPE is NULL then
                    'PLCY_PREM_RET'
                   WHEN pm.plevel = 'plc' and
                        MTYPE = 'reversed' and
                        MSUBTYPE = 'added' then
                    'PLCY_PREM_RVRSD_ADD'
                   WHEN pm.plevel = 'plc' and
                        MTYPE = 'reversed' and
                        MSUBTYPE = 'returned' then
                    'PLCY_PREM_RVRSD_RET'
                   WHEN pm.plevel = 'plc' and
                        MTYPE = 'reversed' and
                        MSUBTYPE = 'reversed' then
                    'PLCY_PREM_RVRSD_RVRSD'
                   WHEN pm.plevel <> 'plc' and
                        cv.plcdetail_id is NOT NULL and
                        cv.driveroid is NULL and MTYPE = 'added' and
                        MSUBTYPE is NULL then
                    'PLCY_COV_PREM_ADD'
                   WHEN pm.plevel <> 'plc' and
                        cv.plcdetail_id is NOT NULL and
                        cv.driveroid is NULL and MTYPE = 'returned' and
                        MSUBTYPE is NULL then
                    'PLCY_COV_PREM_RET'
                   WHEN pm.plevel <> 'plc' and
                        cv.plcdetail_id is NOT NULL and
                        cv.driveroid is NULL and MTYPE = 'reversed' and
                        MSUBTYPE = 'added' then
                    'PLCY_COV_PREM_RVRSD_ADD'
                   WHEN pm.plevel <> 'plc' and
                        cv.plcdetail_id is NOT NULL and
                        cv.driveroid is NULL and MTYPE = 'reversed' and
                        MSUBTYPE = 'returned' then
                    'PLCY_COV_PREM_RVRSD_RET'
                   WHEN pm.plevel <> 'plc' and
                        cv.plcdetail_id is NOT NULL and
                        cv.driveroid is NULL and MTYPE = 'reversed' and
                        MSUBTYPE = 'reversed' then
                    'PLCY_COV_PREM_RVRSD_RVRSD'
                   WHEN pm.plevel <> 'plc' and
                        cv.plcdetail_id is NOT NULL and
                        cv.driveroid is NOT NULL and MTYPE = 'added' and
                        MSUBTYPE is NULL then
                    'DRVR_COV_PREM_ADD'
                   WHEN pm.plevel <> 'plc' and
                        cv.plcdetail_id is NOT NULL and
                        cv.driveroid is NOT NULL and MTYPE = 'returned' and
                        MSUBTYPE is NULL then
                    'DRVR_COV_PREM_RET'
                   WHEN pm.plevel <> 'plc' and
                        cv.plcdetail_id is NOT NULL and
                        cv.driveroid is NOT NULL and MTYPE = 'reversed' and
                        MSUBTYPE = 'added' then
                    'DRVR_COV_PREM_RVRSD_ADD'
                   WHEN pm.plevel <> 'plc' and
                        cv.plcdetail_id is NOT NULL and
                        cv.driveroid is NOT NULL and MTYPE = 'reversed' and
                        MSUBTYPE = 'returned' then
                    'DRVR_COV_PREM_RVRSD_RET'
                   WHEN pm.plevel <> 'plc' and
                        cv.plcdetail_id is NOT NULL and
                        cv.driveroid is NOT NULL and MTYPE = 'reversed' and
                        MSUBTYPE = 'reversed' then
                    'DRVR_COV_PREM_RVRSD_RVRSD'
                   WHEN pm.plevel <> 'plc' and
                        cv.plcdetail_id is NULL and MTYPE = 'added' and
                        MSUBTYPE is NULL then
                    'RSK_COV_PREM_ADD'
                   WHEN pm.plevel <> 'plc' and
                        cv.plcdetail_id is NULL and
                        MTYPE = 'returned' and MSUBTYPE is NULL then
                    'RSK_COV_PREM_RET'
                   WHEN pm.plevel <> 'plc' and
                        cv.plcdetail_id is NULL and
                        MTYPE = 'reversed' and
                        MSUBTYPE = 'added' then
                    'RSK_COV_PREM_RVRSD_ADD'
                   WHEN pm.plevel <> 'plc' and
                        cv.plcdetail_id is NULL and
                        MTYPE = 'reversed' and
                        MSUBTYPE = 'returned' then
                    'RSK_COV_PREM_RVRSD_RET'
                   WHEN pm.plevel <> 'plc' and
                        cv.plcdetail_id is NULL and
                        MTYPE = 'reversed' and
                        MSUBTYPE = 'reversed' then
                    'RSK_COV_PREM_RVRSD_RVRSD'
                   else
                    Null
                 END as CLM_N

Regards
faijurias@gmail.com



[Edit MC: add code tags]

[Updated on: Fri, 22 February 2013 01:37] by Moderator

Report message to a moderator

Re: CASE STMT VS DECODE [message #577958 is a reply to message #577952] Fri, 22 February 2013 01:37 Go to previous message
Michel Cadot
Messages: 59405
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Welcome to the forum.

Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" or "Preview Quick Reply" button to verify.
Also always post your Oracle version, with 4 decimals.

If your query lasts long, this is not because of this expression.

Regards
Michel
Previous Topic: Count data
Next Topic: How to use default value
Goto Forum:
  


Current Time: Mon Oct 20 21:36:33 CDT 2014

Total time taken to generate the page: 0.10229 seconds