Home » SQL & PL/SQL » SQL & PL/SQL » Dynamic Join (10g)
Dynamic Join [message #357805] Thu, 06 November 2008 12:51 Go to next message
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 #357829 is a reply to message #357805] Thu, 06 November 2008 19:49 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
the problem seems to be the tool. You say it is interpreting the sql query on the way in, eg. it is parsing the sql? If so then what sql does it actually send to the database?

i would first look to see if there is an option within the tool that would fix this for you. Maybe you can turn pass-through SQL on.

In any event, some questions for you:

Quote:
how does the tool connect to the database? odbc, sqlnet, java connection?

will the too let you call stored procedures?

Will the tool let you call a funtion that returns a cursor?

All of these options would offer a solution to your issue I think.

Good luck, Kevin
Re: Dynamic Join [message #358049 is a reply to message #357805] Sat, 08 November 2008 00:05 Go to previous message
Barbara Boehmer
Messages: 8620
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> 

Previous Topic: can i add a new column in the middle of a table
Next Topic: PL/SQL Procedures using time
Goto Forum:
  


Current Time: Fri Dec 02 12:24:00 CST 2016

Total time taken to generate the page: 0.07899 seconds