Dynamic Join [message #357805] |
Thu, 06 November 2008 12:51  |
OraNoob
Messages: 3 Registered: November 2008
|
Junior Member |
|
|
I'm finding this hard to explain, but I need help with a sort of dynamic join. We're using a 3rd party reporting tool that isn't flexible enough in handling SQL/dynamic SQL to do what I want, so I'm looking for the simplest way to do this on the DB side.
I have a master "key" table (filled with people records) that the user can filter. I also have a set of views, which use the same person key, that I want to allow the user to join to dynamically.
So, for example, I am providing the user a drop down list of views. He selects a view, say, the ADDRESS view, and the reporting tool will returned his filtered master results joined with the ADDRESS records. If he chooses the TELEPHONE view same deal.
If I were to write this out in pl/sql, my dynamic sql might look something like:
sqlText := 'SELECT * FROM '||DropDownListViewChosen||'
JOIN MASTERKEYTABLE ON '||DropDownListViewChosen||'.KeyField = MASTERKEYTABLE.KeyField';
The reporting tool is the challenege here - it cannot evaluate the above SQL because it does not recognize the "DropDownListViewChosen" as a true table, so it can't validate your SQL and won't allow the code.
So I guess what I'm looking for is a way to create a view object of some sort in the database on the fly...using any one of a number of my already defined views that would be passed in when the user chooses it.
Maybe some sort of pipelined table...???
SELECT * FROM TABLE (generate_view(DropDownListViewChosen))
I'm out of ideas and I don't know enough about oracle to know what my options are...any help is appreciated...
|
|
|
|
Re: Dynamic Join [message #358049 is a reply to message #357805] |
Sat, 08 November 2008 00:05  |
 |
Barbara Boehmer
Messages: 9104 Registered: November 2002 Location: California, USA
|
Senior Member |
|
|
Any time that you have a variable that is an object, like a table name or column name, it has to be referenced dynamically. The problem with using a function to return a pipelined table is that the structure of the row returned would have to be the same for each view that it could be joined to. The usual method is to create a function to open a ref cursor dynamically and return that ref cursor to your calling program, but you cannot use that function in a sql select statement. I have provided a simplified demonstration below.
-- test environment:
SCOTT@orcl_11g> CREATE TABLE masterkeytable
2 (KeyField NUMBER,
3 name VARCHAR2(5))
4 /
Table created.
SCOTT@orcl_11g> INSERT INTO masterkeytable VALUES (1, 'name1')
2 /
1 row created.
SCOTT@orcl_11g> INSERT INTO masterkeytable VALUES (2, 'name2')
2 /
1 row created.
SCOTT@orcl_11g> CREATE TABLE addresstable
2 (KeyField NUMBER,
3 address VARCHAR2(10),
4 city VARCHAR2(10),
5 state VARCHAR2(4),
6 zip NUMBER)
7 /
Table created.
SCOTT@orcl_11g> INSERT INTO addresstable VALUES (1, 'address1', 'city1', 'CA', '900000')
2 /
1 row created.
SCOTT@orcl_11g> INSERT INTO addresstable VALUES (2, 'address2', 'city2', 'CA', '900000')
2 /
1 row created.
SCOTT@orcl_11g> CREATE OR REPLACE VIEW addressview AS
2 SELECT * FROM addresstable
3 /
View created.
SCOTT@orcl_11g> CREATE TABLE telephonetable
2 (KeyField NUMBER,
3 area_code NUMBER,
4 phone NUMBER)
5 /
Table created.
SCOTT@orcl_11g> INSERT INTO telephonetable VALUES (1, 800, 1234567)
2 /
1 row created.
SCOTT@orcl_11g> INSERT INTO telephonetable VALUES (2, 800, 7654321)
2 /
1 row created.
SCOTT@orcl_11g> CREATE OR REPLACE VIEW telephoneview AS
2 SELECT * FROM telephonetable
3 /
View created.
-- function:
SCOTT@orcl_11g> CREATE OR REPLACE FUNCTION generate_view
2 (DropDownListViewChosen IN VARCHAR2)
3 RETURN SYS_REFCURSOR
4 AS
5 sqlText VARCHAR2(32767);
6 results SYS_REFCURSOR;
7 BEGIN
8 sqlText := 'SELECT * FROM '||DropDownListViewChosen||'
9 JOIN MASTERKEYTABLE ON '||DropDownListViewChosen||'.KeyField = MASTERKEYTABLE.KeyField';
10 OPEN results FOR sqlText;
11 RETURN results;
12 END generate_view;
13 /
Function created.
SCOTT@orcl_11g> SHOW ERRORS
No errors.
-- refcursor variable to hold results:
SCOTT@orcl_11g> VARIABLE g_ref REFCURSOR
-- sample execution and results for addressview:
SCOTT@orcl_11g> EXEC :g_ref := generate_view ('ADDRESSVIEW')
PL/SQL procedure successfully completed.
SCOTT@orcl_11g> PRINT :g_ref
KEYFIELD ADDRESS CITY STAT ZIP KEYFIELD NAME
---------- ---------- ---------- ---- ---------- ---------- -----
1 address1 city1 CA 900000 1 name1
2 address2 city2 CA 900000 2 name2
-- sample execution and results for telephoneview:
SCOTT@orcl_11g> EXEC :g_ref := generate_view ('TELEPHONEVIEW')
PL/SQL procedure successfully completed.
SCOTT@orcl_11g> PRINT :g_ref
KEYFIELD AREA_CODE PHONE KEYFIELD NAME
---------- ---------- ---------- ---------- -----
1 800 1234567 1 name1
2 800 7654321 2 name2
SCOTT@orcl_11g>
|
|
|