• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 769
  • Last Modified:

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
0
txbtech
Asked:
txbtech
  • 9
  • 7
1 Solution
 
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
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
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
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 9
  • 7
Tackle projects and never again get stuck behind a technical roadblock.
Join Now