I need to convert a complex decode statement to a case statement

Database

I need to convert a complex decode statement to a case statement

I'm not sure I have it right or i'm missing something.  Any suggestions are appreciated.

 

(decode
(decode(TB_LN_DZV_C2_PMT_SCHED_DIM.PMT_SCHED_TYP_CDE,'I',
decode(TB_LN_DZV_C2_PMT_SCHED_DIM.SCHED_EFF_DTE,TB_LN_DZU_C2_NOTE_RCD_DIM.INT_NXT_SCHED_DTE,'Y','N'),'T',

decode(TB_LN_DZV_C2_PMT_SCHED_DIM.SCHED_EFF_DTE,TB_LN_DZU_C2_NOTE_RCD_DIM.LN_PMT_NXT_SCHED_DTE,'Y','N')),'N',

decode
(decode(TB_LN_DZN_C2_NOTE_SCD_DIM.MAT_DTE, TB_LN_DZU_C2_NOTE_RCD_DIM.NXT_PMT_DUE_DTE,'Y', 'N') || TB_LN_DZV_C2_PMT_SCHED_DIM.MOST_RECNT_PMT_SCHED_IND,'YY','Y','N'),
decode(TB_LN_DZV_C2_PMT_SCHED_DIM.PMT_SCHED_TYP_CDE,'I',
decode(TB_LN_DZV_C2_PMT_SCHED_DIM.SCHED_EFF_DTE,TB_LN_DZU_C2_NOTE_RCD_DIM.INT_NXT_SCHED_DTE,'Y','N'),'T',
decode(TB_LN_DZV_C2_PMT_SCHED_DIM.SCHED_EFF_DTE,TB_LN_DZU_C2_NOTE_RCD_DIM.LN_PMT_NXT_SCHED_DTE,'Y','N')
)
)
)= 'Y'
)
)

this is the case statement I've created but I'm not so confident that it is correct. 
case when ([PMT_SCHED_TYP_CDE] = 'I' and
[SCHED_EFF_DTE] = [INT_NXT_SCHED_DTE])
then('Y')
when ([PMT_SCHED_TYP_CDE] = 'I' and
[SCHED_EFF_DTE] <> [INT_NXT_SCHED_DTE])
then('N')
when ([PMT_SCHED_TYP_CDE] <> 'I' and
[SCHED_EFF_DTE] = [LN_PMT_NXT_SCHED_DTE])
Then ('T')
when ([PMT_SCHED_TYP_CDE] <> 'I' and
[SCHED_EFF_DTE] <> [LN_PMT_NXT_SCHED_DTE])
Then ('T')
when   ([PMT_SCHED_TYP_CDE] = 'I' and
[SCHED_EFF_DTE] = [LN_PMT_NXT_SCHED_DTE])
then('Y')
when   ([PMT_SCHED_TYP_CDE] = 'I' and
[SCHED_EFF_DTE] <> [LN_PMT_NXT_SCHED_DTE])
then ('N')
when   ([PMT_SCHED_TYP_CDE] <> 'I' and
[SCHED_EFF_DTE] = [LN_PMT_NXT_SCHED_DTE])
then ('N')
when   ([PMT_SCHED_TYP_CDE] <> 'I' and
[SCHED_EFF_DTE] <> [LN_PMT_NXT_SCHED_DTE])
then ('N')
when([PMT_SCHED_TYP_CDE] = 'I' and
[MAT_DTE]=[NXT_PMT_DUE_DTE])
then('Y')
when([PMT_SCHED_TYP_CDE] = 'I' and
[MAT_DTE]<>[NXT_PMT_DUE_DTE])
Then('N')

when([MOST_RECNT_PMT_SCHED_IND] ='YY')
 then ('Y')
