Is it mandatory to use an ALIAS when we are doing some operation on the column?

Question

Folks, Is it mandatory to use an ALIAS when we are doing some operation on the column?

Ex: select upper(col1) from table1
when i am trying to retrieve the resultset by rs.getString("col1"), it was giving this exception COM.ibm.db2.jdbc.DB2Exception: [IBM][JDBC Driver] CLI0611E Invalid column name. SQLSTATE=S0022

when I changed the query to:
select upper(col1) as col1 from table1 and used rs.getString("col1"), it is working fine.

So, is it mandatory to use an ALIAS when some function like upper,trim,lower was applied on a column???

I am using: DB2 8.2, Type 2 driver

Thanks

Answer 1

Try rs.getString("upper(col1)") instead? Typically column names in the result include any functions that were applied for that column. Otherwise, you couldn't have results for things like SELECT MIN(col1),MAX(col1) ...

Answer 2

The expression UPPER(COL1) is not the same thing as COL1.

Compare this:

$ db2 "describe select col1 from session.t1"

SQLDA Information

 sqldaid : SQLDA     sqldabc: 1136  sqln: 20  sqld: 1

 Column Information

 sqltype               sqllen  sqlname.data                    sqlname.length
 --------------------  ------  ------------------------------  --------------
 453   CHARACTER           10  COL1                                         4

To this:

$ db2 "describe select upper(col1) from session.t1"

SQLDA Information

 sqldaid : SQLDA     sqldabc: 1136  sqln: 20  sqld: 1

 Column Information

 sqltype               sqllen  sqlname.data                    sqlname.length
 --------------------  ------  ------------------------------  --------------
 453   CHARACTER           10  1                                            1

Notice that the column names in each result set (sqlname.data) are not the same.

So, you would have either have to use a column alias or use rs.getString("1").