Re: select list: expression values

[Jeff Butler <je...@gmail.com>]

You might need to do this:

select elemento - 1 as elemento, ...

sometimes databases rename these calculated columns.

If you run the query in the Squirrel SQL client you can see exactly what's
happening.

Jeff Butler


On 11/17/06, c.zecca@ads.it <c....@ads.it> wrote:
>
>  Hi all
>
> The following SQL map and query
>
> <resultMap id="mapTipoRiga" class="
> it.finmatica.gpj.ec.tipidato.TipoCampoBean">
> <result property="id" column="TIPO_RIGA_ID" jdbcType="NUMBER"
> javaType="int"/>
> <result property="ordinale" column="ELEMENTO" jdbcType="NUMBER"
> javaType="int"/>
> <result property="nome" column="NOME" typeHandler="
> it.finmatica.gpj.aa.frontebd.NomeTypeHandlerCallback"/>
> <result property="idTipoOrizzontale" column="TIPO_ELEMENTO_ID"
> jdbcType="NUMBER" javaType="int"/>
> </resultMap>
>
> <select id="selectTipoRiga" resultMap="mapTipoRiga">
> select
> TIPO_RIGA_ID, *ELEMENTO - 1*, NOME, TIPO_ELEMENTO_ID
> from TIPI_DATO_RIGA
> where tipo_dato_id = #id:INTEGER#
> </select>
>
>
> raises the following exception
>
>
>    DEBUG [main] - {pstm-100001} PreparedStatement:    select
>     TIPO_RIGA_ID, ELEMENTO - 1, NOME, TIPO_ELEMENTO_ID   from TIPI_DATO_RIGA
>    where tipo_dato_id = ?
>    DEBUG [main] - {pstm-100001} Parameters: [21]
>    DEBUG [main] - {pstm-100001} Types: [java.lang.Integer]
>    *com.ibatis.common.jdbc.exception.NestedSQLException*:
>    --- The error occurred in it/finmatica/gpj/aa/frontebd/TipoRiga.xml.
>
>    --- The error occurred while applying a result map.
>    --- Check the GPJ.mapTipoRiga.
>    --- Check the result mapping for the 'ordinale' property.
>    --- Cause: *java.sql.SQLException*: Invalid colum name
>    Caused by: *java.sql.SQLException*: Invalid colum name
>    at
>    com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.executeQueryWithCallback
>    (*GeneralStatement.java:185*)
>    at
>    com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.executeQueryForList
>    (*GeneralStatement.java:123*)
>
>
>
> I have to load an 1-based index (stored in the ELEMENTO column) to
> transform it into a 0-based Java index. I want to avoid to write a type
> handler, thus i thought to use an expression as part of a select list<http://www.firstsql.com/tutor3.htm#op>
>
> select ELEMENTO - 1
>
> Please note that the query above is correct and is successfully run from
> PL/SQL developer.
>
> Replacing the "ELEMENTO - 1" expression with the "ELEMENTO" column "works
> out" the problem but loades 1-based indexes. :(
>
> Is there anything wrong?
> Shall I write a type handler? (I cannot change the ordinale property of
> the TipoCampoBean)
>
> Regards
>
>