when([MOST_RECNT_PMT_SCHED_IND] <>'YY')
Then ('N')
when([PMT_SCHED_TYP_CDE] ='I' and
[SCHED_EFF_DTE] =[INT_NXT_SCHED_DTE])
then('Y')
when([PMT_SCHED_TYP_CDE] ='I' and
[SCHED_EFF_DTE] <>[INT_NXT_SCHED_DTE])
Then ('N')
 when([PMT_SCHED_TYP_CDE] ='I' and
[SCHED_EFF_DTE] <>[INT_NXT_SCHED_DTE])
Then ('T')
when([PMT_SCHED_TYP_CDE] <>'I' and
[SCHED_EFF_DTE] <>[INT_NXT_SCHED_DTE])
Then ('T')
 when ([PMT_SCHED_TYP_CDE] ='I' and
[SCHED_EFF_DTE] =[LN_PMT_NXT_SCHED_DTE])
then('Y')
 when ([PMT_SCHED_TYP_CDE] ='I' and
[SCHED_EFF_DTE] <>[LN_PMT_NXT_SCHED_DTE])
Then('N')
end

4 REPLIES
Enthusiast

Re: I need to convert a complex decode statement to a case statement

When you say that you "need" to convert the decode statement, are you aware that the most recent releases of Teradata have a "decode" function in TD_SYSFNLIB so that you don't have to convert it? Just making sure that you're aware of this in case you want to just use that instead...

Re: I need to convert a complex decode statement to a case statement

Well please don't shoot me - but I am converting business objects/oracle reports to cognos vs 11/db2 blue.  Simple decodes I can decipher, but this one has me stumped. 

 

Junior Contributor

Re: I need to convert a complex decode statement to a case statement

Decode is the same as a Valued Case, no need for a Searched Case, just replacing commas with when/then/else: 

decode(TB_LN_DZV_C2_PMT_SCHED_DIM.SCHED_EFF_DTE,TB_LN_DZU_C2_NOTE_RCD_DIM.LN_PMT_NXT_SCHED_DTE,'Y','N')'N'
=
case TB_LN_DZV_C2_PMT_SCHED_DIM.SCHED_EFF_DTE
when TB_LN_DZU_C2_NOTE_RCD_DIM.LN_PMT_NXT_SCHED_DTE then 'Y'
else 'N'
end

And you can nest it exactly like Decode.

 

But this expression is a mess, seems to be part of a WHERE-condition, you can probably simplify it quite a lot :-)

 

 

Teradata Employee

Re: I need to convert a complex decode statement to a case statement

BHull63 is right: Decode was built into Teradata starting with 14.0, and was available for user installation long before that.  And I sympathize with Dieter - I would hope there is a way to simplify this!  But if you really want the CASE version, here is what I get:

 

(case
      (case TB_LN_DZV_C2_PMT_SCHED_DIM.PMT_SCHED_TYP_CDE
            when 'I' then
                  (case TB_LN_DZV_C2_PMT_SCHED_DIM.SCHED_EFF_DTE
                  when TB_LN_DZU_C2_NOTE_RCD_DIM.INT_NXT_SCHED_DTE then 'Y'
                  else 'N' end)
            when 'T' then
                  (case TB_LN_DZV_C2_PMT_SCHED_DIM.SCHED_EFF_DTE
                  when TB_LN_DZU_C2_NOTE_RCD_DIM.LN_PMT_NXT_SCHED_DTE then 'Y'
                  else 'N' end)
      end)
      when 'N' then
            (case
                  (case TB_LN_DZN_C2_NOTE_SCD_DIM.MAT_DTE
                  when TB_LN_DZU_C2_NOTE_RCD_DIM.NXT_PMT_DUE_DTE then 'Y'
                  else 'N' end) || TB_LN_DZV_C2_PMT_SCHED_DIM.MOST_RECNT_PMT_SCHED_IND
            when 'YY' then 'Y'
            else 'N' end)
      else (case TB_LN_DZV_C2_PMT_SCHED_DIM.PMT_SCHED_TYP_CDE
            when 'I' then
                  (case TB_LN_DZV_C2_PMT_SCHED_DIM.SCHED_EFF_DTE
                  when TB_LN_DZU_C2_NOTE_RCD_DIM.INT_NXT_SCHED_DTE then 'Y'
                  else 'N' end)
            when 'T' then
                  (case TB_LN_DZV_C2_PMT_SCHED_DIM.SCHED_EFF_DTE
                  when TB_LN_DZU_C2_NOTE_RCD_DIM.LN_PMT_NXT_SCHED_DTE then 'Y'
                  else 'N' end)
            end)
end)