Skip to Content
0
Sep 10, 2020 at 12:09 PM

In Select DAYS_BETWEEN

60 Views

Hi Experts.

My requirement is to update the value of days field to 1 if it is 0.

example : here when day 1 is June 30 and day 2 is june 29 then days_between gives -1 and in the below first select logic I will get DAYS as 0. And in the second select I am using division so here I get division by zero error. So I when ever DAYS is 0 I Want to make it as 1 and use in second select. In third select I am using NDIV0 to avoid error division by zero but I dont know how to make value of days as 1 if is 0. Please help what change I need to make.

My code is

--First select

SELECT

DAYS_BETWEEN(DAY1, DAY2) + 1 AS DAYS

from ztable;

-- Second Select

SELECT

CASE

WHEN Indicator = '01' THEN ZAMOUNT

WHEN Indicator = '02' THEN ZAMOUNT/2

WHEN Indicator = '03' THEN ( ZAMOUNT) / days )

end as ZZAmt

FROM YTABLE;

--Thrid Select

SELECT

CASE

WHEN Indicator = '01' THEN ZAMOUNT

WHEN Indicator = '02' THEN ZAMOUNT/2

WHEN Indicator = '03' THEN NDIV0 ( ZAMOUNT, days )

end as ZZAmt

FROM YTABLE;