Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

SQL Query using multiple CASE

Posted on 2014-01-03
12
294 Views
Last Modified: 2014-01-03
In  my sql table, there are multiple teams that can rate a record.  I am tasked to build a report to display on a web page that counts ratings that are the same for a record. I am trying to build my query using CASE.  If I use only one WHEN statement, my datagrid populates.  But if I use multiple WHEN I am getting an error:  "Error near WHEN".
   
Table data looks like this:

Record         MyRating       YourRating        HisRating
1                   Good              Best                 Poor
2                   Good              Good                Good
3                    Best               Best                 Best
4                   Good                                      Good
5  

My query works in access db but I am trying to make it work for the sql table.  Here is my query.  Eventually, it will be a union query for multiple tables.

"SELECT RecNum, CASE " +
             " WHEN (MyRating is NULL or MyRating = '') AND (YourRating is NULL or YourRating = '') AND (HisRating is NULL or HisRating = '' )" +
             " THEN 1  ELSE 0 END AS NotViewed " +
             " WHEN (MyRating = 'Good' AND YourRating = 'Good' AND HisRating = 'Good' )" +
             " THEN 1 ELSE 0  END AS Good "+
             " WHEN (MyRating = 'Best' AND YourRating = 'Best' AND HisRating = 'Best' )" +
             " THEN 1 ELSE 0  END AS Best " +
       " FROM tbl_MySurvey " +
                 " WHERE (Status = 'Done') " +
                 " GROUP BY  RecNum, CASE " +
                " WHEN (MyRating is NULL or MyRating = '') AND (YourRating is NULL or YourRating = '') AND (HisRating is NULL or HisRating = '' )" +
             " THEN 1  ELSE 0 END AS NotViewed " +
             " WHEN (MyRating = 'Good' AND YourRating = 'Good' AND HisRating = 'Good' )" +
             " THEN 1 ELSE 0  END AS Good "+
             " WHEN (MyRating = 'Best' AND YourRating = 'Best' AND HisRating = 'Best' )" +
             " THEN 1 ELSE 0  END AS Best ;";
0
Comment
Question by:ptslv
12 Comments
 
LVL 12

Expert Comment

by:Habib Pourfard
ID: 39754065
Here is the Sql equivalent:

SELECT RecNum, CASE 
              WHEN (MyRating is NULL or MyRating = '') AND (YourRating is NULL or YourRating = '') AND (HisRating is NULL or HisRating = '' )
              THEN 1  ELSE 0 END AS NotViewed ,
              CASE WHEN (MyRating = 'Good' AND YourRating = 'Good' AND HisRating = 'Good' )
              THEN 1 ELSE 0  END AS Good ,
              CASE WHEN (MyRating = 'Best' AND YourRating = 'Best' AND HisRating = 'Best' )
              THEN 1 ELSE 0  END AS Best 
        FROM tbl_MySurvey 
                 WHERE (Status = 'Done')
                 GROUP BY  RecNum, CASE 
              WHEN (MyRating is NULL or MyRating = '') AND (YourRating is NULL or YourRating = '') AND (HisRating is NULL or HisRating = '' )
              THEN 1  ELSE 0 END,
              CASE WHEN (MyRating = 'Good' AND YourRating = 'Good' AND HisRating = 'Good' )
              THEN 1 ELSE 0  END,
              CASE WHEN (MyRating = 'Best' AND YourRating = 'Best' AND HisRating = 'Best' )
              THEN 1 ELSE 0  END 

Open in new window

0
 
LVL 65

Expert Comment

by:Jim Horn
ID: 39754075
I have a demo of nested CASE blocks in this article, scroll down about a third of the way.

Looks like you are missing commas after every 'END as column_name'.

btw is it necessary to use dynamic SQL?  Makes it a lot more work handling all of the + 's and quotes.
0
 

Author Comment

by:ptslv
ID: 39754083
Pourfard,  Thans for the quick response.  I adjusted my query to what you have, but now I am getting  this error:

Incorrect syntax near the keyword 'CASE'.
0
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 
LVL 12

Accepted Solution

by:
Habib Pourfard earned 400 total points
ID: 39754097
Are you sure? mine is working! to make it short you can write it as follows too:

