odd results from table aliases

From: Larry Holder <lholder_at_utm.edu>
Date: Tue, 29 Aug 2000 12:24:34 -0500
Message-Id: <10603.115757@fatcity.com>

Here's something that's been puzzling me quite awhile. Can anyone shed some light on it?

Here's a query that compares some data dictionary info on one database with the data dictionary on another.

select a.column_name,
       a.data_type,
       b.data_type
from dba_tab_columns a,
     dba_tab_columns_at_PROD_LINK b
where a.owner = 'GENERAL'
  and a.owner       = b.owner
  and a.table_name  = b.table_name

  and a.column_name = b.column_name
  and (a.data_type != b.data_type)

Now, you'd expect to see the value of data_type from table alias "a", followed by the value of data_type from table alias "b". However, I end up seeing, in both result columns, the same value (from alias "b").

Oddly enough, if I do some sort of concatenation, the expected results are returned. For example, here I've concatenated an empty string to the data_type results:

select a.column_name,
       a.data_type || '',
       b.data_type || ''
from dba_tab_columns a,
     dba_tab_columns_at_PROD_LINK b
where a.owner = 'GENERAL'
  and a.owner       = b.owner
  and a.table_name  = b.table_name

  and a.column_name = b.column_name
  and (a.data_type != b.data_type)

In this case, I get the expected results (for example, "RAW" for alias "a", "VARCHAR2" for alias "b").

I've run across this same sort of anomaly before, and have yet to figure out -- is it a bug or a feature? :-o

Any guru's with a solution?

Thanks!

Larry Holder
Senior Systems Analyst, Oracle Database Administrator The University of Tennessee at Martin Computer Center lholder_at_utm.edu (901) 587-7890 www.utm.edu/~lholder Received on Tue Aug 29 2000 - 12:24:34 CDT