CODEWITHSUNDEEP
sql
Chris
Chris

Reputation: 23

CASE Syntax error when > is used in the WHEN clause

This Query runs fine when I do not have the ">" symbol in the WHEN clause however I need this to work when the ProposalDate is any date later than '2017-04-10 00:00:00.000'. Not sure why I am getting a Syntax error when I add the > into the WHEN clause.

SELECT *,
    CASE ProposalDate WHEN > '2017-04-10 00:00:00.000'
        THEN (OrderTotal - ProposalWholesalePrice) +
             (ProposalWholesalePrice * 1.02)
        ELSE OrderTotal    
    END "New_OrderTotal"
FROM webservices.dbo.DT_Open_Orders_Baseprice
WHERE ProposalSerialNumber = '639036'

Upvotes: 2

Views: 38

Answers (3)

Mureinik
Mureinik

Reputation: 311893

The shorthand case syntax doesn't use any operators, just a series of values which are evaluated with the = condition. If you want to use other operators, you have to use the full case syntax:

SELECT *, 
       CASE WHEN ProposalDate > '2017-04-10 00:00:00.000' 
            THEN (OrderTotal - ProposalWholesalePrice) + 
                 (ProposalWholesalePrice * 1.02) 
           ELSE OrderTotal 
       END "New_OrderTotal"
FROM   webservices.dbo.DT_Open_Orders_Baseprice 
WHERE  ProposalSerialNumber = '639036'

Upvotes: 0

Miguel Patreze
Miguel Patreze

Reputation: 65

Have you ever tried to put the "WHEN" before the column name? For example:

SELECT *, CASE WHEN ProposalDate > '2017-04-10 00:00:00.000'

THEN (OrderTotal - ProposalWholesalePrice) + (ProposalWholesalePrice * 1.02) ELSE OrderTotal

END "New_OrderTotal" FROM webservices.dbo.DT_Open_Orders_Baseprice WHERE ProposalSerialNumber = '639036'

Upvotes: 0

Sergey Kalinichenko
Sergey Kalinichenko

Reputation: 726849

SQL CASE expression has two forms:

  • The simple CASE expression, and
  • The searched CASE expression.

Simple CASE, i.e. CASE ProposalDate WHEN ... cannot be used with comparison operators; you need a searched CASE instead:

SELECT
    *
,   CASE
        WHEN ProposalDate > '2017-04-10 00:00:00.000'
        THEN (OrderTotal - ProposalWholesalePrice) + (ProposalWholesalePrice * 1.02)
        ELSE OrderTotal
    END "New_OrderTotal"
FROM webservices.dbo.DT_Open_Orders_Baseprice
WHERE ProposalSerialNumber = '639036'

Upvotes: 2

Related Questions