Embeded subquery for table name Pl SQL

I'm trying to write query that gets it's table names from a sub query.

I tried Select * from TABLE(Select tablename from tablename where x=foo)
 and I get and error: [Oracle][ODBC][Ora]ORA-22905:cannot access rows from a non-nested table item

I tried Select * from (Select tablename from tablename where x=foo)  T
 and get no error but there are not fields avaiable
txbtechAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
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.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
you cannot do that without dynamic sql.
and as you want some SELECT to be executed, that will be a dynamic cursor:
http://www.adp-gmbh.ch/ora/plsql/cursors/dynamic.html
0
txbtechAuthor Commented:
Appearently you can do this as long as you cast the table query. http://www.error-code.org.uk/view.asp?e=ORACLE-ORA-22905
But I don't know what to cast as. I tried "(Cast(select ....) as Table)" and that didn't work
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
the TABLE() construct can only take a cursor or an array/collection as input, and not a "single value that represents the table name". CAST() as such is of no help here.
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

txbtechAuthor Commented:
So since I can only send oracle a select statement via Crystal Reports to get my data, I'm hosed? Do you have any suggestions?
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>So since I can only send oracle a select statement via Crystal Reports to get my data
can you clarify, please? not sure what you mean?
0
txbtechAuthor Commented:
I'm using crystal reports and entering this select statement in the SQL Command window.  So, I can't write any PL SQL procedures with variables, if that is what you mean by Dynamic SQL.  therefore, I'm stuck.

I guess I should ask you to clearify "Dynamic SQL".
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
I have only done a couple of reports in CR, so bare with me on that part.

however, I remember that indeed you enter the sql to be executed in the CR interface, so where is the problem?

from what I guess, you don't want to write the actual sql in the report itself, but have it (the sql) stored in a table , and just refer to the key information to identify which SQL to run for that particular report.
is that correct?
0
txbtechAuthor Commented:
That is correct. There is a command window where you enter the SQL to be executed and entered the following query which resulted in an error:
Select * from (Select tablename from tablename where x=foo)  T

I don't know which table I need to pull from. The correct table name is the result of the subquery in paranthasis.
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
so I have to repeat:
you cannot do that without dynamic sql.

you really should create a view for each of those reports, and base your report on the view (select * from view_name).

hence, when a report's SQL has to be changed (ie the table it comes from), all you have to do is to change the view.

now, you might want to clarify what exactly is the "table name changing requirement", as eventually there are some other solutions?¨
0
txbtechAuthor Commented:
The problem with that is I have readonly access to the DB.

There are over 300 structurally identical tables that this could be pulling from, so it would not be very feasible to create a view for each.

Again, what do you mean by "Dynamic SQL"? Stored Proc perhapse?

If that is the only solution, then I'm hosed.
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>Again, what do you mean by "Dynamic SQL"? Stored Proc perhapse?
yes, well a function actually, but that's indeed the only solution (using either a function returning data PIPELINEd , or via a REF CURSOR)...
0

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
txbtechAuthor Commented:
I don't suppose there is any way of using either in a Select statement is there?
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
at least not that I know. the table (and column) names cannot get put dynamically.
0
txbtechAuthor Commented:
Is that Business Objects offcial stance? I ask because the report works fine on my desktop.
0
txbtechAuthor Commented:
This should work, but it seems that the server isn't executing this properly.

https://www.sdn.sap.com/irj/servlet/prt/portal/prtroot/com.sap.km.cm.docs/oss_notes/sdn_oss_boj_erq/sap(bD1lbiZjPTAwMQ==)/bc/bsp/spn/scn_bosap/notes{6163636573733d36393736354636443646363436353344333933393338323636393736354637333631373036453646373436353733354636453735364436323635373233443330333033303331333233313335333133383336}.do

This subreport runs fine even on the server as a stand alone report, but as a subreport it fails.
0
txbtechAuthor Commented:
It took a lot of prompting for the expert to finally give the specifics.
0
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.