;WITH  Survay AS (
SELECT RecNum, 
              CASE WHEN (MyRating is NULL or MyRating = '') AND (YourRating is NULL or YourRating = '') AND (HisRating is NULL or HisRating = '' )
              THEN 1  ELSE 0 END AS NotViewed,
              CASE WHEN (MyRating = 'Good' AND YourRating = 'Good' AND HisRating = 'Good' )
              THEN 1 ELSE 0  END AS Good,
              CASE WHEN (MyRating = 'Best' AND YourRating = 'Best' AND HisRating = 'Best' )
              THEN 1 ELSE 0  END AS Best 
        FROM tbl_MySurvey 
		WHERE (Status = 'Done')
) 
SELECT * FROM survay              
GROUP BY  RecNum, NotViewed, Good, Best

Open in new window


by the way I think may be you need to sum up the result as you are grouping by ratings:
;WITH  Survay AS (
SELECT RecNum, 
              CASE WHEN (MyRating is NULL or MyRating = '') AND (YourRating is NULL or YourRating = '') AND (HisRating is NULL or HisRating = '' )
              THEN 1  ELSE 0 END AS NotViewed,
              CASE WHEN (MyRating = 'Good' AND YourRating = 'Good' AND HisRating = 'Good' )
              THEN 1 ELSE 0  END AS Good,
              CASE WHEN (MyRating = 'Best' AND YourRating = 'Best' AND HisRating = 'Best' )
              THEN 1 ELSE 0  END AS Best 
        FROM tbl_MySurvey 
		WHERE ([Status] = 'Done')
) 
SELECT RecNum, SUM(NotViewed), SUM(Good), SUM(Best) FROM survay              
GROUP BY  RecNum

Open in new window

0
 

Author Comment

by:ptslv
ID: 39754114
I put in the missing commas and it worked.  Eventually, I will sum it all up.  I have to include some other tables, and I will be working with filters since my groups are actually dynamically generated from whatever is populated in the lookup table.
0
 

Author Comment

by:ptslv
ID: 39754116
Is this correct?

;WITH  Survay AS (......

What is in front of the ";"?
0
 
LVL 12

Expert Comment

by:Habib Pourfard
ID: 39754126
If you mean why With is comming with a semicolon:
It is optional, usually included to avoid ambiguity because WITH can be used elsewhere
..FROM..WITH (NOLOCK)..

To read more about With cluase you can refer to MSDN
0
 

Author Comment

by:ptslv
ID: 39754169
OK, I understand.  One last question - how do I add the tablename as an alias?  I am getting an error.  

"SELECT 'Bat Charge' AS TableName, " +  my case statements .....+
" GROUP BY TableName;";

The error says " A field or property with the name 'TableName' was not found on the selected data source."

It is a bound column in my datagrid.
0
 
LVL 12

Expert Comment

by:Habib Pourfard
ID: 39754180
you cannot use an alias in group by clause.

but the simple select query works:
SELECT 'Bat Charge' AS ColumnName
0
 
LVL 50

Assisted Solution

by:Lowfatspread
Lowfatspread earned 100 total points
ID: 39754299
"select tablename,.... from (SELECT 'Bat Charge' AS TableName, " +  my case statements .....+
" ) as x GROUP BY TableName;";

although i'm not clear why you have the semi-colon after tablename in the text...?
0
 

Author Comment

by:ptslv
ID: 39754339
The semicolon is the end of my query in my code.
0
 

Author Closing Comment

by:ptslv
ID: 39754804
Thank you for all the help.  This is the final query with the alias column:

strSQL = "";WITH  Survay AS (
  SELECT RecNum,
              CASE WHEN (MyRating is NULL or MyRating = '') AND (YourRating is NULL or YourRating = '') AND (HisRating is NULL or HisRating = '' )
              THEN 1  ELSE 0 END AS NotViewed,
              CASE WHEN (MyRating = 'Good' AND YourRating = 'Good' AND HisRating = 'Good' )
              THEN 1 ELSE 0  END AS Good,
              CASE WHEN (MyRating = 'Best' AND YourRating = 'Best' AND HisRating = 'Best' )
              THEN 1 ELSE 0  END AS Best,  'Bat Charge' AS TableName
        FROM tbl_MySurvey
            WHERE ([Status] = 'Done')
)
SELECT SUM(NotViewed), SUM(Good), SUM(Best), TableName FROM survay              
GROUP BY  TableName
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
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.

840 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