MS SQL 2005: Assigning value to NULL

[romeogq]

For some reason I have been finding it impossible to assign the value of '0' to the null result of count(). It's been easy to identify the NULL values, but the assignment of zero fails continuously.

[paross1]

Count(whatever) is never going to be NULL, since if there are no matching values then COUNT() will be 0. Try COUNT(ISNULL(r.res_id, 0)).

This is more likely to be done with SUM than COUNT.

Phil

[romeogq]

I'll post the entire code to you can see null is a valid value. Also, finding the null value isn't the issue. Assigning an actual value in its place is the issue. I've even used the ELSE clause to assign 0 to any value not specified and it still doesn't work (shown in example 2).

The first subquery finds the number of package components sold and assigns it's value to the com_sold variable. Components can be part of many packages, but have an absolute inventory.

[paross1]

quote:

---

I'll post the entire code to you can see null is a valid value

---

A valid value for what? COUNT(r.res_id) will never be NULL, and will return 0 if r.res_id itself is NULL. Of cours, why don't you use an OUTER join if you want to count values in your subselect regardless of a correlation?

Phil

[romeogq]

First thanks for the help, continuing...

When changing the subquery to the following, I get this error:
"Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression."

[romeogq]

To add,

If I change the LEFT OUTER to a RIGHT OUTER, I still get an [empty string] instead of '0' when cfdumping.

I know I could easily replace the empty string through cf, but I rather do it through sql if possible.

[paross1]

How about this?

SELECT COUNT( ISNULL(r.res_id, 0)) * pc.paccom_quantity
FROM bat_paccomponents pc INNER JOIN bat_packages pa ON pc.package_id = pa.package_id
RIGHT OUTER JOIN bat_reservations r ON pa.package_id = r.package_id
WHERE pc.com_id = c.com_id
GROUP BY pc.paccom_quantity) AS com_sold

or this?

(SELECT COUNT( ISNULL(r.res_id, 0)) * pc.paccom_quantity
FROM bat_paccomponents pc INNER JOIN bat_packages pa ON pc.package_id = pa.package_id
INNER JOIN bat_reservations r ON pa.package_id = r.package_id
WHERE pc.com_id = c.com_id
GROUP BY pc.paccom_quantity) AS com_sold

Phil

[romeogq · Accepted solution]

GOT IT!!

The subQuery itself is evaluating to NULL, so it just hit me to ISNULL the entire subQuery! Lol, wow. 4 days to figure this out, and its this simple.

Thanks for the help Phil.

[paross1]

Funny, that was going to be my next suggestion... Glad that you solved it!

Phil