Re: Self-referring to table_name in query

Write a procedure and use dynamic SQL

-Dean

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

>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 Fri Apr 02 1999 - 00:00:00 CEST