Click here to Skip to main content
15,867,308 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I want to use where clause after using WHEN THEN CASE statement into sql query.
When i have below run query output blank result




select Activate,AadhaarNo,gender,emailID,joindate,qua, CASE WHEN classCourse='class' THEN [bitcrj_database].[tbClassMaster].classname
 WHEN classCourse='course' THEN tbCrsDetail.subCrs END as course  from tbStudInfo left join  tbCrsDetail 
 on tbStudInfo.cID=tbCrsDetail.crID left join [bitcrj_database].[tbClassMaster]   on  tbStudInfo.cID=[bitcrj_database].[tbClassMaster].id  
  Where  course=''


What I have tried:

I want to result through where clause after use CASE when Statement.
When i trying output is blank
Posted
Updated 20-Jul-22 2:21am

The WHERE clause is evaluated before the SELECT clause. It cannot refer to columns which are calculated or aliased in the SELECT clause.

The simplest solution is to move the calculated column to a correlated sub-query:
SQL
SELECT
    S.Activate,
    S.AadhaarNo,
    S.gender,
    S.emailID,
    S.joindate,
    S.qua, 
    C.course  
FROM
    tbStudInfo As S
    OUTER APPLY
    (
        SELECT CM.classname
        FROM [bitcrj_database].[tbClassMaster] As CM
        WHERE S.cID = [bitcrj_database].[tbClassMaster].id
        AND S.classCourse = 'class'
        
        UNION
        
        SELECT C.subCrs
        FROM tbCrsDetail As C
        WHERE S.cID = C.crID
        AND S.classCourse = 'course'
    ) As C
WHERE
    (C.course Is Null Or C.course = '')
;
 
Share this answer
 
Comments
Nishant.Chauhan80 21-Jul-22 1:26am    
Thank you so much sir
I'm not a fan of correlated subqueries (The reason: SQL server performance - Death by correlated subqueries - SQL Service[^] ) so an alternative approach is to use Common Table Expressions (WITH common_table_expression (Transact-SQL) - SQL Server | Microsoft Docs[^]). E.g. something like (NB untested)
SQL
;with cte as 
(
	select Activate,AadhaarNo,gender,emailID,joindate,qua, 
	CASE WHEN classCourse='class' THEN CM.classname
		WHEN classCourse='course' THEN CD.subCrs 
		ELSE ''
		END as course  
	from tbStudInfo SI
	left join  tbCrsDetail CD on SI.cID=CD.crID 
	left join [bitcrj_database].[tbClassMaster]  CM on  SI.cID=CM.id 	
)
select Activate,AadhaarNo,gender,emailID,joindate,qua
from cte
Where  course=''
Points to note:
- Both of us (Solutions 1 and 2) have used Table Aliases - it saves a lot of typing and makes the sql easier to read
- I've added an ELSE to your case - because you are using left join it is possible that there are no matches on the joined tables and classCourse would be returned as NULL not ''
- the semi-colon in ;with is not necessary if you terminate the previous statement with ;
 
Share this answer
 
Comments
Nishant.Chauhan80 21-Jul-22 1:26am    
Thank you so much sir

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900