June 25, 2008 at 11:29 pm
What is the correct way to use in a case when inside a select query?
Select
S.SaleID
,S.UnitID
,R.PostalCode
,case R.PostalCode
When < 10 then '000' + cast(R.PostalCode as varchar(50))
When < 100 then '00' + Cast(R.PostalCode as varchar(50))
When < 1000 then '0' + Cast(R.PostalCode as varchar(50))
When < 10000 then Cast(R.PostalCode as varchar(50))
from
DCSLive.dbo.Sale S (NoLock)
Inner Join DCSLive.dbo.Client C (NoLock)
On S.ClientID = C.ClientID
Inner Join DCSLive.dbo.Address A (NoLock)
On A.ClientID = C.ClientID
Inner Join DCSLive.dbo.Region R (NoLock)
On A.RegionID = R.RegionID
June 26, 2008 at 4:27 am
No need for CASE at all.
RIGHT('0000' + cast(R.PostalCode as varchar(50), 5)
_____________
Code for TallyGenerator
June 26, 2008 at 4:40 am
Wow, should have thought of that!!!!!!!
and this simply because the original designer saved the postal code as int and not varchar(5)
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply