Hello to group:
i have a table 'pedido' as follows:
CREATE TABLE PEDIDO ( codPedido VARCHAR( 200 ) NOT NULL,
PRIMARY KEY( codPedido ),
fechaPedido DATETIME NOT NULL,
fechaLimRecepcion DATETIME NOT NULL,
codEstado INT NOT NULL,
INDEX (codEstado),
FOREIGN KEY (codEstado) REFERENCES ESTADO( codEstado )
ON DELETE RESTRICT ON UPDATE CASCADE,
codUsuario INT NOT NULL,
INDEX (codUsuario),
FOREIGN KEY (codUsuario) REFERENCES USUARIO( codUsuario )
ON DELETE RESTRICT ON UPDATE CASCADE) TYPE=INNODB;
The field 'codPedido' contains this values, for example:
codPedido
cc_6
cc_7
cc_8
cc_9
cc_10
i need execute a query that return de max value of the numeric part in this field ( '10' in this case )
i execute this sql sentence in the mysql console and it works fine:
Code:
SELECT MAX( CAST( RIGHT( codPedido, LENGTH( codPedido ) - 3 ) AS SIGNED ) ) FROM `pedido`
How can i exectue this sentence through session.createSQLQuery() method ???
i have try the next code lines
Code:
session = getSessionFactory().openSession();
String sql = "select max( cast( right( ( {p}.codPedido ), length( ( {p}.codPedido ) ) - 3 ) as signed ) ) from Pedido as p";
List list = session.createSQLQuery( sql, "p", Pedido.class ).list();
and this is the output console:
Hibernate: select max( cast( right( ( p.codPedido ), length( ( p.codPedido ) ) - 3 ) as signed ) ) from Pedido as p
2004-03-18 22:06:00,030 [WARN,net.sf.hibernate.util.JDBCExceptionReporter] SQL Error: 0, SQLState: S0022
2004-03-18 22:06:00,030 [ERROR,net.sf.hibernate.util.JDBCExceptionReporter] Column 'codPedido0_' not found.
2004-03-18 22:06:00,030 [WARN,net.sf.hibernate.util.JDBCExceptionReporter] SQL Error: 0, SQLState: S0022
2004-03-18 22:06:00,030 [ERROR,net.sf.hibernate.util.JDBCExceptionReporter] Column 'codPedido0_' not found.
2004-03-18 22:06:00,090 [ERROR,net.sf.hibernate.util.JDBCExceptionReporter] SQLException occurred
java.sql.SQLException: Column 'codPedido0_' not found.
at com.mysql.jdbc.ResultSet.findColumn(ResultSet.java:2260)
at com.mysql.jdbc.ResultSet.getString(ResultSet.java:1797)
at net.sf.hibernate.type.StringType.get(StringType.java:18)
at net.sf.hibernate.type.NullableType.nullSafeGet(NullableType.java:62)
at net.sf.hibernate.type.NullableType.nullSafeGet(NullableType.java:53)
at net.sf.hibernate.loader.Loader.getKeyFromResultSet(Loader.java:347)
............
............
............
Note the sql generated by hibernate in the output console(highlighted) is just i need and it works in the mysql console.
I don't achieve understanding the behavior of this method.
Can anybody help me ? can anybody give me the sql sentence ??
Thanks in advance and sorry for my english !!
Login
FAQ
Search