DECODE Statement 1

[CrystalVis]

I have to perform a division on two field to find the average. example: tbl.contact / tbl.count
How do I check to ensure that when tbl.count is 0 then don't perform the calculation? I've tried the following but it doesn't work:

DECODE(tbl.count, tbl.count > 0, tbl.contact / tbl.count,0 )
the decode statement doesn't like the > expression. I guess I cannot use this type of expression with the decode function.

In SQL Server, I can use case statement to check the value in tbl.count. Example:

average = CASE
WHEN tbl.count > 0
THEN round((tbl.contact / tbl.count),2)
ELSE 0
END

Can I do something thing similar to this in Oracle? Your help/suggestion is greatly appreciated.

 

[sem]

You may use about the same in Oracle:

select CASE
WHEN tbl.count > 0
THEN round((tbl.contact / tbl.count),2)
ELSE 0
END CASE
...

Or with DECODE:

select DECODE(tbl.count,0,0,round((tbl.contact / tbl.count),2)) ...

Decode performs only EXACT (=) matching, so in some cases you should use more complex expressions, e.g check sign(tbl.count) for being 1 instead of tbl.count for being positive.



Regards, Dima

 

[CrystalVis]

Dima,
Thank you very much for your help. This is exactly what I need.