Re: Interesting Oracle/JDBC Problem

Michael Cordner wrote:
> select * from arc_10750 where fuzzy=2 UNION select * from arc_10751 where
> fuzzy=2 UNION select * from arc_10752 . . .
>
> The problem arises when we allow fields to be added to next day's table.
> When we add a field to the schema, the field is included in all future
> tables.
>
> Say that these are the fields in arc_10750:
>
> key, field1, field2, fuzzy
>
> The next day, we decide to add a field to the archive system. arc_10751, and
> all later tables, have these fields:
>
> key, field1, field2, fuzzy, newfield
>
> If I execute the above UNION statement across these two tables, I get an
> Oracle error which tells me that the query block has an invalid number of
> result columns. This makes sense, since I'm trying to UNION tables with
> different fields.
>
> So here's the problem: I need to somehow modify this statement to make it
> work. I need to be able to search across the entire range of tables,
> regardless of whether later tables have added fields. I'm using the JDBC
> thin driver to access the db, and I have access to a set of metadata tables
> which describe what the fields are in a table for any given date.
>
> If anyone has come across a similar situation, I'd appeciate some advice. Is
> there a way to somehow "widen" a resultset?

The easiest way is to "widen" the result column list for the earlier tables, substituting an acceptable literal (you might need to do a convert), like this ...

select arc_10750.*,' ' from arc_10750 where fuzzy=2 UNION select * from arc_10751 where fuzzy=2 UNION . . .

(the literal ' ' matches the new field in arc_10751 in size and type). If Oracle won't let you use the 'arc_10750.*' construct, you'll just have to list the fields.

--
Lee Fesperman, FFE Software, Inc. (http://www.firstsql.com) Received on Wed Jun 23 1999 - 00:32:02 CDT