Home » SQL & PL/SQL » SQL & PL/SQL » Select from table using an array (Oracle 10g)
Select from table using an array [message #304209] Tue, 04 March 2008 09:46 Go to next message
gdottorini
Messages: 23
Registered: November 2006
Location: Napoli
Junior Member

Good Morning,
suppose i have an array declared in this way

Type Array_DateDis Is Table Of Date Index By Binary_Integer;
ArrDateDis Array_DateDis;

Suppose i have in this array n records.
Suppose i have this table too
create table pippo
(id_table number not null,
start_date date not null,
end_date date not null)

I have to select from table all the records that satifies this rule:

Select ...
from pippo
where ArrDateDis(x) between start_date and end_date

Now i'm using a loop to select single object of the array and then make the comparison. Is there any way to make "on the fly" using just the select (or something similar)?
Hope i've been clear.
Thanks in advance.

Regards,
Giulio
Re: Select from table using an array [message #304293 is a reply to message #304209] Tue, 04 March 2008 15:17 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8625
Registered: November 2002
Location: California, USA
Senior Member
If you use a SQL object type instead of an index-by table, you can use the TABLE function, as demonstrated below.

SCOTT@orcl_11g> create table pippo
  2    (id_table    number not null,
  3  	start_date  date not null,
  4  	end_date    date not null)
  5  /

Table created.

SCOTT@orcl_11g> INSERT ALL
  2  INTO pippo VALUES (1, SYSDATE - 2, SYSDATE + 2)
  3  INTO pippo VALUES (2, SYSDATE - 3, SYSDATE + 3)
  4  INTO pippo VALUES (3, SYSDATE - 2, SYSDATE - 1)
  5  INTO pippo VALUES (4, SYSDATE + 2, SYSDATE + 3)
  6  SELECT * FROM DUAL
  7  /

4 rows created.

SCOTT@orcl_11g> CREATE OR REPLACE TYPE Array_DateDis AS TABLE OF DATE;
  2  /

Type created.

SCOTT@orcl_11g> VARIABLE g_ref REFCURSOR
SCOTT@orcl_11g> DECLARE
  2    ArrDateDis Array_DateDis := Array_DateDis();
  3  BEGIN
  4    ArrDateDis.EXTEND(3);
  5    ArrDateDis(1) := SYSDATE;
  6    ArrDateDis(2) := SYSDATE - 1;
  7    ArrDateDis(3) := SYSDATE + 1;
  8    OPEN :g_ref FOR
  9    Select p.*, t.*
 10    from   pippo p,
 11  	      TABLE (ArrDateDis) t
 12    where  t.column_value between p.start_date and p.end_date
 13    ORDER  BY p.id_table, p.start_date, t.column_value;
 14  END;
 15  /

PL/SQL procedure successfully completed.

SCOTT@orcl_11g> PRINT g_ref

  ID_TABLE START_DAT END_DATE  COLUMN_VA
---------- --------- --------- ---------
         1 02-MAR-08 06-MAR-08 03-MAR-08
         1 02-MAR-08 06-MAR-08 04-MAR-08
         1 02-MAR-08 06-MAR-08 05-MAR-08
         2 01-MAR-08 07-MAR-08 03-MAR-08
         2 01-MAR-08 07-MAR-08 04-MAR-08
         2 01-MAR-08 07-MAR-08 05-MAR-08
         3 02-MAR-08 03-MAR-08 03-MAR-08

7 rows selected.

SCOTT@orcl_11g> 


Re: Select from table using an array [message #304421 is a reply to message #304293] Wed, 05 March 2008 02:38 Go to previous message
gdottorini
Messages: 23
Registered: November 2006
Location: Napoli
Junior Member

Hi Barbara,
thanks a lot for your help. It functions really nice.

Regards,
Giulio Dottorini
Previous Topic: comparing 2 tables
Next Topic: The OR operator
Goto Forum:
  


Current Time: Sun Dec 04 16:41:13 CST 2016

Total time taken to generate the page: 0.08951 seconds