Splicing Table name with variable


Looking to splice table name with another data pull

One table I have
table test
ID       NO
123   55
124   57

select * from XXNOXX a, test b
where a.id = b.id  (Matched only on 123)
in other words the actual query would be
select * from XX55XX a, test b
where a.id = b.id

Eventually want to union the queries

select * from XX55XX a, test b
where a.id = b.id
select * from XX57XX a, test b
where a.id = b.id

Tables XX55XX and XX57XX  exist of course.
 Any help would be much appreciate.


Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

how may different tables do you have?

other than generating dynamic SQL on the fly and executing it, another approach may be to make a VIEW,
SELECT '55' as no, a.* FROM XX55XX a
SELECT '57' as no, a.* FROM XX57XX a;

and then join to the view, e.g.
select * from XXNOXX a, test b
where a.id = b.id
AND a.no = b.no
Franck PachotCommented:
I don't know if you can change the design, and you have Entreprise Edition, but your table looks like a godd candidate for partitioning: same logical structure, different physical storage.
Göran AnderssonCommented:
If the tables have the same layout and only differs by name, they shouldn't be separate tables. The data should be in the tables, not part of the table name. If possible, make a single table out of them, with one added field that contains that number from the table name. That way you can easily query the table without creating the SQL dynamically.

If you can't change the database design, you have to create the query dynaimcally.
slightwv (䄆 Netminder) Commented:
I agree that if possible, change the design.  If you have the license for partitioning by all means use it.

That said:  I've seen the 'pre-create a view with ALL tables' as suggested in http:#30090305.  It didn't work that well from a performance standpoint.  Why scan ALL tables when you are only after 2?

All that said:  What do you want to do with the results once you get them?

Here's a simple function that returns a refcursor.  You can change it to a pilelined function if you really want to 'select' the values like a real table.

drop table tab1 purge;
create table tab1(id number);

drop table tab2 purge;
create table tab2(id number);

drop table test purge;
create table test(id number, no number);

insert into tab1 values(1);
insert into tab2 values(2);

insert into test values(123,1);
insert into test values(123,2);

create or replace function myFunc(inId number) return sys_refcursor is
	mySQL varchar2(4000);
	myCursor sys_refcursor;
	--set initial dummy select to keep from coding a lot of exceptions in case you pass in a
	--an id that returns no rows
	mySQL := ' select null from dual union ';
	for i in (select no from test where id=inId) loop
		mySQL := mySQL || 'select id from tab' || i.no || ' where id=' || i.no || ' union ';
	end loop;

	mySQL := rtrim(mySQL,'union ');

	open myCursor for mySQL;
	return myCursor;

show errors

select cursor( select myfunc(123) from dual) from dual;

--return nothin w/o error
select cursor( select myfunc(999) from dual) from dual;

Open in new window


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
B_riderAuthor Commented:
Great thanks
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.