8 Replies Latest reply on Dec 10, 2007 12:24 PM by paross1

    MS SQL 2005: Assigning value to NULL

    romeogq Level 1
      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.
        • 1. MS SQL 2005: Assigning value to NULL
          paross1 Level 2
          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
          • 2. Re: MS SQL 2005: Assigning value to NULL
            romeogq Level 1
            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.

            • 3. Re: MS SQL 2005: Assigning value to NULL
              paross1 Level 2
              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
              • 4. Re: MS SQL 2005: Assigning value to NULL
                romeogq Level 1
                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."
                • 5. Re: MS SQL 2005: Assigning value to NULL
                  romeogq Level 1
                  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.
                  • 6. MS SQL 2005: Assigning value to NULL
                    paross1 Level 2
                    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
                    • 7. Re: MS SQL 2005: Assigning value to NULL
                      romeogq Level 1
                      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.
                      • 8. Re: MS SQL 2005: Assigning value to NULL
                        paross1 Level 2
                        Funny, that was going to be my next suggestion... Glad that you solved it!

                        Phil