trouble with (select case when end) syntax :-(

  • 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

  • No need for CASE at all.

    RIGHT('0000' + cast(R.PostalCode as varchar(50), 5)

    _____________
    Code for TallyGenerator

  • 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