CASE STATEMENT

Database
MBR
Enthusiast

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

7 REPLIES
Teradata Employee

Re: CASE STATEMENT

just substitute:

if -> case when

use -> then

and add the "end" at the end.

KVB
Enthusiast

Re: CASE STATEMENT

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
Enthusiast

Re: CASE STATEMENT

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
Enthusiast

Re: CASE STATEMENT

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

Senior Supporter

Re: CASE STATEMENT

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... 

Teradata Employee

Re: CASE STATEMENT

- The brackets [] should better be ()

= ('WHS')    should better be     = 'WHS'

apart from that, I just agree with Ulrich.

Regards,

Vlad.

MBR
Enthusiast

Re: CASE STATEMENT

@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.