Toby Derrum Toby Derrum - 5 months ago 7
SQL Question

Counting row from a query that includes an EXCEPT

I have the following query:

SELECT *
FROM
(SELECT
SREF, DELIVERYDATE, DATEIDENTIFIED, DCID, SUPN, PONUMBER,
FROM
BNHEAD
WHERE
(SUPP = 'Sanchez') AND (STAT = '2')

EXCEPT

SELECT
SREF, DELIVERYDATE, DATEIDENTIFIED, DCID, SUPN, PONUMBER,
FROM
BNHEAD
WHERE
(SUPP = 'sanchez')
AND (STAT = '2')
AND ((STI = 'Yes') AND (RESN = 'third party'))
OR (FORWARDEDTO = 'STI')) t1
ORDER BY
DeliveryDate DESC


How do I get the row count only from this query and if it is zero return 0 ? I think it might need to involve coalesce but I'm unsure how.

Answer

You just use select count(*) in the outer query:

select count(*)
from (select sref . . .
      . . .
     ) t

select count(*) always returns one row (when there is no group by). It will return 0 if there are no rows in the subquery.