CODEWITHSUNDEEP
sqloracle-database
Marek Lewandowski
Marek Lewandowski

Reputation: 109

How to deal with 1 level deep nesting limit in Oracle?

I have a query, which should return records from table1, where number of business day between two dates is greater than 14:

select * 
from table1
where (select count(*)
       from (select rownum n
            from table2
            where rownum <= sysdate - table1.date_from + 1)
       where to_char(table1.date_from + n - 1, 'D') <> 6 
        and to_char(table1.date_from + n - 1, 'D') <> 7
        and not exists (select 1 
                        from holidays
                        where table1.date_from + n - 1 between holiday_from and holiday_to)) > 14;

First date is selected from table1 (table1.date_from) and second date is sysdate. I need to check all days between these days and exclude saturdays, sundays and holidays (from table holidays). The problem is in part:

(select rownum n
 from table2
 where rownum <= sysdate - table1.date_from + 1)

because i can't get there table1.date_from (1 level deep limit). I tried using CONNECT BY:

   select * 
    from table1
    where (select count(*)
           from dual
           where to_char(table1.date_from + LEVEL - 1, 'D') <> 6 
            and to_char(table1.date_from + LEVEL - 1, 'D') <> 7
            and not exists (select 1 
                            from holidays
                            where table1.date_from + LEVEL - 1 between holiday_from and holiday_to)
            CONNECT BY LEVEL <= sysdate - table1.date_from + 1) > 14;

but i can't use LEVEL here:

and not exists (select 1 from holidays
where table1.date_from + LEVEL - 1 between holiday_from and holiday_to)

Moreover, I can't use function in filter, because of performance issues. So, what is the best solution to solve this problem?

UPDATE:

@imbalind: I really like your approach with starting and descending from SYSDATE and I used it in my solution.

@Lalit Kumar B: Using WITH clause solved the problem with 1 level deep limit.

I combined hints from your answers and this is my new query (working):

select * 
from table1
where (with counter as (select rownum n
                        from table2
                        where rownum <= 40)
       select count(*)
       from counter
       where sysdate - n >= table1.date_from
        and to_char(sysdate - n, 'D') <> DECODE('N', 'T', '-1', '6')
        and to_char(sysdate - n, 'D') <> DECODE('N', 'T', '-1', '7')
        and not exists (select 1 
                        from holidays
                        where sysdate - n between holiday_from and holiday_to)) > 14;

Thank you very much, I really appreciate your help.

Upvotes: 2

Views: 269

Answers (2)

Lalit Kumar B
Lalit Kumar B

Reputation: 49092

because i can't get there table1.date_from (1 level deep limit). I tried using CONNECT BY:

You could use subquery factoring, i.e. WITH clause to avoid the SQL restriction on subquery.

Try this query,

SELECT *
FROM   table1
WHERE (WITH required_dates(d)
            AS (SELECT date_from + LEVEL - 1
                FROM   table1
                CONNECT BY LEVEL < = ( SYSDATE - date_from ) + 1)
       SELECT Count(*)
        FROM   required_dates
        WHERE  To_char(d, 'DY', 'NLS_DATE_LANGUAGE=ENGLISH') NOT IN ( 'SAT',
               'SUN' )
               AND NOT EXISTS (SELECT 1
                               FROM   holidays
                               WHERE  table1.date_from + n - 1 BETWEEN
                                      holiday_from AND holiday_to)) > 14

Upvotes: 1

imbalind
imbalind

Reputation: 1182

My approach would be to generate a fixed number of dates starting from sysdate -1 descending and then work on them. In the following snippet I chose 100. Once you got the right date, just use it to filter on table1.

select dates 
  from (
  select rownum as rn, 
         dates 
    from (
    select x.dates, 
           nvl2(h.holiday_from,'T','F') as hd, 
           decode (to_char(x.dates,'D')-1,6,'T',7,'T','F') as WE 
      from (
      select trunc(sysdate) - rownum as dates
        from dual d
     connect By rownum <= 100 -- change this number if you plan on having long holidays
           ) x 
    left outer join holidays h
      on x.dates between h.holiday_fromand h.holiday_to
         )
   where hd = 'F' and WE = 'F' -- exclude holidays and weekends
       )
 where rn = 14; -- return the 14th working day from now

Upvotes: 2

Related Questions