Re: Self-referring to table_name in query

On Thu, 01 Apr 1999 10:00:30 -0500, Chris Nelson <ChrisNelson_at_eaton.com> wrote:

I have not worked with Oracle for a few years, just getting back to it now. However, I suspect that as in SQL-Server, there ought to be a stored procedure or as I recall (bear with me - 6.0 was quite a while beck !!) you can retrieve table objects is the database.

Byr placing that information into a listbox/combobox you can have the user select the table.

From the table getting the fieds, again I think there was a call "DESCRIBE" to give you most of the information about the fields.

Once you put that info into a second control and this time I would use a multi-select listbox, you can now get your query built programmatically.

You could take it further and allow for WHERE selection, ORDER BY and even some advanced functions such as SUM, COUNT etc.,

Should be possible to build as a Single Form application. I know I have built similar tools in VB. I suspect that Oracle 2000 is quite as capable.

Sol.

>Hi, all.
>
>Is there a way to make table_name self-referring in a SQL Plus query?
>Put simply, here is what I am attempting:
>
>I want to make a tool to quickly find lookup_type, lookup_code, meaning,
>and description in each of several Oracle Manufacturing lookup tables,
>all at one go. My query prompts for input of substrings for
>'lookup_type', 'lookup_code' and 'meaning' (or defaults to '%' in each),
>and then does a union query in the general form of:
>
>Select (these columns)
>From table_1
>Where (meet user query criteria)
>UNION
>Select (these columns)
>From table_2
>Where (meet user query criteria)
>UNION
>Select (these columns)
>From table_3
>Where (meet user query criteria)
>(and so on)
>Order by (columns)
>/
>
>The problem with this is that I can end up with column names from any
>one or each of the tables, and I won't know which table the column came
>from. I realize that I can hard code the table name into the query
>(such as: Select 'table_1.' || lookup_type), but this is the 90s. I
>don't want to do it that way; I want SQL to do it for me.
>
>Is there a simple solution?
>
>Thanks,
> Chris
>
Received on Thu Apr 01 1999 - 00:00:00 CEST