How do I execute different SELECT statements based

问题:

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.

回答1:

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

回答2:

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

The problem is that you are missing opening and closing brackets in your nested 'Select' statements :)

回答3:

Please do note that it is not a case STATEMENT, it is a case EXPRESSION. By enclosing the queries in parentheses, you are converting them (syntactically) to values.

This is similar in principle to a subquery, such as " select name from Doctor where salary = (select max(salary) from Doctor)"

回答4:

select 
  case when 
      LEN('1948082100')=8 then 
          (select 'HELLO' )
      when 
      LEN('194808210')=10 then 
          (select 'GOODBYE')
  end

Change the values to test results.