CODEWITHSUNDEEP
oracle-database
Gowtham VJ
Gowtham VJ

Reputation: 97

Alternate for decode function

I have a table 'Holiday' which lists a set of holiday details.If i specify a date,I should obtain a result date after 5 days of specified date.If there is holiday in between it should exclude them and display the non holiday date.I have table named holiday which includes holiday date,holiday type|(weekly off,local holiday).Now i have used nested decode for continuous holiday checking.Tell me how this can be changed in case function.

DECODE
               (date,
                holidaydate, DECODE
                   (date + 1,
                    holidaydate + 1, DECODE
                       (date + 2,
                        holidaydate + 2, DECODE
                           (date + 3,holidaydate+3,date+4,date+3),date+2),date+1),date);

Upvotes: 0

Views: 4477

Answers (3)

Gowtham VJ
Gowtham VJ

Reputation: 97

Finally i found the optimal solution.Thanks for ur response guys. SELECT dt FROM (SELECT dt FROM (SELECT TO_DATE('15-AUG-2013','dd-mon-yyyy')+LEVEL dt FROM DUAL CONNECT BY LEVEL < 30)
WHERE (SELECT COUNT (*) FROM mst_holiday WHERE holidaydate = dt) = 0 ) where rownum=1

Upvotes: 0

Joe
Joe

Reputation: 6827

This can be achieved with a simple subquery which counts the number of holiday dates between a specified date and date+5. The following will return a date that is five non-holiday days in the future:

testdate+(select 5+count(1) 
            from holiday
           where holidaydate between testdate
                                 and testdate + 5)

Simply change both "5"s so another number to change the evaluation period.

SQLFiddle here

Edit - based on comment below, my code doesn't evaluate any days after the fifth day. This would probably be much easier with a function, but the following cte-based code will work also:

with cte as ( (select alldate,holidaydate 
                 from (select to_date('20130101','yyyymmdd')+level alldate 
                         from dual 
                       connect by level < 10000 -- adjust for period to evaluate
                      ) alldates 
                      left join holiday on alldate=holidaydate) )
select 
    testdate,test_plus_five
from ( 
    select 
        alldate test_plus_five,testdate,
        sum(case when holidaydate is null 
                 then 1 
                 else 0 end) over (partition by testdate order by alldate) lastday 
    from 
        cte,
        testdates
    where
        alldate >= testdate
    group by
        alldate,holidaydate,testdate)
where
    lastday = 6

This script builds a calendar table so it can evaluate each day (holiday or non-holiday); then we get a running count of non-holiday days, and use the sixth one.

SQLFiddle here

Upvotes: 4

Ravi
Ravi

Reputation: 31397

AFAIK, You can use CASE alternative to DECODE in Oracle

CASE [ expression ]

   WHEN condition_1 THEN result_1
   WHEN condition_2 THEN result_2
   ...
   WHEN condition_n THEN result_n

   ELSE result

END

Upvotes: 1

Related Questions