Hi,

I am using the following SQL statement as sub select.

SELECT COUNT(OrderID) AS MyCount FROM Jobs WHERE OrderID=derid  AND OrderlineID > 0 AND status<>6 GROUP BY OrderID

How can I set the return value from count(orderid) to 0 when it is null?

Mike

Is the problem that you get some rows with a null or (as I'm guessing) that you get no rows?

Roy

Hi Roy,

I get some rows with a null. The null value must be set to 0.

Mike


Just looking at it again I think your problem will be solved if you just drop the GROUP BY clause. I'd also use COUNT(*)

SELECT COUNT(*) AS MyCount FROM Jobs WHERE OrderID=derid  AND OrderlineID > 0 AND status<>6

You are picking a single OrderID so the GROUP BY is essentially grouping everything there anyway and isn't needed

Roy Lambert

Thank you!

That seems to do the trick.