I am facing a problem in executing queries with CASE statement. Based on my condition,(for eg. length), I want to execute different SQL statement.
Problematic sample query is as follows:
select case
when char_length('19480821') = 8
then select count(1) from Patient
when char_length('19480821')=10
then select count(1) from Doctor
end
Exception:
[Error] Script lines: 1-5 --------------------------
Incorrect syntax near the keyword 'select'.
Msg: 156, Level: 15, State: 2
Server: sunsrv4z7, Line: 2
I am not able to correct the syntax. I am getting the string for char_length as input from the user. How can I fire queries based on certain condition? Is CASE the right choice ? Or do I have to use any other thing.
The case statement in SQL returns a value on a specified condition. We can use a Case statement in select queries along with Where, Order By, and Group By clause. It can be used in the Insert statement as well.
Multiple conditions in CASE statementYou can evaluate multiple conditions in the CASE statement.
Just put opening and closing bracket around select statement resolve you problem
select
case when
char_length('19480821')=8 then
(select count(1) from Patient )
when
char_length('19480821')=10 then
(select count(1) from Doctor )
end
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With