help on case statement please

[ss7415]

I need to add a W to a field and i get an error

case
when qa.Wo_nbr is not null and ord.part is not null then 'W'+ qa.wo_nbr else 0 end as a

i get an invalid number error, but part and wo_nbr are both strings

 

[PHV]

Perhaps this (ANSI SQL syntax)?
CASE
WHEN qa.Wo_nbr IS NOT NULL AND ord.part IS NOT NULL THEN 'W' || qa.wo_nbr ELSE [!]'[/!]0[!]'[/!]
END AS a


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[AlexCuse]

Try putting quotes around the 0. Like so '0'

Both parts of a CASE must be same data type.

And in the future, try forum183 (assuming this is a SQL Server question)

Hope this helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[gmmastros]

Try...

case 
when qa.Wo_nbr is not null and ord.part  is not null then  'W'+ qa.wo_nbr else [!]'[/!]0[!]'[/!] end as a

Notice the single quotes around the 0. The first part of the case statement returns a string, but the second returns a number. By putting single quotes around the 0, you are now returning a string that just so happens to be a number. The important point to remember is that all return values from the case statement should have the same data type.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[KeertanaCR]

I guess you need to say not sure though.
I think it is considering 0 in else as number and giving this error.


case
when qa.Wo_nbr is not null and ord.part is not null then 'W'+ qa.wo_nbr else '0' end as a

hope this helps
thank you
keerthana

 

[ss7415]

wow. thanks so very much, sir or maam