Hi,
I am using a case statement and hard coding it before but want to change this to do dynamical. But how can I do this when I have more than one value.
Intial I did this Hard coding in the case statment in my select clause as
AcctStrip =
(case
when ppid = 'AAA' then substring(acc_num, 1, 11)
when ppid = 'BBBB' then substring(acc_num, 1, 12)
when ppid = 'CCC' then substring(acc_numb, 1, 13)
when ppid = 'DDDDDD' then substring(acc_num, 1, 19)
when ppid = 'EEEE' then substring(acc_num, 1, 10)
when ppid = 'FFF' then substring(acc_num, 1, 13)
else
acct_num
end )
I want to do something like this but won't work since it has more than 1 value
AcctStrip =
(case
when ppid in (select ppid from table2) then
substring(acc_num, 1, (select acct_len from table2))
else
acct_num
end)
Any help is appreciated.
Thanks,
Sameer
I am using a case statement and hard coding it before but want to change this to do dynamical. But how can I do this when I have more than one value.
Intial I did this Hard coding in the case statment in my select clause as
AcctStrip =
(case
when ppid = 'AAA' then substring(acc_num, 1, 11)
when ppid = 'BBBB' then substring(acc_num, 1, 12)
when ppid = 'CCC' then substring(acc_numb, 1, 13)
when ppid = 'DDDDDD' then substring(acc_num, 1, 19)
when ppid = 'EEEE' then substring(acc_num, 1, 10)
when ppid = 'FFF' then substring(acc_num, 1, 13)
else
acct_num
end )
I want to do something like this but won't work since it has more than 1 value
AcctStrip =
(case
when ppid in (select ppid from table2) then
substring(acc_num, 1, (select acct_len from table2))
else
acct_num
end)
Any help is appreciated.
Thanks,
Sameer