Solved

SQL Query using multiple CASE

Posted on 2014-01-03
12
292 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
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 
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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
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.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

809 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