Re: Self-referring to table_name in query

Sure, use table aliases.
Ex:

SELECT a.foo, a.foo1
FROM table1 a
WHERE a.xyz=...
UNION
SELECT b.baz, b.baz2
FROM table2 b
WHERE b.bar>...
UNION
SELECT c.foo, c.foo1
FROM table1 c
WHERE c.fubar<...
ORDER BY 1, 2 dean.reynolds_at_home.net wrote in message <3704106f.10061297_at_news>...
>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