what swrong with this

Question

what swrong with this

nikki123

Ten Centuries

Points: 1338
More actions
November 12, 2009 at 10:46 am

#216698

select (case when substring(svc_ord_nbr,1,1) = 'V' and svc_ord_nbr like '%COG%' then 'VIDEO' else
case when substring(svc_ord_nbr,1,1) = 'Z' and svc_ord_nbr like '%COG%' then 'FDV' else
case when substring(svc_ord_nbr,1,1) not in ('V','Z') and svc_ord_nbr like '%COG%' then 'DATA'
case when svc_ord_nbr not like '%COG%' then 'VOICE' end) as Service_type,* from FALLOUTSDASHBOARD..DOTCOMNET_FALLOUT_REPORT (NOLOCK)
where master_order_id ='VA00038486874'

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

dba.nivali (11/12/2009)
select (case when substring(svc_ord_nbr,1,1) = 'V' and svc_ord_nbr like '%COG%' then 'VIDEO' else
case when substring(svc_ord_nbr,1,1) = 'Z' and svc_ord_nbr like '%COG%' then 'FDV' else
case when substring(svc_ord_nbr,1,1) not in ('V','Z') and svc_ord_nbr like '%COG%' then 'DATA'
case when svc_ord_nbr not like '%COG%' then 'VOICE' end) as Service_type,* from FALLOUTSDASHBOARD..DOTCOMNET_FALLOUT_REPORT (NOLOCK)
where master_order_id ='VA00038486874'

Okay, I give up, what is wrong with this query? Are you getting an error message? Is it not returning the data you expect?

More information would really help us help you.

Lowell SSC Guru Points: 323518 More actions · Answer 2

syntax.

a case statement has only a single CASE keyword between the CASE and END,

ie

CASE

WHEN [condition1] THEN [value]

WHEN [condition2] THEN [value]

ELSE [value3]

END

you can look in BOL for some other examples...it helps me when i layout each WHEN on a single line for readability.

basically this is what you wanted i think:

select

case

when substring(svc_ord_nbr,1,1) = 'V' and svc_ord_nbr like '%COG%'

then 'VIDEO'

when substring(svc_ord_nbr,1,1) = 'Z' and svc_ord_nbr like '%COG%'

then 'FDV'

when substring(svc_ord_nbr,1,1) not in ('V','Z') and svc_ord_nbr like '%COG%'

then 'DATA'

when svc_ord_nbr not like '%COG%' then 'VOICE'

end as Service_type,

*

from FALLOUTSDASHBOARD..DOTCOMNET_FALLOUT_REPORT (NOLOCK)

where master_order_id ='VA00038486874'

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

Geez, Lowell, you take all the fun out making the OP tell us what the problem is... 😉

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 4

And Lowell is correct, proper formatting of your code will help you find syntax problems, like missing END's for CASE's or parens, etc.

White space is your friend, not your enemy.

Lowell SSC Guru Points: 323518 More actions · Answer 5

Lynn Pettis (11/12/2009)
Geez, Lowell, you take all the fun out making the OP tell us what the problem is... 😉

mea culpa; it was low hanging fruit and i'm try to bump my post count to be half of yours 🙂

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

SQLRNNR SSC Guru Points: 281334 More actions · Answer 6

Lowell (11/12/2009)
Lynn Pettis (11/12/2009)
Geez, Lowell, you take all the fun out making the OP tell us what the problem is... 😉
mea culpa; it was low hanging fruit and i'm try to bump my post count to be half of yours 🙂

Lowell, you're gonna need to start posting while on vacation, from your CrackBerry, and while you sleep - just like Lynn

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events