Home » SQL & PL/SQL » SQL & PL/SQL » Variable in the FROM clause (11GXE, Windows XP)
Variable in the FROM clause [message #584107] Wed, 08 May 2013 13:59 Go to next message
abnk
Messages: 30
Registered: January 2012
Location: US
Member
Hi, all:


Is there a way to have a variable in the FROM clause or another way to have the table name as a variable? I'm trying to not repeat the same query three times with only the table name being different.

---the tables

create table org_a (emp_id number(5) not null, name varchar2(20));

create table org_b (emp_id number(5) not null, name varchar2(20));

create table org_c (emp_id number(5) not null, name varchar2(20));


---the records

INSERT ALL
  INTO org_a (emp_id, name) VALUES (00001, 'MISTER WHITE')
  INTO org_a (emp_id, name) VALUES (00002, 'MISTER ORANGE')
  INTO org_b (emp_id, name) VALUES (00003, 'MISTER PINK')
  INTO org_b (emp_id, name) VALUES (00004, 'MISTER BROWN')
  INTO org_c (emp_id, name) VALUES (00005, 'MISTER BLUE')
  INTO org_c (emp_id, name) VALUES (00006, 'MISTER BLOND')
SELECT * FROM dual;


---verify inserts

SELECT * FROM org_a
UNION ALL
SELECT * FROM org_b
UNION ALL
SELECT * FROM org_c;


---i want the table name to be dependent on a variable.  eventually, 
---i intend to link v_org to a form with radio buttons (values: 1, 2, 3)
---to keep this simple, i'll just assign 1 to v_org

DECLARE

  v_org number(1) := 1;
  v_table varchar2(5);

BEGIN

  v_table :=
  CASE v_org WHEN 1 THEN 'org_a'
             WHEN 2 THEN 'org_b'
             WHEN 3 THEN 'org_c'
  END;

  SELECT * FROM v_table;

END;


--this is what i receive

SQL> /
  SELECT * FROM v_table;
                *
ERROR at line 10:
ORA-06550: line 10, column 17:
PL/SQL: ORA-00942: table or view does not exist
ORA-06550: line 10, column 3:
PL/SQL: SQL Statement ignored


Thank you in advance.
Re: Variable in the FROM clause [message #584109 is a reply to message #584107] Wed, 08 May 2013 14:22 Go to previous messageGo to next message
BlackSwan
Messages: 22555
Registered: January 2009
Senior Member
you must (ab)use EXECUTE IMMEDIATE
Re: Variable in the FROM clause [message #584111 is a reply to message #584107] Wed, 08 May 2013 14:33 Go to previous messageGo to next message
Michel Cadot
Messages: 58643
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Is there a way to have a variable in the FROM clause or another way to have the table name as a variable?


Not in SQL.

Regards
Michel
Re: Variable in the FROM clause [message #584149 is a reply to message #584111] Thu, 09 May 2013 05:39 Go to previous messageGo to next message
abnk
Messages: 30
Registered: January 2012
Location: US
Member
BlackSwan wrote on Wed, 08 May 2013 14:22
you must (ab)use EXECUTE IMMEDIATE


Thank you. I will read up on it.

Michel Cadot wrote on Wed, 08 May 2013 14:33
Quote:
Is there a way to have a variable in the FROM clause or another way to have the table name as a variable?


Not in SQL.

Regards
Michel


Thank you. Do you see an alternative to having the same query three times?
Re: Variable in the FROM clause [message #584150 is a reply to message #584149] Thu, 09 May 2013 05:42 Go to previous messageGo to next message
Michel Cadot
Messages: 58643
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What is the problem of having the same query 3 times?
What is the actual issue you are trying to solve?

Regards
Michel
Re: Variable in the FROM clause [message #584152 is a reply to message #584150] Thu, 09 May 2013 05:50 Go to previous messageGo to next message
abnk
Messages: 30
Registered: January 2012
Location: US
Member
I already have 600 lines of code for org_a. The process for org_b and org_c is identical to org_a. It doesn't seem efficient to me to have 1,800 lines (the same code three times with only the table names being different) if I can have the user select the organization (table) with a radio button from a form and run the procedure against the selected organization.

Short answer, I'm just trying to not multiply my current code by three.

[Updated on: Thu, 09 May 2013 06:02]

Report message to a moderator

Re: Variable in the FROM clause [message #584154 is a reply to message #584152] Thu, 09 May 2013 06:24 Go to previous messageGo to next message
cookiemonster
Messages: 10860
Registered: September 2008
Location: Rainy Manchester
Senior Member
The obvious question is why do you have three tables when you're doing the same process against each?
Why not just combine them into one table?
Re: Variable in the FROM clause [message #584157 is a reply to message #584154] Thu, 09 May 2013 06:40 Go to previous messageGo to next message
abnk
Messages: 30
Registered: January 2012
Location: US
Member
cookiemonster wrote on Thu, 09 May 2013 06:24
The obvious question is why do you have three tables when you're doing the same process against each?
Why not just combine them into one table?


Not my database. This database was meant to be a more accessible version of a mainframe system. The data is not organized as it would normally be in a RDBMS, but it is designed to mirror the mainframe.
Re: Variable in the FROM clause [message #584158 is a reply to message #584152] Thu, 09 May 2013 07:28 Go to previous messageGo to next message
Michel Cadot
Messages: 58643
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
abnk wrote on Thu, 09 May 2013 12:50
I already have 600 lines of code for org_a. The process for org_b and org_c is identical to org_a. It doesn't seem efficient to me to have 1,800 lines (the same code three times with only the table names being different) if I can have the user select the organization (table) with a radio button from a form and run the procedure against the selected organization.

Short answer, I'm just trying to not multiply my current code by three.


So the question is not really how to put the variable in a FROM clause.
Here's an example, for one table you have:
for rec in (select * from mytable loop
<300 lines of code>
end loop;

Then convert it to:
Open c for 'select * from '||v_table';
loop
  fetch c into myvar;
  exit when c%notfound;
  <300 lines of code>
end loop;

Regards
Michel

Re: Variable in the FROM clause [message #584183 is a reply to message #584158] Thu, 09 May 2013 13:56 Go to previous messageGo to next message
abnk
Messages: 30
Registered: January 2012
Location: US
Member
Michel, I don't understand how that would work, but that's my problem. I will study the OPEN-FOR and see if I can make sense of it.

Thank you, all, for your help.
Re: Variable in the FROM clause [message #584187 is a reply to message #584183] Thu, 09 May 2013 14:45 Go to previous messageGo to next message
Michel Cadot
Messages: 58643
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Database PL/SQL User's Guide and Reference
Chapter 6 Performing SQL Operations from PL/SQL
Sections:
Managing Cursors in PL/SQL
Using Cursor Variables (REF CURSORs)

Regards
Michel

Re: Variable in the FROM clause [message #585290 is a reply to message #584158] Thu, 23 May 2013 08:37 Go to previous messageGo to next message
abnk
Messages: 30
Registered: January 2012
Location: US
Member
Michel Cadot wrote on Thu, 09 May 2013 07:28


open c for 'select * from '||v_table';
loop
  fetch c into myvar;
  exit when c%notfound;
  <300 lines of code>
end loop;

Regards
Michel


Thank you for the links. I tested cursors in different loops and I think I am comforable enough.

Using your loop example, those 300 lines are spooling a long concatenated string, for each record meeting the criteria in the WHERE clause, in a .txt file. I was doing that with a SELECT statement. Do you see a another way to write that concatenated string in a .txt file?

[Updated on: Thu, 23 May 2013 08:38]

Report message to a moderator

Re: Variable in the FROM clause [message #585292 is a reply to message #585290] Thu, 23 May 2013 09:01 Go to previous messageGo to next message
Michel Cadot
Messages: 58643
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
In SQL*Plus:
spool xx
select ...
spool off

with some "set" commands to avoid what you want or not (heading, feedback, pagesize, linesize...)

Regards
Michel
Re: Variable in the FROM clause [message #585294 is a reply to message #585292] Thu, 23 May 2013 09:40 Go to previous messageGo to next message
abnk
Messages: 30
Registered: January 2012
Location: US
Member
That's what I currently have, but it still requires a table name (org_a, org_b, or org_c) in the FROM clause. This would make the statement not reusable for each organization.


[EDITED by LF: removed unnecessary quote of the whole previous message]

[Updated on: Thu, 23 May 2013 10:28] by Moderator

Report message to a moderator

Re: Variable in the FROM clause [message #585295 is a reply to message #585294] Thu, 23 May 2013 09:46 Go to previous messageGo to next message
_jum
Messages: 489
Registered: February 2008
Senior Member
Three ways for variable/dynamic tablenames are shown just yesterday in the PL/SQL challenge.
Re: Variable in the FROM clause [message #585296 is a reply to message #585294] Thu, 23 May 2013 09:47 Go to previous messageGo to next message
BlackSwan
Messages: 22555
Registered: January 2009
Senior Member
This whole design is flawed; since the data is NOT normalized.
Single table should contain a column like ORGANIZATION_NAME which would be populated with a value of 'A', 'B', or 'C'
After the design is corrected the solution is trivial!
Re: Variable in the FROM clause [message #585297 is a reply to message #585296] Thu, 23 May 2013 09:57 Go to previous messageGo to next message
pablolee
Messages: 2612
Registered: May 2007
Location: Scotland
Senior Member
This is true, however, that point has already been pointed out by cookie and explained by the OP. Not everybody (in fact, very few people) has the access/ ability to redesign their company's database(s) especially if their responsibility is centred around extracting data from the db rather than maintaining, designing administering to those dbs and so have to work with what they have, warts and all. But of course, you know that, I'm sure.
Re: Variable in the FROM clause [message #585299 is a reply to message #585296] Thu, 23 May 2013 10:08 Go to previous messageGo to next message
abnk
Messages: 30
Registered: January 2012
Location: US
Member
_jum wrote on Thu, 23 May 2013 09:46
Three ways for variable/dynamic tablenames are shown just yesterday in the PL/SQL challenge.


Link did not work, but I think this is what you were referring to PL/SQL Challenge Thank you.


BlackSwan wrote on Thu, 23 May 2013 09:47
This whole design is flawed; since the data is NOT normalized.
Single table should contain a column like ORGANIZATION_NAME which would be populated with a value of 'A', 'B', or 'C'
After the design is corrected the solution is trivial!


I will see if I can get access to create a temporary view where I can add that org_name column, INSERT all values from all three tables, and SELECT FROM there.
Re: Variable in the FROM clause [message #585300 is a reply to message #585294] Thu, 23 May 2013 10:16 Go to previous messageGo to next message
Michel Cadot
Messages: 58643
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
abnk wrote on Thu, 23 May 2013 16:40
That's what I currently have, but it still requires a table name (org_a, org_b, or org_c) in the FROM clause. This would make the statement not reusable for each organization.


You can pass the table name to the script.

And you can easily write a script that generates a call to this previous script passing the table name for all those you want.

Regards
Michel
Re: Variable in the FROM clause [message #585303 is a reply to message #585300] Thu, 23 May 2013 11:11 Go to previous messageGo to next message
gazzag
Messages: 278
Registered: November 2010
Location: Bristol, UK
Senior Member
Would a VIEW help at all?
Re: Variable in the FROM clause [message #585327 is a reply to message #585300] Thu, 23 May 2013 12:50 Go to previous messageGo to next message
abnk
Messages: 30
Registered: January 2012
Location: US
Member
Michel Cadot wrote on Thu, 23 May 2013 10:16

You can pass the table name to the script.

And you can easily write a script that generates a call to this previous script passing the table name for all those you want.

Regards
Michel


Are you referring to a script as defined here?
Re: Variable in the FROM clause [message #585328 is a reply to message #585327] Thu, 23 May 2013 12:53 Go to previous messageGo to next message
Michel Cadot
Messages: 58643
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
No, SQL script, a file containing SQL and/or PL/SQL statements and SQL*Plus commands.
If you put:
spool xxx_&1
select * from &1;
spool off
in a file, this is a script.

Regards
Michel

[Updated on: Thu, 23 May 2013 12:54]

Report message to a moderator

Re: Variable in the FROM clause [message #585986 is a reply to message #585328] Fri, 31 May 2013 12:43 Go to previous message
abnk
Messages: 30
Registered: January 2012
Location: US
Member
I have tested the solutions and they work. Smile

Thank you, all. Merci a tous.
Previous Topic: total count per day query
Next Topic: cross database join on fields with mismatched data types
Goto Forum:
  


Current Time: Fri Aug 01 10:32:54 CDT 2014

Total time taken to generate the page: 0.12255 seconds