CODEWITHSUNDEEP
javasqloracle-databasehibernateora-00905
user123475
user123475

Reputation: 1105

ORA-00905: missing keyword Hibernate Query Oracle

I have n columns in a table. I want to have selection criteria as if a value in column2 is null then select row based on column 1 and if column2 is not null then select based on the value of column 2. I have a long query which is working fine before introducing CASE WHEN THEN but I get ORA-00905 when I added case in my query. Sample query is as:

select * from table where table.testcolumn='some value' and ( case when table.column2 is NULL then  table.column1 = 'value' else table.column2 = 'value' end )

Exceptions:

Caused by: java.sql.SQLSyntaxErrorException: ORA-00905: missing keyword

    at oracle.jdbc.driver.SQLStateMapping.newSQLException(SQLStateMapping.java:91)
    at oracle.jdbc.driver.DatabaseError.newSQLException(DatabaseError.java:133)
    at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:206)
    at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:455)
    at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:413)
    at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:1035)
    at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:194)
    at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:791)
    at oracle.jdbc.driver.T4CPreparedStatement.executeMaybeDescribe(T4CPreparedStatement.java:866)
    at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1187)
    at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3386)
    at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3430)
    at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeQuery(OraclePreparedStatementWrapper.java:1491)
    at weblogic.jdbc.wrapper.PreparedStatement.executeQuery(PreparedStatement.java:128)
    at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:186)
    at org.hibernate.loader.Loader.getResultSet(Loader.java:1787)
    at org.hibernate.loader.Loader.doQuery(Loader.java:674)
    at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:236)
    at org.hibernate.loader.Loader.doList(Loader.java:2213)

Upvotes: 1

Views: 738

Answers (1)

Maciej Kowalski
Maciej Kowalski

Reputation: 26502

I think you can use an alternative statement without the usage of case when..:

select * from table 
where table.testcolumn='some value' 
and ( 
      (table.column2 is NULL and table.column1 = 'value') 
      or (table.column2 = 'value')
    )

Upvotes: 1

Related Questions