Solved

Incorrect syntax near the keyword 'LIKE'

Posted on 2009-05-13
4
1,494 Views
Last Modified: 2012-05-06
Sorry, I'm not seeing where my syntax is incorrect on this simple query:

SELECT
      CASE strMerchItemName
            WHEN LIKE 'group%' THEN GroupItem
            WHEN LIKE 'trade%' THEN TradeItem
            ELSE EverythingElse
            END AS ItemType,
      SUM(curPrice) AS Total
FROM tblMerchItem
GROUP BY strMerchItemName

But I'm getting the following error:
"Msg 156, Level 15, State 1, Line 3
Incorrect syntax near the keyword 'LIKE'."

I don't see what's wrong with my CASE statement and the LIKE statements.  Can somebody please show me where I'm making my mistake(s)?
0
Comment
Question by:sevenpeaks
  • 2
4 Comments
 
LVL 75

Accepted Solution

by:
Aneesh Retnakaran earned 50 total points
ID: 24377481
SELECT
      CASE
            WHEN strMerchItemName LIKE 'group%' THEN GroupItem
            WHEN strMerchItemName LIKE 'trade%' THEN TradeItem
            ELSE EverythingElse
            END AS ItemType,
      SUM(curPrice) AS Total
FROM tblMerchItem
GROUP BY strMerchItemName
0
 
LVL 39

Assisted Solution

by:BrandonGalderisi
BrandonGalderisi earned 50 total points
ID: 24377541
All evaluations in a case, that are not = evaluations, must be done like this:


SELECT
      CASE
            WHEN strMerchItemName LIKE 'group%' THEN GroupItem
            WHEN strMerchItemName LIKE 'trade%' THEN TradeItem
            ELSE EverythingElse
            END AS ItemType,
      SUM(curPrice) AS Total
FROM tblMerchItem
GROUP BY strMerchItemName


Same would apply for <, >, <=, >=, etc...


SELECT
      CASE
            WHEN strMerchItemName > 1 THEN GroupItem
            WHEN strMerchItemName < 1 THEN TradeItem
..
..
..
..
            ELSE EverythingElse
            END AS ItemType,
      SUM(curPrice) AS Total
FROM tblMerchItem
GROUP BY strMerchItemName
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 24377542
sorry for the double post aneeshattingal
0
 

Author Closing Comment

by:sevenpeaks
ID: 31581113
Beautiful.  Thanks for your work.  That will help me out.
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now