CODEWITHSUNDEEP
sql-server-2008
Nate Pet
Nate Pet

Reputation: 46222

sql server CASE - getting Incorrect syntax near the keyword 'select'

I am using SQL Server 2008. I have the following query. What I like to do is if when there is a record that EXIST, I like to assign Actual Date to 'N/A' else I like to have it be the CreatedBy date that is in Hand.

   select ActualDate = (CASE
                         WHEN EXISTS
                         (select NeedHand from Truck 
                           where Truck.NeedHand = 0 and Truck.ID = '34343')
                         THEN
                          'N/A'
                         ELSE
                          SELECT  CreatedByDate from Hand  where ID = '34343'
                         END )

I get the following message: Incorrect syntax near the keyword 'select'.

Upvotes: 1

Views: 3581

Answers (3)

user1499112
user1499112

Reputation:

Option #1

SELECT
(
    CASE
        WHEN 
                Exists(SELECT top 1 NeedHand FROM Truck 
                WHERE Truck.NeedHand = 0 and Truck.ID = '34343')
        THEN
                'N/A'
        ELSE
                (SELECT TOP 1 CreatedByDate FROM Hand WHERE ID = '34343')
    END 
)

Option #2

SELECT
(
    CASE
        WHEN 
                ((SELECT top 1 NeedHand FROM Truck 
                WHERE Truck.NeedHand = 0 and Truck.ID = '34343') IS NOT NULL)
        THEN
                'N/A'
        ELSE
                (SELECT TOP 1 CreatedByDate FROM Hand WHERE ID = '34343')
    END 
)

Added Top 1 to avoid the following issue

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

Upvotes: 0

Abe Miessler
Abe Miessler

Reputation: 85046

There are a number of problems with your query. The version below should take care of all of them:

select  ActualDate = 
CASE 
     WHEN EXISTS(
            select NeedHand from Truck 
            where Truck.NeedHand = 0 and Truck.ID = '34343'
            )
     THEN
               'N/A'
     ELSE
               (SELECT  CreatedByDate from Hand  where ID = '34343')
END

I had to :

  1. change EXIST to EXISTS
  2. change END CASE to END
  3. add parens around your last query.

Upvotes: 0

Paddy
Paddy

Reputation: 33857

Your EXIST should be EXISTS, I think.

Upvotes: 3

Related Questions