Skip to Main Content

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

155651
You can make use of user defined objects in Oracle 8 onwards. You can create either table of objects or varray of objects.

Mohan
395113
I am giving you an example of using array of Record


DECLARE
-- declaration of Record
TYPE REC1 IS RECORD (COLUMN1 VARCHAR2(10),
COLUMN2 DATE) ;
-- Array of Record
TYPE TAB1 IS TABLE OF REC1 INDEX BY BINARY_INTEGER ;
TAB2 TAB1 ;
-- New Procedure
PROCEDURE P1 (TAB3 TAB1) IS
BEGIN
FOR I IN 1..10 LOOP
DBMS_OUTPUT.PUT_LINE(TAB3(I).COLUMN1||'-'||TAB3(I).COLUMN2) ;
END LOOP ;
END ;

BEGIN
FOR I IN 1..10 LOOP
TAB2(I).COLUMN1 := I ;
TAB2(I).COLUMN2 := TO_DATE('23/05/2003','DD/MM/YYYY') ;
END LOOP ;

P1(TAB2) ;

EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SQLERRM) ;
END ;

I am assigning some values to array and passing it to an procedure and printing the value . The printing procedure
can exists separately also . Hope this solves your issue .
1 - 2
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,793 views