April 26, 2011 at 2:05 am
Hi
I have data in a table which is laid out awkwardly.
I think I have a solution using a CASE statement but despite attempts with CAST and Convert, I keep getting the following error:
Conversion failed when converting the varchar value '02 00:00:00 ' to data type int.
I assume my problem has to do with the colons.
Attached is a spreadsheet showing sample data and the desired result set, plus an explanation of what I'm trying to achieve.
Also attached is a word document with some sample data in a union statement.
Many thanks in advance
April 26, 2011 at 2:22 am
The values returned by each condition of a CASE construct must be of the same data type. Change
,CASE When APrio1 = 4 then RespT1
When APrio2 = 4 then RespT2
When APrio3 = 4 then RespT3
When APrio4 = 4 then RespT4
When APrio5 = 4 then RespT5
else 0
end as P4
to
,CASE When APrio1 = 4 then RespT1
When APrio2 = 4 then RespT2
When APrio3 = 4 then RespT3
When APrio4 = 4 then RespT4
When APrio5 = 4 then RespT5
else '0' -- or CAST(NULL AS CHAR(15))
end as P4
Edit: changed
else ''
to
else '0'
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
April 26, 2011 at 7:54 pm
Man, talk about wood for the trees. I was so focussed on fixing the problem by using converts and casts in the upper part of the formula I didn't see the obvious problem at the very bottom.
Many thanks for pointing the error out. All is working perfectly now.
Paul
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply