Hi,
my question is rather simple. I would like to express the following query with the Criteria interface. Is this possible and if yes how ?
Code:
select *
from PE_REQUESTS t1
where NOT EXISTS
( select 1
from PROVISIONING_LOCKS t2
where t2.request_number = t1.request_number )
The only way I see is using a Query with session.createSQLQuery()
And even if I try like that with the following code :
Code:
String[] aliases = new String[2];
Object[] classes = new Class[2];
aliases[0] = "pe";
aliases[1] = "lock";
classes[0] = PendingRequest.class;
classes[1] = LockData.class;
Query query = session.createSQLQuery("select {pe.*} from PE_REQUESTS {pe} where NOT EXISTS ( select {lock.*} from PROVISIONING_LOCKS {lock} where {pe.id} = {lock.requestNumber} )", (String[])aliases, (Class[])classes);
I get an exception with the following generated sql :
Code:
Hibernate: select pe.REQUEST_NUMBER as REQUEST_1_0_, pe.CREATION_DATETIME as CREATION2_0_, pe.REQUEST_DATETIME as REQUEST_3_0_, pe.INTERFACE as INTERFACE0_, pe.LAST_UPDATE_DATETIME as LAST_UPD5_0_, pe.LAST_USER_ID as LAST_USE6_0_, pe.NE_TYPE as NE_TYPE0_, pe.PRIMARY_SUBS_ID as PRIMARY_8_0_, pe.PRIORITY as PRIORITY0_, pe.REQUEST_PROCESS_ID as REQUEST10_0_, pe.RETRY_COUNTER as RETRY_C11_0_, pe.WORKFLOW_VERSION as WORKFLO12_0_, pe.SECONDARY_SUBS_ID as SECONDA13_0_, pe.REQUEST_SEQ_NUM as REQUEST14_0_, pe.REQUEST_STATUS as REQUEST15_0_, pe.TASK_DATA as TASK_DATA0_, pe.TASK_TYPE as TASK_TYPE0_, pe.REQUEST_USER_ID as REQUEST18_0_, pe.HISTORY as HISTORY0_ from PE_REQUESTS pe where NOT EXISTS ( select lock.ID as ID1_, lock.INTERFACE as INTERFACE1_, lock.SUBSCRIBER_ID as SUBSCRIB3_1_, lock.REQUEST_NUMBER as REQUEST_4_1_ from PROVISIONING_LOCKS lock where REQUEST_1_0_ = REQUEST_4_1_ )
net.sf.hibernate.JDBCException: SQLException occurred
at net.sf.hibernate.impl.SessionImpl.findBySQL(SessionImpl.java:3809)
at net.sf.hibernate.impl.SQLQueryImpl.list(SQLQueryImpl.java:52)
at be.belgacom.mobile.fiona.tools.TestCriteriaQueries.test_Query1(TestCriteriaQueries.java:78)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
at java.lang.reflect.Method.invoke(Unknown Source)
at junit.framework.TestCase.runTest(TestCase.java:154)
at junit.framework.TestCase.runBare(TestCase.java:127)
at junit.framework.TestResult$1.protect(TestResult.java:106)
at junit.framework.TestResult.runProtected(TestResult.java:124)
at junit.framework.TestResult.run(TestResult.java:109)
at junit.framework.TestCase.run(TestCase.java:118)
at junit.framework.TestSuite.runTest(TestSuite.java:208)
at junit.framework.TestSuite.run(TestSuite.java:203)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:421)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:305)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:186)
Caused by: java.sql.SQLException: ORA-00936: missing expression
at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134)
at oracle.jdbc.ttc7.TTIoer.processError(TTIoer.java:289)
at oracle.jdbc.ttc7.Oall7.receive(Oall7.java:582)
at oracle.jdbc.ttc7.TTC7Protocol.doOall7(TTC7Protocol.java:1986)
at oracle.jdbc.ttc7.TTC7Protocol.parseExecuteDescribe(TTC7Protocol.java:880)
at oracle.jdbc.driver.OracleStatement.doExecuteQuery(OracleStatement.java:2516)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:2850)
at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:609)
at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:537)
at net.sf.hibernate.impl.BatcherImpl.getResultSet(BatcherImpl.java:87)
at net.sf.hibernate.loader.Loader.getResultSet(Loader.java:875)
at net.sf.hibernate.loader.Loader.doQuery(Loader.java:269)
at net.sf.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:133)
at net.sf.hibernate.loader.Loader.doList(Loader.java:1033)
at net.sf.hibernate.loader.Loader.list(Loader.java:1024)
at net.sf.hibernate.loader.SQLLoader.list(SQLLoader.java:92)
at net.sf.hibernate.impl.SessionImpl.findBySQL(SessionImpl.java:3806)
... 17 more
So if someone can help me on that stuff it would be very appreciated !
Thanks for your time anyway.
Regards.
Login
FAQ
Search