I have problem setting a prefix in a case statement.
Data set:
x
missionid:
5505
5506
select
CASE
WHEN m.EXTLOCATIONID is not null THEN '01' + convert(nvarchar(50),m.missionid)
ELSE tg.ID_ACTIVITY
END as Barcode2
from MISSION m
left join TASKGROUP tg with(nolock) on m.MMPICKLISTID = tg.ID
When I run this query my result is this:
Barcode2:
15505
15506
Desired result is this:
015505
015506
As one can see, the first zero is not shown in the result. How can I achieve this?
Advertisement
Answer
CASE
expression would always return one type so, you need to do conversion:
CASE WHEN m.EXTLOCATIONID is not null
THEN '01' + convert(nvarchar(50), m.missionid)
ELSE CONVERT(VARCHAR(255), tg.ID_ACTIVITY)
END as Barcode2