At 12/17/2004 12:06 PM (Friday), Garrett Smith wrote:

>I have several tables that I want to query as if their rows were in a
>single table. My thinking was to use a temporary table and execute
>multiple 'select into' queries to populate it and then run a final
>select against it.
>
>E.g., given two tables:
>
> foo ( id integer, name varchar)
> bar ( id integer, name varchar, status integer)
>
>I want a structure looks like:
>
> some_structure (
> name varchar,
> status integer,
> foo_name varchar,
> bar_name varchar,
> bar_status integer)
>
>so I can run queries like:
>
> select id from some_structure where name = 'Baz' or status = 1;
> select id from some_structure where foo_name = 'Baz' or bar_status =
>1;
>
>Rows from table foo would NULL values for status. The underlying
>applications lets users select objects (represented by id) by specifying
>property name values. We validate input to ensure that an unqualified
>properties (e.g. 'name', as opposed to 'bar_name') have compatible
>types.

From the above description I would deduce that what you are trying to do
is actually:

Let each entity type be represented by a table and each record in each
table is an instance of that table's entity type. You want to query from
multiple entity types at once. For the result you want to obtain the entity
ID, an indication of the entity type for each ID, and the entity name.

I would write a stored procedure which accepts a string as the search
criteria (WHERE clause) and issues a series of queries (using immediate
execute), each against a specific table. Each query would return the ID,
the entity type, and the NAME. This would be instead of collecting all
those fields in the result (most of which will be NULL).

For example,

Select * from TheStoredProc('attribute1=''Baz'' or status=1')

would cause these queries to be issued within TheStoredProc:

Select T.ID, 'Table1', T.Name from table1 T where attribute1='Baz' or
status=1
Select T.ID, 'Table2', T.Name from table2 T where attribute1='Baz' or
status=1
Select T.ID, 'Table3', T.Name from table3 T where attribute1='Baz' or
status=1

and TheStoredProc would return something like
6,'Table1','nameF'
100,'Table2','nameA'
145,'Table2','nameG'
334,'Table3','nameB'
678,'Table3','nameX'

Just an alternative way of looking at the solution which has worked in
similar cases for me.