In Select DAYS_BETWEEN

[former_member543694]

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;

[former_member358098]

how about changing first select?

SELECT 
case DAYS_BETWEEN(DAY1, DAY2) + 1
when 0 then 1
else DAYS_BETWEEN(DAY1, DAY2) + 1
end DAYS
from ztable;

[lbreddemann]

You're not wrong and I saw that.

I figured that since the subsequent calculation was "AMOUNT" divided by "DURATION" that the "direction" of the duration didn't matter here.

There doesn't come a scenario to my mind, right now, where for such a calculation the sign of the duration plays a role ("early payment/sconto" or "overdue fees" are usually calculated separately and commonly against a step-function).

But hey, I don't actually know the scenario, and as I wrote, you are not wrong.

An alternative approach could be to keep the sign:

SELECT
     MAP (DAYS_BETWEEN(DAY1, DAY2)
          , 0, 1
         , DAYS_BETWEEN(DAY1, DAY2)) AS NONZERO_DAYS
FROM
     ztable;

Which is, of course, the same as your solution, just a little bit more compact.

[lbreddemann]

Ok, so you want to make sure that there is never a division by zero, and from what I understand you want to achieve this but treating any occurrence of zero from the first query as if were a 1.

That's relatively easy to do and there is no CASE expression required:

SELECT
     GREATEST (1, DAYS_BETWEEN(DAY1, DAY2)) AS NONZERO_DAYS
FROM
     ztable;

By using GREATEST and 1 as the comparison value you ensure that no value smaller than 1 is returned, which solves the problem as I understood it.

[former_member358098]

I'm not sure, because in the example there were negative numbers, which going to get lost as they're not greater than 1

[former_member358098]

You're totally good in code golfing, I admit it. Both answers are smooth.

Edit: just noticed the're both missing +1, in case someone gonna blindly copy it 🙂

[former_member543694]

Thanks a ton to both of you for the quick solutions. Could you please tell me is there a way to implement the logic in 2nd and 3rd select instead of 1st select.

Apart from 0 all other values to be allowed i.e both positive and negative . This statement

GREATEST (1, DAYS_BETWEEN(DAY1, DAY2))AS NONZERO_DAYS

will not help me because as you explained it gives only positive values starting from 1.

[lbreddemann]

Not sure if you’ve seen my second answer that addressed the potential for negative results.

Since you’re asking for this specific behavior, tell us: what meaning do you assign to negative numbers in this case?

And how do you ensure the order of DAY1 and DAY2 in the data? As soon as the sign of the difference is relevant the order of those to operands is too.