Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

SQL Query using multiple CASE

Posted on 2014-01-03
12
Medium Priority
?
302 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 66

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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 12

Accepted Solution

by:
Habib Pourfard earned 1600 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 400 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

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

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

It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Ready to get certified? Check out some courses that help you prepare for third-party exams.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
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.

971 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