Skip to Main Content
Forums

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Hierarchial query

Peter GjelstrupNov 3 2009 — edited Nov 9 2009
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
This post has been answered by Karthick2003 on Nov 3 2009
Jump to Answer

Comments

Centinul
Peter Gjelstrup wrote:
My problem is, how do I get rid of that 2009 in the query, so I can have all bank days at once.
Hi Peter.

Can you clarify this statement please? Do you mean you want to remove the YEAR column and possibly CALENDAR_DATE column from the query?
Peter Gjelstrup
Hi Centinul,

No, I mean something in the lines of:
    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 bankcalendar
connect by level <= length(dayvector);
Only, this query will give 2046 rows instead of 20.

Regards
Peter
Frank Kulash
Hi, Peter,

Sorry, I don't understand the question.
What are the results you want from that sample data?

Are you wondering how to use 
SELECT  LEVEL  AS n
FROM    table_x
CONNECT BY  LEVEL <= y
where table_x has more than 1 row?

It's simpler not to use a table with more than one row.
Use a one-row table to generate all the numbers you might need, and then join to that result set.
In this case, you know the maximum you'll need is 366, but if you didn't: 
WITH  cntr  AS
(
    SELECT  LEVEL  AS n
    FROM    dual
    CONNECT BY  LEVEL <= ( SELECT  MAX (LENGTH (dayvector)
                            FROM    bankcalendar
                          )
)
SELECT  SUBSTR (dayvector, c.n, 1)
...
FROM    bankcalendar  b
JOIN    cntr          c   ON  c.n <= LENGTH (dayvecotr)
Edited by: Frank Kulash on Nov 3, 2009 8:49 AM
1 person found this helpful
Karthick2003
Answer
Like this??
with bankcalender
as
(
select 2008 year, '-+++--++++' dayvector from dual
union all
select 2009, '-+--+++++-' from dual
),
tmp 
as
(
select level no from dual connect by level <= 10
)
select to_date('01-01-'||to_char(year),'dd-mm-yyyy')+ (no-1) dt, decode(substr(dayvector,no,1),'-',0,'+',1) bankday
  from bankcalender
  cross join tmp
order by 1
or set your day vector to max 366 in the tmp
with bankcalender
as
(
select 2008 year, '-+++--++++' dayvector from dual
union all
select 2009, '-+--+++++-' from dual
),
tmp 
as
(
select level no from dual connect by level <= 366
)
select to_date('01-01-'||to_char(year),'dd-mm-yyyy')+ (no-1) dt, decode(substr(dayvector,no,1),'-',0,'+',1) bankday
  from bankcalender
  cross join tmp
where substr(dayvector,no,1) is not null
order by 1
Edited by: Karthick_Arp on Nov 3, 2009 5:52 AM
Marked as Answer by Peter Gjelstrup · Sep 27 2020
Peter Gjelstrup
Hi, sorry for not being clear.

I want this output:
      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
      2008 08-01-01          0
      2008 08-01-02          1
      2008 08-01-03          1
      2008 08-01-04          1
      2008 08-01-05          0
      2008 08-01-06          0
      2008 08-01-07          1
      2008 08-01-08          1
      2008 08-01-09          1
      2008 08-01-10          1

20 rows selected.
For which I cheated, and just did this: 
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)
union all
    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 = 2008)
connect by level <= length(dayvector);
Regards
Peter
Centinul
How about this?
SQL> WITH dense_year AS
  2  (
  3          SELECT  ROWNUM - 1 RN
  4          FROM    DUAL
  5          CONNECT BY ROWNUM <= (SELECT MAX(LENGTH(DAYVECTOR)) FROM BANKCALENDAR) + 1
  6  ),   vector_lengths AS
  7  (
  8          SELECT  YEAR
  9          ,       LENGTH(DAYVECTOR) AS VEC_LEN
 10          FROM    BANKCALENDAR
 11  )
 12  SELECT  BANKCALENDAR.YEAR
 13  ,       TRUNC(TO_DATE(BANKCALENDAR.YEAR,'YYYY'),'YEAR') + RN AS CALENDAR_DATE
 14  ,       (CASE SUBSTR(DAYVECTOR, RN, 1)
 15                  WHEN '-' THEN 0
 16                  WHEN '+' THEN 1
 17          END) AS IS_BANKDAY
 18  FROM    BANKCALENDAR
 19  JOIN    VECTOR_LENGTHS  ON VECTOR_LENGTHS.YEAR = BANKCALENDAR.YEAR
 20  JOIN    DENSE_YEAR      ON DENSE_YEAR.RN <= VECTOR_LENGTHS.VEC_LEN - 1
 21  ORDER BY 1,2
 22  /

      YEAR CALENDAR_DATE       IS_BANKDAY
