Re: How To Avoid Divide by Zero?

Sean <sharasin_at_mbakercorp.com>ÀÌ(°¡) ¾Æ·¡ ¸Þ½ÃÁö¸¦ news:x%934.58$LLj.393378_at_dca1-nnrp1.news.digex.net¿¡ °Ô½ÃÇÏ¿´½À´Ï´Ù.
> I would appriciate any help with this query. The sixth line where I am
> doing the division has the potential to cause a divide by zero error.
> Is there any way that I can return a null value if I hit the div by 0?
>
> Thanks in advance.
>
> SELECT
> READING_DATE AS READ_DATE,
> MIN(reading_time) AS READ_TIME,
> SUM(DECODE(INSTRUMENT_NAME, '01-1', HEADWATER)) AS "01-1_HEADWATER",
> SUM(DECODE(INSTRUMENT_NAME, '01-1', TAILWATER)) AS "01-1_TAILWATER",
> SUM(DECODE(INSTRUMENT_NAME, '01-1', ((WATER_SURFACE - TAILWATER) /
> ((HEADWATER - TAILWATER) / 100)))) AS "01-1_DELTA_HEAD",
> SUM(DECODE(INSTRUMENT_NAME, '01-1', WATER_SURFACE)) AS
"01-1_WATERSURFACE",
> LOC_CODE
> FROM
> P_READINGS R1
> WHERE
> (READING_DATE <= TO_DATE('12/31/1973', 'MM/DD/YYYY')) AND
> (READING_DATE >= TO_DATE('01/01/1973', 'MM/DD/YYYY')) AND
> (UPPER(LOC_CODE) = 'CAP') AND
> (INSTRUMENT_NAME IN ('01-1')) AND
> READING_TIME = (SELECT MIN(R2.READING_TIME)
> FROM
> P_READINGS R2
> WHERE
> R2.LOC_CODE = R1.LOC_CODE AND
> R2.INSTRUMENT_NAME = R1.INSTRUMENT_NAME AND
> TRUNC(R2.READING_DATE, 'DD') = TRUNC(R1.READING_DATE, 'DD'))
> GROUP BY
> READING_DATE,
> LOC_CODE
> ORDER BY
> READING_DATE
>
>

Change
(HEADWATER - TAILWATER) / 100
to
(HEADWATER - DECODE(TAILWATER,HEADWATER,NULL,TAILWATER,TAILWATER))/100 Received on Tue Dec 07 1999 - 11:24:12 CST