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
2条答案
按热度按时间fkaflof61#
Use a
case
expression to:xzabzqsa2#
I'm reading through several lines here, but at a pure guess what you are actually want is something like:
"I want a
COUNT
of the number of rows for a specific ID where another column has a specific value. If there are no rows for the ID I want to return-1
, however, if there are no rows where the other column has a specific value, bit there are rows for the ID, then0
should be returned."If this is the correct interpretation, then you could use a subquery and then within the subquery
GROUP BY
the ID, even though it will still be a single distinct group; this means that if the ID doesn't exist no rows are returned. To ensure that rows are returned regardless of the value of the other column, you'll need to use conditional aggregation. This results in something like this:The
ISNULL
then means that when no rows are returned, which would result inNULL
,-1
is then returned.