---------- ------------------- ----------
      2008 01/01/2008 00:00:00          0
      2008 01/02/2008 00:00:00          0
      2008 01/03/2008 00:00:00          1
      2008 01/04/2008 00:00:00          1
      2008 01/05/2008 00:00:00          1
      2008 01/06/2008 00:00:00          0
      2008 01/07/2008 00:00:00          0
      2008 01/08/2008 00:00:00          1
      2008 01/09/2008 00:00:00          1
      2008 01/10/2008 00:00:00          1
      2009 01/01/2009 00:00:00          0
      2009 01/02/2009 00:00:00          0
      2009 01/03/2009 00:00:00          1
      2009 01/04/2009 00:00:00          0
      2009 01/05/2009 00:00:00          0
      2009 01/06/2009 00:00:00          1
      2009 01/07/2009 00:00:00          1
      2009 01/08/2009 00:00:00          1
      2009 01/09/2009 00:00:00          1
      2009 01/10/2009 00:00:00          1
Edited by: Centinul on Nov 3, 2009 9:46 AM

Tweaked solution a bit, had extra row.
1 person found this helpful
Peter Gjelstrup
Very promising , Karthick

Especially after changing into 366 :-)

Thanks
Peter
Peter Gjelstrup
Thanks guys, you are the best
Karthick2003
Been there myself ;) !!
Centinul
What about the issue of 365 vs 366? Where some years have 365 days and others 366.

If I modify your example slightly and have one with 9 bank days and the other with 10 we see this result:
SQL> with bankcalender
  2  as
  3  (
  4  select 2008 year, '-+++--+++' dayvector from dual
  5  union all
  6  select 2009, '-+--+++++-' from dual
  7  ),
  8  tmp
  9  as
 10  (
 11  select level no from dual connect by level <= 10
 12  )
 13  select to_date('01-01-'||to_char(year),'dd-mm-yyyy')+ (no-1) dt, decode(substr(dayvector,no,1),'-',0,'+',1) bankday
 14    from bankcalender
 15    cross join tmp
 16  order by 1
 17  /

DT                     BANKDAY
------------------- ----------
01/01/2008 00:00:00          0
01/02/2008 00:00:00          1
01/03/2008 00:00:00          1
01/04/2008 00:00:00          1
01/05/2008 00:00:00          0
01/06/2008 00:00:00          0
01/07/2008 00:00:00          1
01/08/2008 00:00:00          1
01/09/2008 00:00:00          1
01/10/2008 00:00:00
01/01/2009 00:00:00          0
01/02/2009 00:00:00          1
01/03/2009 00:00:00          0
01/04/2009 00:00:00          0
01/05/2009 00:00:00          1
01/06/2009 00:00:00          1
01/07/2009 00:00:00          1
01/08/2009 00:00:00          1
01/09/2009 00:00:00          1
01/10/2009 00:00:00          0
There is an extra entry for 1/10/2008 because of the cross join to "tmp." So any year that isn't a leap year may return wrong results.

