CODEWITHSUNDEEP
sqlsql-server
Ori Lev
Ori Lev

Reputation: 31

Displaying an alternative result when derrived table is empty

I have this sql code where I try to display an alternative value as a result whenever the table is empty or the the single column of the top row when it is not

select top 1 case when count(*)!=0 then derrivedTable.primarykey
else 0 end endCase
from
(   
    select top 1 m.primarykey
    from mytable m
    where 0=1
)derrivedTable

The problem is that when I run this, I get the error message "column 'derrivedTable.primarykey' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause." But when I put 'derrivedTable.primarykey' in the group by clause, I just get an empty table. Does anyone hve a solution? thanks in advance

Upvotes: 0

Views: 38

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269693

You can use aggregation:

select coalesce(max(m.primarykey), 0)
from mytable m;

An aggregation query with no group by always returns exactly one row. If the table is empty (or all rows are filtered out), then the aggregation functions -- except for COUNT() -- return NULL -- which can be transformed to a value using COALESCE().

Such a construct makes me worry. If you are using this to set the primary key on an insert, then you should learn about identity columns or sequences. The database will do the work for you.

Upvotes: 1

mkRabbani
mkRabbani

Reputation: 16908

Can you try this below script-

SELECT 
CASE
    WHEN COUNT(*) = 1 THEN derrivedTable.primarykey
    ELSE 0
END endCase
FROM
(
    SELECT TOP 1 m.primarykey
    FROM mytable m
    WHERE 0 = 1
) derrivedTable
derrivedTable.primarykey;

Upvotes: 0

Related Questions