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

aliasdb2exception

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

Best Answer

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

Related Solutions

R – com.ibm.db2.jcc.b.SqlException: Invalid argument: unknown column name COL1

Nothing in DB2 or SQL requires the column names of functions to be specific values.

It may be that the earlier drivers (or even the t2 drivers, which I would generally never use anymore, preferring t4 myself) gave you a column name you expected but I wonder what it would have given you for col1 | '.' | col2.

Your query really should be what you have to get it working:

select upper(col1) as col1 from table1

This guarantees the column name is col1.

If you really want to know what the column names are for your query, you can retrieve the meta data for the result set with the following calls (examples only):

ResultSet rs = <get your result set here>;
ResultSetMeatData meta = rs.getMetaData();
for (int i = 1; i < meta.getColumnCount(); i++) {
    System.out.println (getColumnName (i));
}

But you should be using the as clause to ensure the names are as expected.

R – BIRT with db2 giving problem

This may have nothing to do with BIRT specifically. Perhaps your query to retrieve the data. Have you tried using the jdbc driver stand alone in a simple Java program to try and retrieve the data? This may be easier to debug (as you can actually attach a debugger) than from within BIRT.

Best Answer

Related Solutions

R – com.ibm.db2.jcc.b.SqlException: Invalid argument: unknown column name COL1

R – BIRT with db2 giving problem

Related Topic