All Forums Database
MBR 29 posts Joined 01/13
20 Nov 2013
CASE STATEMENT

Hi,
i want to conver the below 'if' statement into 'CASE' statement can any one help me on this please.
IF (PLNV.LMV_BUS_TYPE_NEW) = ('COR')
    if LM_PASS_THRU_EXPRESS_IND = '3'
        use LV_LX_FUND_PRIN_BAL
    else if LV_LX_FUND_PRIN_BAL > 0
        use LV_LX_FUND_PRIN_BAL – LV_LX_PROJ_PRIN_REDUC – LV_LX_PRIN_REDUCT      
    else                                      
        use [LM_AMT_LOAN – LV_LX_PROJ_PRIN_REDUC – LV_LX_PRIN_REDUCT ]
ELSE IF PLNV.LMV_BUS_TYPE_NEW = ('WHS')
    use AD_CURR_PRIN_BAL
ELSE (PLNV.LMV_BUS_TYPE_NEW) NOT IN ('COR','WHS')
    use LM_AMT_LOAN
Appreciates quick reply. Thanks
Regards
MBR

Bala
VBurmist 96 posts Joined 12/09
20 Nov 2013

just substitute:
if -> case when
use -> then
and add the "end" at the end.

KVB 124 posts Joined 09/12
20 Nov 2013

CASE
WHEN PLNV.LMV_BUS_TYPE_NEW='COR' THEN
 CASE WHEN LM_PASS_THRU_EXPRESS_IND = '3' THEN LV_LX_FUND_PRIN_BAL
            ELSE CASE WHEN LV_LX_FUND_PRIN_BAL > 0 THEN LV_LX_FUND_PRIN_BAL – LV_LX_PROJ_PRIN_REDUC – LV_LX_PRIN_REDUCT
   ELSE [LM_AMT_LOAN – LV_LX_PROJ_PRIN_REDUC – LV_LX_PRIN_REDUCT ]
        END
 END
ELSE
 CASE WHEN PLNV.LMV_BUS_TYPE_NEW = ('WHS') THEN AD_CURR_PRIN_BAL
  ELSE  CASE WHEN PLNV.LMV_BUS_TYPE_NEW) NOT IN ('COR','WHS') THEN LM_AMT_LOAN
                       END
 END
END

KVB 124 posts Joined 09/12
20 Nov 2013

 
Paste this in SQL assistant to gain understaing of indentation.
CASE WHEN PLNV.LMV_BUS_TYPE_NEW='COR' THEN
   CASE WHEN LM_PASS_THRU_EXPRESS_IND = '3' THEN LV_LX_FUND_PRIN_BAL
               ELSE
               CASE WHEN LV_LX_FUND_PRIN_BAL > 0 THEN LV_LX_FUND_PRIN_BAL – LV_LX_PROJ_PRIN_REDUC – LV_LX_PRIN_REDUCT
      ELSE [LM_AMT_LOAN – LV_LX_PROJ_PRIN_REDUC – LV_LX_PRIN_REDUCT ]
           END
   END
ELSE
 CASE WHEN PLNV.LMV_BUS_TYPE_NEW = ('WHS') THEN AD_CURR_PRIN_BAL
    ELSE 
    CASE WHEN PLNV.LMV_BUS_TYPE_NEW) NOT IN ('COR','WHS') THEN LM_AMT_LOAN
             END
 END
END

MBR 29 posts Joined 01/13
20 Nov 2013

@bikky6...thanks for your quick response but the code is not working..

Bala

ulrich 816 posts Joined 09/09
20 Nov 2013

Hi MBR,
what do you exptect others to do?
You don't share DDL and data (as inserts!!!). So how could you expect that syntactially correct code is posted? 
And even now you don't share even the return code or error message... 

feel free to donate bitcoin:12kgAUHFUqvG2sQgaRBXFhCwyf9HXdkGud

VBurmist 96 posts Joined 12/09
20 Nov 2013

- The brackets [] should better be ()
- = ('WHS')    should better be     = 'WHS'
apart from that, I just agree with Ulrich.
Regards,
Vlad.

MBR 29 posts Joined 01/13
21 Nov 2013

@ulrich thanks for your comments. in future i will makesure that will post necessary information.
any how my problem got reolved just a small syntax mistake in the above query.
thanks everyone.

Bala

You must sign in to leave a comment.