Case Statement in Where condition Error

lejo

select emp.* into #TempEmpList1 from (SELECT row_number()over(partition by Emp_ID order by Emp_ID desc) rn,*
   from #TempEmpList) emp where emp.rn<=
   (case when exists (select Emp_noEligibleforSchoolFee from NSP113_EducationAllowanceMaster where Emp_ID= emp.Emp_ID )
   ELSE
   emp.Emp_noEligibleforSchoolFee end )

BOLD PORTION IS ERROR

HOW TO CORRECT THIS WHERE CONDITION

3 Replies

Know the answer? Post it — somebody with the same question will find it here.

Jignesh Kumar3y ago
Accepted answer
Hello,

You are missing then in Case when ?? Then Else ?? End
```
(case when exists  (select Emp_noEligibleforSchoolFee from NSP113_EducationAllowanceMaster where Emp_ID= emp.Emp_ID ) Then "YourValuesForMatchedCondition" 
     ELSE  
    emp.Emp_noEligibleforSchoolFee end  )
```
Refer :

https://stackoverflow.com/questions/18099514/where-case-when-statement-with-exists

Vishal Joshi3y ago

Hello Lejo,

It looks like a case statement syntax issue. there must be Than after When condition.

Please try the below query it should work.

select emp.* into #TempEmpList1 from (SELECT   row_number()over(partition by  Emp_ID order by Emp_ID desc) rn,*from  #TempEmpList) emp 
where emp.rn<=
    (case when exists  (select Emp_noEligibleforSchoolFee from NSP113_EducationAllowanceMaster where Emp_ID= emp.Emp_ID )
	 THEN
		select Emp_noEligibleforSchoolFee from NSP113_EducationAllowanceMaster where Emp_ID= emp.Emp_ID 
     ELSE  
		emp.Emp_noEligibleforSchoolFee 
	END)

Thanks

Aravind Govindaraj3y ago
can you paste the error message pls