I believe the solution presented by Frank (which is more concise) and I can handle that scenario.
SQL> with BANKCALENDAR
  2  as
  3  (
  4  select 2008 year, '-+++--+++' dayvector from dual
  5  union all
  6  select 2009, '-+--+++++-' from dual
  7  ),dense_year AS
  8  (
  9          SELECT  ROWNUM - 1 RN
 10          FROM    DUAL
 11          CONNECT BY ROWNUM <= (SELECT MAX(LENGTH(DAYVECTOR)) FROM BANKCALENDAR) + 1
 12  ),   vector_lengths AS
 13  (
 14          SELECT  YEAR
 15          ,       LENGTH(DAYVECTOR) AS VEC_LEN
 16          FROM    BANKCALENDAR
 17  )
 18  SELECT  BANKCALENDAR.YEAR
 19  ,       TRUNC(TO_DATE(BANKCALENDAR.YEAR,'YYYY'),'YEAR') + RN AS CALENDAR_DATE
 20  ,       (CASE SUBSTR(DAYVECTOR, RN, 1)
 21                  WHEN '-' THEN 0
 22                  WHEN '+' THEN 1
 23          END) AS IS_BANKDAY
 24  FROM    BANKCALENDAR
 25  JOIN    VECTOR_LENGTHS  ON VECTOR_LENGTHS.YEAR = BANKCALENDAR.YEAR
 26  JOIN    DENSE_YEAR      ON DENSE_YEAR.RN <= VECTOR_LENGTHS.VEC_LEN - 1
 27  ORDER BY 1,2
 28  /

      YEAR CALENDAR_DATE       IS_BANKDAY
---------- ------------------- ----------
      2008 01/01/2008 00:00:00          0
      2008 01/02/2008 00:00:00          0
      2008 01/03/2008 00:00:00          1
      2008 01/04/2008 00:00:00          1
      2008 01/05/2008 00:00:00          1
      2008 01/06/2008 00:00:00          0
      2008 01/07/2008 00:00:00          0
      2008 01/08/2008 00:00:00          1
      2008 01/09/2008 00:00:00          1
      2009 01/01/2009 00:00:00          0
      2009 01/02/2009 00:00:00          0
      2009 01/03/2009 00:00:00          1
      2009 01/04/2009 00:00:00          0
      2009 01/05/2009 00:00:00          0
      2009 01/06/2009 00:00:00          1
      2009 01/07/2009 00:00:00          1
      2009 01/08/2009 00:00:00          1
      2009 01/09/2009 00:00:00          1
      2009 01/10/2009 00:00:00          1
Thanks! :)
Karthick2003
Check out my second query.
Centinul
Karthick_Arp wrote:
Check out my second query.
Thanks for pointing that out, missed that part. I don't mean to criticize I was just trying to understand :)
Karthick2003
Thanks for pointing that out, missed that part. I don't mean to criticize I was just trying to understand :)
Please feel free to criticize. It sharpens our knowledge :)
Aketi Jyuuzou
I recommend using recursive with clause (since Oracle11gR2) or model clause (since Oracle10g) :-)
with BANKCALENDAR as(
select 2008 year, '-+++--+++' dayvector from dual union all
select 2009     , '-+--+++++-' from dual)
select year,
trunc(to_date(to_char(year),'yyyy'),'yyyy')+Level-1 as CALENDAR_DATE,
case substr(dayvector,Level-1,1)
when '-' then 0
when '+' then 1 end as IS_BANKDAY
  from BANKCALENDAR
connect by prior year = year
       and level <= Length(dayvector);

YEAR  CALENDAR  IS_BANKDAY
----  --------  ----------
2008  08-01-01           0
2008  08-01-02           0
2008  08-01-03           1
2008  08-01-04           1
2008  08-01-05           1
2008  08-01-06           0
2008  08-01-07           0
2008  08-01-08           1
2008  08-01-09           1
2009  09-01-01           0
2009  09-01-02           0
2009  09-01-03           1
2009  09-01-04           0
2009  09-01-05           0
2009  09-01-06           1
2009  09-01-07           1
2009  09-01-08           1
2009  09-01-09           1
2009  09-01-10           1
1 person found this helpful
Peter Gjelstrup
Thanks Aketi,

Which reminds me that I forgot to mention my version (Shame on me)
with bankcalendar as (select 2008 year, '-+++--+++' dayvector from dual
                      union all
                      select 2009, '-+--+++++-' from dual)
    select year
          ,trunc(to_date(to_char(year), 'yyyy'), 'yyyy') + level - 1
              as calendar_date
          ,case substr(dayvector, level - 1, 1)
              when '-' then 0
              when '+' then 1
           end
              as is_bankday
      from bankcalendar
connect by prior year = year
       and level <= length(dayvector)
                                                                   *
