undefined alias: decode

Roberto · **Posted:** Sun Nov 16, 2003 11:31 pm

I have added decode=decode to query.substitutions but it's not work
did I miss anything else?

could any one tell me how to use decode() function of oracle in hiberante query language? thanks a lot.

david · **Posted:** Mon Nov 17, 2003 3:42 am

Decode is not supported as a function in the select clause.

Roberto · **Posted:** Mon Nov 17, 2003 9:29 pm

Thank you, David.

so that I'm tring use createSQLQuery to solve this problem.

Code:

StringBuffer sb = new StringBuffer("select {nego}.stk_no as {nego.stkNo}, {equity}.stk_name as {equity.stkNo} "); 
sb.append("from cbotctrad as {nego}, te.testmb as {equity} "); 
sb.append("where {nego}.stk_no = {equity}.stk_no"); 
Query q = session.createSQLQuery(sb.toString(), new String[] {"nego", "equity"} 
, new Class[] {Negotiation.class, Equity.class}); 

the sql statement is simple but got following errors

Code:

20:59,703 INFO [STDOUT] Hibernate: select nego.stk_no as stk_no0_, equity.stk_name as stk_no1_ from cbotctrad as nego, te.testmb as equity where nego.stk_no = equity.stk_no 
20:59,781 WARN [JDBCExceptionReporter] SQL Error: 933, SQLState: 42000 
20:59,781 ERROR [JDBCExceptionReporter] ORA-00933: SQL command not properly ended 

20:59,797 WARN [JDBCExceptionReporter] SQL Error: 933, SQLState: 42000 
20:59,797 ERROR [JDBCExceptionReporter] ORA-00933: SQL command not properly ended 

20:59,797 ERROR [JDBCExceptionReporter] SQLException occurred 
java.sql.SQLException: ORA-00933: SQL command not properly ended

I have no ideas......

Roberto · **Posted:** Wed Nov 19, 2003 1:55 am

I have tried on the formula attribute.
but it must select all colums otherwise error occurred.

mappings:

Code:

<property name="decodeTradeAmount" formula="decode(bs_type, 'B', -1*td_amt, 'S', td_amt)"/>

error:

Code:

net.sf.hibernate.QueryException: could not resolve property: decodeTradeAmount 
at net.sf.hibernate.persister.AbstractPropertyMapping.toType(AbstractPropertyMapping.java:35)

gavin · **Posted:** Wed Nov 19, 2003 2:05 am

Stick with the createSQLQuery() solution. Thats the Right Way to solve it. But note that createSQLQuery MUST return entities (it can't do projection).

Roberto · **Posted:** Wed Nov 19, 2003 2:24 am

createSQLQuery() seems a little issue about composite-id

gavin · **Posted:** Wed Nov 19, 2003 2:32 am

Ah yes, I understand that this is fixed in CVS.

Max?

max · **Posted:** Sat Nov 22, 2003 3:23 pm

yes ?

What's da' problem ? ;)

johnamos · **Joined:** Tue Oct 12, 2004 10:32 pm **Posts:** 5

In Hibernate 3.1.2, if I put a space between decode and the open parenthesis, like so:

Code:

  <property name="txnTypeDesc"
      type="java.lang.String"
      formula="(DECODE (UPPER (MAINTYP_CD),
                  'A', 'Add',
                  'C', 'Change',
                  'D', 'Delete'
                 ))"
      insert="false"
      update="false"
    />

Hibernate translates it into the following SQL:

Code:

(this_.DECODE (UPPER (this_.MAINTYP_CD),                   'A', 'Add',                   'C', 'Change',                   'D', 'Delete'                  )) as formula10_0_

And it doesn't work because DECODE is not a column, it's a function.

If I remove the space it works as expected.