Trouble in migrating query from DB2 to Oracle

[gunalan]

This is the query in IBM DB2...


select (days(max(end_dt)) - days(min(bgn_dt)) + 1) - sum(days(coalesce(end_dt, Current_timestamp)) - days(bgn_dt) + 1) as OffRent
from rental
where id = 2395

I tried using to_date instead of days.I was not succesful.
end_dt and bgn_dt are of type date.so i have trouble dealing with this.any body can give me query in oracle for this.

 

[SantaMufasa]

Gunalan,

Oracle's date-handling functions and operations are amongst the simplest, yet most robust, in the db industry. So, to get you started, (and presuming that you have defined "END_DT" and "BGN_DT" are Oracle DATE data types) I believe that the following code fragment does what you wanted for the first couple of your expressions:

select (max(end_dt) - (min(bgn_dt) + 1)...)...

But before I can "fill in the blanks" with the rest of you code, could you please explain what the "coalesce" function does in this context in DB2?

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[gunalan]

HI SantaMufasa,

I got the exact definition:

COALESCE returns the first expression that is not NULL. The expressions are evaluated in the order in which they are specified. This function is usually used to replace NULL values with a not NULL value

thanks...

 

[SantaMufasa]

Gunalan,

Thanks for the clarification. If that is the case, then here is the equivalent syntax in the Oracle World:

select (max(end_dt) - min(bgn_dt) + 1) -
       sum (nvl(end_dt,SYSDATE) - bgn_dt + 1) as OffRent
  from rental
 where id = 2395;

Let us know if this produces the effect/results that you wanted.



Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[gunalan]

Hi Santa,

I got the desired result.

Thanks.