Error at line 1
ORA-01436: CONNECT BY loop in user data
Regards
Peter
Aketi Jyuuzou
OOPS
I forgot that we have to use prior sys_guid() is not null or
prior dbms_random.random() is not null
with bankcalendar as 
(select 2008 year, '-+++--+++' dayvector from dual union all
 select 2009, '-+--+++++-' from dual)
select year,
trunc(to_date(to_char(year), 'yyyy'), 'yyyy') + level - 1 as calendar_date,
case substr(dayvector, level - 1, 1)
when '-' then 0
when '+' then 1 end as is_bankday
  from bankcalendar
connect by prior year = year
       and level <= length(dayvector)
--       and prior sys_guid() is not null
       and prior dbms_random.random() is not null
Aketi Jyuuzou
There is model clause solution B-) 
with bankcalendar as
(select 2008 year, '-+++--+++' dayvector from dual
union all
select 2009, '-+--+++++-' from dual)
select year,calendar_date,is_bankday
  from bankcalendar
 model
partition by (year)
dimension by (1 as soeji)
measures(trunc(to_date(to_char(year), 'yyyy'), 'yyyy') as calendar_date,
dayvector,0 as is_bankday)
rules ITERATE (999) UNTIL (ITERATION_NUMBER+1 = length(dayvector[1]))
(
calendar_date[ITERATION_NUMBER+1] = calendar_date[1]+ITERATION_NUMBER,
is_bankday[ITERATION_NUMBER+1] = case substr(dayvector[1],ITERATION_NUMBER,1)
                                    when '-' then 0
                                    when '+' then 1 end)
order by year,calendar_date;
Aketi Jyuuzou
Oracle11gR2 allows below recursive with clause

hahaha I used PostgreSQL8.4 :8}
with recursive bankcalendar(year,dayvector) as(
select 2008,'-+++--+++' union all
select 2009,'-+--+++++-'),
W(year,dayvector,Level,calendar_date,is_bankday) as(
select year,dayvector,1,to_timestamp(year::Text,'yyyy'),0
  from bankcalendar
union all
select W.year,W.dayvector,W.Level+1,
W.calendar_date + interVal '1 day',
case substr(W.dayvector,W.Level,1)
when '-' then 0
when '+' then 1 end
  from W,bankcalendar b
 where W.year = b.year
   and W.Level != length(W.dayvector))
select year,calendar_date,is_bankday from W
order by Year,calendar_date;

 year |     calendar_date      | is_bankday
------+------------------------+------------
 2008 | 2008-01-01 00:00:00+09 |          0
 2008 | 2008-01-02 00:00:00+09 |          0
 2008 | 2008-01-03 00:00:00+09 |          1
 2008 | 2008-01-04 00:00:00+09 |          1
 2008 | 2008-01-05 00:00:00+09 |          1
 2008 | 2008-01-06 00:00:00+09 |          0
 2008 | 2008-01-07 00:00:00+09 |          0
 2008 | 2008-01-08 00:00:00+09 |          1
 2008 | 2008-01-09 00:00:00+09 |          1
 2009 | 2009-01-01 00:00:00+09 |          0
 2009 | 2009-01-02 00:00:00+09 |          0
 2009 | 2009-01-03 00:00:00+09 |          1
 2009 | 2009-01-04 00:00:00+09 |          0
 2009 | 2009-01-05 00:00:00+09 |          0
 2009 | 2009-01-06 00:00:00+09 |          1
 2009 | 2009-01-07 00:00:00+09 |          1
 2009 | 2009-01-08 00:00:00+09 |          1
 2009 | 2009-01-09 00:00:00+09 |          1
 2009 | 2009-01-10 00:00:00+09 |          1
666352
Hi Aketi,

