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

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").

Answer 2

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) ...

友情链接