dynamic look up

[sameer11]

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

 

[ESquared]

Would you explain why you are doing this? What do AAA and BBBB and CCC mean? In the meantime:

CREATE TABLE AcctStripLen (ppid varchar(10), CharLen int)

INSERT AcctStripLen SELECT 'AAA', 11
UNION ALL SELECT 'BBBB', 12
UNION ALL SELECT 'CCC', 13
UNION ALL SELECT 'DDDDDD', 19
UNION ALL SELECT 'EEEE', 10
UNION ALL SELECT 'FFF', 13

SELECT
   A.AcctNo,
   A.Acc_Num,
   AcctStrip = IsNull(Substring(acc_num, 1, L.CharLen), acc_num)
FROM
   Accts A
   LEFT JOIN AcctStripLen L ON A.ppid = L.ppid

You might want to experiment with different ways to calculate AcctStrip to find the most efficient one. One alternative is:

AcctStrip = CASE WHEN L.ppid IS NULL THEN acc_num ELSE Substring(acc_num, 1, L.CharLen) END

The first way always performs a substring, but I am not sure, perhaps the second one would not, in which case it would be more efficient. I forget if CASE statements use short-circuit logic or not.

[COLOR=#aa88aa black]Cum catapultae proscriptae erunt tum soli proscript catapultas habebunt.[/color]

 

[sameer11]

Thank you. I figure another work around. I was try to strip the acct so I can join this accstrip to another table where the acct number is base by ppid(pipe id) that is what AAA, BBB, and CCCC are. They have different formats of acct num base on the pipe id. So, I was checking against the pipe id and the len I need.

Thanks again for the quick respond.

sameer

 

[ESquared]

So my answer didn't help you at all?

[COLOR=#aa88aa black]Cum catapultae proscriptae erunt tum soli proscript catapultas habebunt.[/color]