Hi,
I have a problem with a hierarchial query and hope someone can figure it out.
I have been given a proprietary table of bank days and want to turn it into something
more useful.
This is the table along with some test data (Ten days only, actual dayvector has 365/366 characters)
SQL> create table bankcalendar(year number(4) not null primary key
,dayvector varchar2(366) not null)
Table created.
SQL> insert into bankcalendar values (2008, '-+++--++++')
1 row created.
SQL> insert into bankcalendar values (2009, '-+--+++++-')
1 row created.
SQL> commit
Commit complete.
I'm trying to convert this into one row for each bank day. Something like:
SQL> select year anno
,to_date(year || '01', 'yyyymm') + level - 1 calendar_date
,case substr(dayvector, level, 1)
when '-' then 0
when '+' then 1
end
is_bankday
from (select *
from bankcalendar
where year = 2009)
connect by level <= length(dayvector)
ANNO CALENDAR IS_BANKDAY
---------- -------- ----------
2009 09-01-01 0
2009 09-01-02 1
2009 09-01-03 0
2009 09-01-04 0
2009 09-01-05 1
2009 09-01-06 1
2009 09-01-07 1
2009 09-01-08 1
2009 09-01-09 1
2009 09-01-10 0
10 rows selected.
My problem is, how do I get rid of that 2009 in the query, so I can have all bank days at once.
Regards
Peter