Re: Union quries: INTERSECT, MINUS, etc

Jonathon,

I've got a query for you that uses all 3 set operators at once!

I wrote it to compare two different versions of our 3rd Party Student Information System (SASI) in two different databases. We were getting ready to upgrade Production, having already upgraded a Test instance. The query hit the local schema, as well as the remote schema across a DB Link. The results of this query and a couple of others that showed brand-new tables/columns and dropped tables/columns helped our programmers figure out which of their reports, etc. needed modifications.

I was impressed at performance, considering it queried across a DB Link, but mainly because this horrendous mess of an application has over 50,000 tables (User_Tab_Columns has over 1.4 million rows!). One of the DBs is on HP-UX, but the Production DB is on Win2k.

Anyway, hope this is interesting enough. ;-)

BTW, if you can find a way to improve it, please let me know. I sort of "threw it together", knowing it would be a one-time thing, so it could probably be made better with some expert critique.

/* Get a list of columns that have changed from SASI 4.5 to 5.0

   for tables that are present in both versions only for the    current school year. List only the first 4 characters of    the table names, since all campuses will be the same. */

Spool SASI_45_50_Table_Compare.txt

(
Select Substr(TABLE_NAME,1,4) "Table" -- New 5.0 Columns

,COLUMN_NAME "Column"
,'5.0' "Ver"
,DATA_TYPE "DType"
,DATA_LENGTH "DLn"
,DATA_PRECISION "DPr"
,DATA_SCALE "DSc"
Received on Thu Jul 24 2003 - 13:17:53 CDT