You don't need use :*rules ITERATE (999) UNTIL (ITERATION_NUMBER+1 = length(dayvector[1]))* it's like you scan your table least(999 ,length(dayvector) times for each rows in table bankcalendar.
Regards Salim.

My solution 
select year,calendar_date,is_bankday
  from bankcalendar
 model
partition by (year)
dimension by (1 as soeji)
measures(trunc(to_date(to_char(year), 'yyyy'), 'yyyy') as calendar_date,
dayvector,0 as is_bankday,length(dayvector) l)
(calendar_date[for soeji from 1 to l[1] increment 1]=calendar_date[1]+ cv(soeji)-1,
is_bankday[for soeji from 1 to l[1] increment 1] = case substr(dayvector[1],cv(soeji),1)
                                                   when '-' then 0
                                                   when '+' then 1 end)
order by calendar_date
/
SQL> with bankcalendar as
  2  (select 2008 year, '-+++--+++' dayvector from dual
  3  union all
  4  select 2009, '-+--+++++-' from dual)
  5  select year,calendar_date,is_bankday
  6    from bankcalendar
  7   model
  8  partition by (year)
  9  dimension by (1 as soeji)
 10  measures(trunc(to_date(to_char(year), 'yyyy'), 'yyyy') as calendar_date,
 11  dayvector,0 as is_bankday,length(dayvector) l)
 12  (calendar_date[for soeji from 1 to l[1] increment 1]=calendar_date[1]+ cv(soeji)-1,
 13  is_bankday[for soeji from 1 to l[1] increment 1] = case substr(dayvector[1],cv(soeji),1)
 14                                                           when '-' then 0
 15                                                           when '+' then 1 end)
 16* order by calendar_date
SQL> /

      YEAR CALENDAR_ IS_BANKDAY
---------- --------- ----------
      2008 01-JAN-08          0
      2008 02-JAN-08          1
      2008 03-JAN-08          1
      2008 04-JAN-08          1
      2008 05-JAN-08          0
      2008 06-JAN-08          0
      2008 07-JAN-08          1
      2008 08-JAN-08          1
      2008 09-JAN-08          1
      2009 01-JAN-09          0
      2009 02-JAN-09          1
      2009 03-JAN-09          0
      2009 04-JAN-09          0
      2009 05-JAN-09          1
      2009 06-JAN-09          1
      2009 07-JAN-09          1
      2009 08-JAN-09          1
      2009 09-JAN-09          1
      2009 10-JAN-09          0

19 rows selected.

SQL>
Aketi Jyuuzou
Nice for usage.
Thanks for your solution :-)

until of model clause is not readable.
And for of model clause is readable.

Therefore we should use for of model clause B-)
Aketi Jyuuzou
OOPS there is more simple one.
LikeWise I used PostgreSQL8.4 :-)
with recursive bankcalendar(year,dayvector) as(
select 2008,'-+++--+++' union all
select 2009,'-+--+++++-'),
W(year,dayvector,Level,calendar_date,is_bankday) as(
select year,dayvector,1,to_timestamp(year::Text,'yyyy'),0
  from bankcalendar
union all
select W.year,W.dayvector,W.Level+1,
W.calendar_date + interVal '1 day',
case substr(W.dayvector,W.Level,1)
when '-' then 0
when '+' then 1 end
  from W
 where W.Level != length(W.dayvector))
select year,calendar_date,is_bankday from W
order by Year,calendar_date;

 year |     calendar_date      | is_bankday
------+------------------------+------------
 2008 | 2008-01-01 00:00:00+09 |          0
 2008 | 2008-01-02 00:00:00+09 |          0
 2008 | 2008-01-03 00:00:00+09 |          1
 2008 | 2008-01-04 00:00:00+09 |          1
 2008 | 2008-01-05 00:00:00+09 |          1
 2008 | 2008-01-06 00:00:00+09 |          0
 2008 | 2008-01-07 00:00:00+09 |          0
 2008 | 2008-01-08 00:00:00+09 |          1
 2008 | 2008-01-09 00:00:00+09 |          1
 2009 | 2009-01-01 00:00:00+09 |          0
 2009 | 2009-01-02 00:00:00+09 |          0
 2009 | 2009-01-03 00:00:00+09 |          1
 2009 | 2009-01-04 00:00:00+09 |          0
 2009 | 2009-01-05 00:00:00+09 |          0
 2009 | 2009-01-06 00:00:00+09 |          1
 2009 | 2009-01-07 00:00:00+09 |          1
 2009 | 2009-01-08 00:00:00+09 |          1
 2009 | 2009-01-09 00:00:00+09 |          1
 2009 | 2009-01-10 00:00:00+09 |          1
1 - 21
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Dec 7 2009
Added on Nov 3 2009
21 comments
1,795 views