Case Sensitivity in column aliases

[y***@unisystems.gr]

Hello to all
I am new to DB2 and encountered a "problem" on the way
DB2 UDB 8.2 (UTF8 code page) treates aliases.

We try to make a join between to tables and use an allias to define the name of a column at the result set.

When the alias is written in UPPERCASE it works fine. If the alias is lowercase it does not.

This simple SQL commands can show the problem


CREATE TABLE DB2ADMIN.TABLE1 ( common CHARACTER (10) NOT NULL , t1col2 CHARACTER (10) NOT NULL ) ;

CREATE TABLE DB2ADMIN.TABLE2 ( common CHARACTER (10) NOT NULL , t2col2 CHARACTER (10) NOT NULL ) ;

SELECT table1.common, table1.t1col2 from table1
union select table2.common, table2.t2col2 "t1col2" from table2


This results to the second column to be names as "2" instead of "t1col2"
If we give the select union statement as

select table1.common, table1.t1col2 from table1
union select table2.common, table2.t2col2 "T1COL2" from table2

it works fine.

Is there a way, a setting or something to change this behavior ?????
(In ORACLE this works no matter lower or uppercase)

[tonkuma]

I think there is no setting to change the behavior.
DB2 always change output column names to upper case without rename and surrounding it by double quotations.
If you want to show the column name with lower case, rename second column of first select statement too.

[code]------------------------------ Commands Entered ------------------------------
SELECT table1.common, table1.t1col2 "t1col2" from table1
UNION
SELECT table2.common, table2.t2col2 "t1col2" from table2;

[Knut Stolze]

Post by y***@unisystems.gr
When the alias is written in UPPERCASE it works fine. If the alias is
lowercase it does not.
SELECT table1.common, table1.t1col2 from table1
union select table2.common, table2.t2col2 "t1col2" from table2

The first select produces a table with the column names COMMON and T1COL2.
(You did not double-quote them, so DB2 converts them to upper case.) The
second table has columns named COMMON and t2col2. Even if you ignore the
double-quotes, you have a different name for the second column.

With the union operator in the picture, DB2 can not determine a common
column name for the second column of both tables. So what would you expect
to happen now? DB2 generates a new column name for you, which happens to
be "2".

Note that you also have the same issue with upper vs. lower case:

SELECT table1.common, table1.t1col2
FROM table1
UNION table2.common, table2.t2col2 "t2col2"
FROM table2

The first table has T1COL2 as name for the 2nd column, and the 2nd table has
t1col2. This are again different column names and DB2 will generate a new
name to address this ambiguity.