Follow

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use
Contact

Sql query with multi return options

I’m trying to build query that return 0 or more and return -1 if Id isn’t exists
For example:

Select Count(*)
From my table
Where id = inputId

I tried to use case like this:

Select
  CASE WHEN Count(*) >= 0 THEN Count(*) 
   ELSE -1 END
From .....
Where....

my issue is that 0 is valid result that mean Id exist with 0 columns,
I need to return -1 if id isn’t exists

MEDevel.com: Open-source for Healthcare and Education

Collecting and validating open-source software for healthcare, education, enterprise, development, medical imaging, medical records, and digital pathology.

Visit Medevel

>Solution :

Use a case expression to:

  1. If there are values in that other column, return the count.
  2. If no value in that other column, return 0 if id still exists.
  3. None of the above exist, return -1.
select case when count(othercolumn) > 0 then count(id)   -- or perhaps "then count(othercolumn)"
            when count(id) > 0 then 0
            else -1
       end
From my table
Where id = inputId
Add a comment

Leave a Reply

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use

Discover more from Dev solutions

Subscribe now to keep reading and get access to the full archive.

Continue reading