> Hi
>
> I need to create a view where the code looks something like
below.
>
> create or replace view myview as
> select col1, col2, (col1/col2)*1000000 col3
> from mytable
>
> However, it is possible that col2 contains some zero values
and when
> there zeroes the view will break. I want col3 (calculated) to
be 0 when
> col2 is 0. How do I change the sql to do just that with using
UNION?
Hi there,
You just have to use the DECODE function, which implement
a sort of case statement; your example would have to be
coded like follows:
create or replace view myview as
select col1, col2, decode(col2,0,0,(col1/col2)*1000000) col3
from mytable
It means: if col2 (first arg) = 0 (second arg), return 0
(third arg), else return (col1/col2)*1000000 (last arg)
Note that you can use decode to compare with a series of
values:
decode(<expr>,<val1>,<result1>,<val2>,<result2>, ...,
<default>)
I hope this helps!
--
This answer is courtesy of QuestionExchange.com
http://www.questionexchange.com/showUsenetGuest.jhtml?ans_id=5325&cus_id=USENET&qtn_id=3993
Received on Thu Oct 14 1999 - 00:00:00 CDT
