January 13, 2009 at 9:37 am
I have been trying to use the following Case statement to allow a string of vaules to be placed in a row in the same column. Problem is, once the Case finds the first 'Like' value that meets the criteria, it doesn't look any further. Some subs will have multiple codes on the same account; In other words, i need to pull back a AV and AW Then results on the same line.
rtrim((case
when bill_cde_list_osi like '% L# %'and A.BILL_CDE_OCI in ('W0') and B.serv_cde_oci in ('HY')
and C.serv_cde_oci in ('~X')then '|L#||1||W0||1|HY|||1|~X|||1|'
||''||
(CASE WHEN SERVS like '% AV %' THEN '|AV|||1'
WHEN SERVS like '% AZ %' THEN '|AZ|||1'
WHEN SERVS like '% AW %' THEN '|AW|||1'
WHEN SERVS like '% AX %' THEN '|AX|||1'
WHEN SERVS like '% AY %' THEN '|AY|||1'
WHEN SERVS like '% H1 %' THEN '|H1|||1'
WHEN SERVS like '% DE %' THEN '|DE|||1'
WHEN BILLS like '% X1 %' THEN '||X1||1'
WHEN BILLS like '% X3 %' THEN '||X3||1'
WHEN BILLS like '% X5 %' THEN '||X5||1'
WHEN BILLS like '% X7 %' THEN '||X7||1'
WHEN BILLS like '% W1 %' THEN '||W1||1' ELSE ' ' END)
else 'OOOPS' end),'|') Drop_Services
January 13, 2009 at 9:48 am
Then you need to chain the conditions, like this:
(CASE WHEN SERVS like '% AV %' THEN '|AV|||1' Else '' END)
+ (CASE WHEN SERVS like '% AZ %' THEN '|AZ|||1' Else '' END)
+ (CASE WHEN SERVS like '% AW %' THEN '|AW|||1' Else '' END)
...
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
January 13, 2009 at 9:56 am
Case will not look for succeeding WHEN clauses once any of the WHEN clauses satisfies to true.
you may want to write multiple Case statements with different sequence of WHEN clauses...
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply