locked
Selecting using CASE and Getting error when using Alias RRS feed

  • Question

  • Hi,

    Earlier today I was  Selecting  columns in muliple INNEr JOIN and was using a case  as following below.
    But the CASE that I was using I was keeping getthing the error close to '=' . I wonder why.

    Select a.Fname, a.lastName, b.Address1,
    b.Region =CASE   --giving error at this line '='
    WHEN Region='CA' THEN 'WestCost'
    WHEN Region='NY' THEN 'NorthEast'
    ELSE Region
    END

    FROM Customer a INNER JOIN  CustomerDetail b on a.CustomerID=b.CustomerID


    When I removed the alias b from Region like this.

    Region=Case
                WHEN .......

    I found no error and sp ran.
    1) what is the problem running with alias.
    2) if i am not doing something wrong   and in order to get the result i have to do it. Then wouldnt it be a problem if the other table in the join has the same column.

    Thanks



    Wednesday, September 24, 2008 5:04 AM

Answers

  •  

    Hi,

    column alias can't come with table prefix.

     

    instead you should be writing query like this

     

    Code Snippet

    Select a.Fname, a.lastName, b.Address1,
    Region =CASE   --giving error at this line '='

    WHEN b.Region='CA' THEN 'WestCost'
    WHEN b.Region='NY' THEN 'NorthEast'
    ELSE
    b.Region
    END

     

     

    the region in alias don't need followed by  table name.  you can give any xyz name.
    Wednesday, September 24, 2008 5:46 AM
  •  Mangal Pardeshi wrote:

     

    Hi,

    column alias can't come with table prefix.

     

    instead you should be writing query like this

     

    Code Snippet

    Select a.Fname, a.lastName, b.Address1,
    Region =CASE   --giving error at this line '='

    WHEN b.Region='CA' THEN 'WestCost'
    WHEN b.Region='NY' THEN 'NorthEast'
    ELSE
    b.Region
    END

     

     

    the region in alias don't need followed by  table name.  you can give any xyz name.

    This is not entirely accurate.  The problem with the original posted code is that the user was using the wrong case expression.  Case expression can be created in two differing syntaxes.  You are right about column alias though.  I think the OP just got confused with what type of case he was trying to implement.

     

    One syntax is:

    Case when something = something then

     

    The other syntax is

    Case Column When something then something.

     

    As you can see the difference is how you present the boolean value.  If the column value must meet multiple criteria or expressed amongst multiple columns then you must go with option 1; however, if you are simply checking a single column for a value then you can use option 2.

     

    This example works:

    Code Snippet

    declare @t table(

    id int identity(1,1),

    region varchar(10)

    )

     

    insert into @t values ('Eastern')

    insert into @t values ('Western')

    insert into @t values ('Northern')

    insert into @t values ('Southern')

     

    select case b.region

                      when 'Eastern'  then 'E'

                      when 'Western'  then 'W'

                      when 'Northern' then 'N'

                      when 'Southern' then 'S'

                else 'Unknown'

                end

    from @t b

     

     

    Wednesday, September 24, 2008 8:39 PM

All replies

  •  

    Hi,

    column alias can't come with table prefix.

     

    instead you should be writing query like this

     

    Code Snippet

    Select a.Fname, a.lastName, b.Address1,
    Region =CASE   --giving error at this line '='

    WHEN b.Region='CA' THEN 'WestCost'
    WHEN b.Region='NY' THEN 'NorthEast'
    ELSE
    b.Region
    END

     

     

    the region in alias don't need followed by  table name.  you can give any xyz name.
    Wednesday, September 24, 2008 5:46 AM
  • Thanks,
    Wednesday, September 24, 2008 7:03 PM
  •  Mangal Pardeshi wrote:

     

    Hi,

    column alias can't come with table prefix.

     

    instead you should be writing query like this

     

    Code Snippet

    Select a.Fname, a.lastName, b.Address1,
    Region =CASE   --giving error at this line '='

    WHEN b.Region='CA' THEN 'WestCost'
    WHEN b.Region='NY' THEN 'NorthEast'
    ELSE
    b.Region
    END

     

     

    the region in alias don't need followed by  table name.  you can give any xyz name.

    This is not entirely accurate.  The problem with the original posted code is that the user was using the wrong case expression.  Case expression can be created in two differing syntaxes.  You are right about column alias though.  I think the OP just got confused with what type of case he was trying to implement.

     

    One syntax is:

    Case when something = something then

     

    The other syntax is

    Case Column When something then something.

     

    As you can see the difference is how you present the boolean value.  If the column value must meet multiple criteria or expressed amongst multiple columns then you must go with option 1; however, if you are simply checking a single column for a value then you can use option 2.

     

    This example works:

    Code Snippet

    declare @t table(

    id int identity(1,1),

    region varchar(10)

    )

     

    insert into @t values ('Eastern')

    insert into @t values ('Western')

    insert into @t values ('Northern')

    insert into @t values ('Southern')

     

    select case b.region

                      when 'Eastern'  then 'E'

                      when 'Western'  then 'W'

                      when 'Northern' then 'N'

                      when 'Southern' then 'S'

                else 'Unknown'

                end

    from @t b

     

     

    Wednesday, September 24, 2008 8:39 PM
  • Hi Adam,
    Thanks for answering my question in advance, I just came to give the same syntex and ask the quesiton , which you already answer of useing the case in two different syntex.

    thanks.

    Saturday, September 27, 2008 10:28 AM