CASE or ISNULL with sub query 1

[funart]

Hi there,

I'm trying to do some reporting with subqueries, where some of the columns are operations between two subqueries.

The problem lies in that there is a possibilty that one of the denominators could be zero. I have tried checking for this with a CASE or ISNULL, but I still get a divide by zero error.

This is an example of what I'm trying to do. Any help would be appreciated, either if I have a mistake, or a better way:

ISNULL:
SELECT *, (SELECT COUNT(*) FROM [table1]) /
ISNULL((SELECT COUNT(*) FROM [table2]),1)
FROM [table3]

CASE:
SELECT *, CASE
WHEN (SELECT COUNT(*) FROM [table2]) IS NULL
THEN 0
ELSE (SELECT COUNT(*) FROM [table1]) /
(SELECT COUNT(*) FROM [table2])
FROM [table3]

That's the basic gist of it.

Thank you for your future help!

 

[zhavic]

You must test the value for the both 'NULL' and 'zero'

SELECT *, CASE
WHEN ISNULL(SELECT COUNT(*) FROM [table2],0) = 0
THEN 0
ELSE (SELECT COUNT(*) FROM [table1]) /
(SELECT COUNT(*) FROM [table2])
FROM [table3]


Zhavic

---------------------------------------------------------------
In the 1960s you needed the power of two Comodore64s to get a rocket to the moon. Now you need a machine which is a vast number of times more powerful just to run the most popular GUI.

 

[swampBoogie]

The result of a count is never null.

SELECT *, (SELECT COUNT(*) FROM [table1]) /
           case when not exists (SELECT * FROM [table2])
then 1 else (SELECT COUNT(*) FROM [table2]) end
FROM [table3]

 

[zhavic]

Good note 'swampBoogie'.

To 'exists' statement: I have experienced with it some time ago, and I thing it is slower than other ways to determine if some rows exists, but I may be wrong.


Zhavic



---------------------------------------------------------------
In the 1960s you needed the power of two Comodore64s to get a rocket to the moon. Now you need a machine which is a vast number of times more powerful just to run the most popular GUI.

 

[funart]

I would like to thank you both very much for your help. After trying swampBoogie's method, and it didn't work, b/c some of the counts did in fact result in <NULL>, which most likekly is, there might not have been any records for the given criteria, which would result in a <NULL>, or at least in my experience it has), I then went on to Zhavic, which I must thank you again, worked like a charm.

Thank you again!