karim.boulahfa (8/14/2013)
Louis Hillebrand (8/14/2013)
For the formatting you can try this,Use the case to get the correct character in front (or an empty string), and then add the incident_ref in 5 digits. (include leading zero's if needed)
CASE Event_Type
WHEN 'c' THEN 'R'
WHEN 'i' THEN''
ELSE 'P'
END + RIGHT('00000' + CAST(incident_ref As nvarchar(5) ), 5)
Louis
I have tweak the qeury and it comes to this;
select event_type,
'Event_Type' = CASE
WHEN 'c' THEN 'R'
WHEN 'i' THEN ''
ELSE 'P'
END + RIGHT('00000' + CAST(incident_ref As nvarchar(5) ), 5)
from incident, inc_data
but get the following error;
Msg 4145, Level 15, State 1, Line 2
An expression of non-boolean type specified in a context where a condition is expected, near 'THEN'.
You need an input_expression for the CASE expression, see BOL.
Simple CASE expression:
CASE input_expression
WHEN when_expression THEN result_expression [ ...n ]
[ ELSE else_result_expression ]
END
So, for your code it will look like this:
select event_type,
'Event_Type' = CASE event_type
WHEN 'c' THEN 'R'
WHEN 'i' THEN ''
ELSE 'P'
END + RIGHT('00000' + CAST(incident_ref As nvarchar(5) ), 5)
from incident, inc_data