CODEWITHSUNDEEP
sqlsql-server
kgr
kgr

Reputation: 23

Issue while using Sql Subquery with case statements

    select Firstname,LastName,age, case
    when age < 40 and age >= 25 then 'Young' 
    when age < 60 and age >= 40 then 'No More Young'
    when age >= 60 then 'Retired'
    else 'Dont Care'
    END as flag
    from (select FirstName,LastName,DATEDIFF(year,2018-08-26,BirthDate) as age from ContosoRetailDW.dbo.DimCustomer)

I having problem with the piece of code while executing, Incorrect syntax near ')'.

I am learning SQL right now, so any help will be appreciated.

Upvotes: 0

Views: 50

Answers (2)

rcc
rcc

Reputation: 5

You should just add " as customers" at the end of your query. Reason is, every subquery must have an alias. Providing all else is correct (I'm not sure about this version of DATEDIFF) it should work.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269913

The problem is not the case expression. You are missing a table alias for the subquery. In addition, the date constant needs to be in single quotes:

select Firstname, LastName, age,
       (case when age < 40 and age >= 25 then 'Young' 
             when age < 60 and age >= 40 then 'No More Young'
             when age >= 60 then 'Retired'
             else 'Dont Care'
        end) as flag
from (select FirstName, LastName, DATEDIFF(year, '2018-08-26', BirthDate) as age
      from ContosoRetailDW.dbo.DimCustomer
     ) c;

Your case logic can be simplified by recognizing that the conditions are evaluated in order:

select Firstname, LastName, age,
       (case when age >= 25  and age < 40 then 'Young' 
             when age < 60 then 'No More Young'
             when age >= 60 then 'Retired'
             else 'Dont Care'
        end) as flag
from (select FirstName, LastName, DATEDIFF(year, '2018-08-26', BirthDate) as age
      from ContosoRetailDW.dbo.DimCustomer
     ) c;

This isn't much of a simplification with only three ranges being mapped.

Upvotes: 3

Related Questions