Home » SQL & PL/SQL » SQL & PL/SQL » Populate A Table Prior to Select (10g)
Populate A Table Prior to Select [message #293974] Tue, 15 January 2008 16:54 Go to next message
michael2007
Messages: 6
Registered: January 2008
Junior Member
Hopefully someone can give me a fix to a problem we encounter here at work frequently.
Our reporting application has a restriction that it can only accept a single select statement. This makes for a BIG select statement. I moved much of the code to a View and kept a simple select with the record key in the application. Unfortunately the view processes all records for a long time before returning the single key requested. I need the view to look at the one key only.

I was hoping that I could put a function in the report select statement that would cause a table to be loaded with the key and have the view join to this key table. Is something like this possible or is there a better way?

My idea goes something like this:
Report Application:
select *
from report_view a
where my_key_function(1000) = 0


my_key_function:
(loads key into key_table)

Report View:
select *
from report_data a
inner join key_table b
on b.key = a.key

Any help is greatly appreciated! Smile
Re: Populate A Table Prior to Select [message #294000 is a reply to message #293974] Tue, 15 January 2008 23:16 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You can use a pipelined function and something like:
select * from report_data 
where key in (select * from table(my_key_function(1000))

What is the source of my_key_function? Maybe you can merge it in the query.
What are the limits of your tool?
It is most often better to have big query than to call a function.

Regards
Michel
icon10.gif  Re: Populate A Table Prior to Select [message #294104 is a reply to message #294000] Wed, 16 January 2008 08:20 Go to previous messageGo to next message
michael2007
Messages: 6
Registered: January 2008
Junior Member
Shocked Shocked Wow! What a powerful function - They did not mention pipelined table function in my PL/SQL class. The perfect solution - Thank You!
Re: Populate A Table Prior to Select [message #294108 is a reply to message #294104] Wed, 16 January 2008 08:42 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
May not be perfect for the performances.
You can also use, with the same query syntax, a function that returns an array.
SQL> create or replace type my_type is table of number;
  2  /

Type created.

SQL> create or replace function f(n integer) return my_type as
  2    my_tab my_type;
  3  begin
  4    select level bulk collect into my_tab from dual connect by level <= n;
  5    return my_tab;
  6  end;
  7  /

Function created.

SQL> select * from table(f(5));
COLUMN_VALUE
------------
           1
           2
           3
           4
           5

5 rows selected.


Regards
Michel
icon14.gif  Re: Populate A Table Prior to Select [message #294183 is a reply to message #294108] Wed, 16 January 2008 13:43 Go to previous message
michael2007
Messages: 6
Registered: January 2008
Junior Member
Cool Thank You Michel!
This looks even easier. I have been able to run a test of your earlier solution and it is working well - Thank You! As soon as I get a breather here at work, I'm going to implement your solution into production.

Again, Thank You!
Michael
Previous Topic: ALERT LOGS
Next Topic: Looking for a proper search term
Goto Forum:
  


Current Time: Sat Dec 03 07:44:31 CST 2016

Total time taken to generate the page: 0.06800 seconds