During the development phase , we came across a challenge of using the DECODE function of oracle in a query, which will be processed through hibernate.
Original query looks like this:
SELECT ID, NAME, DECODE(CC.TYPE,'REG',C1.VAL,CC.VAL) as DECCOL FROM CUSTOMER CC, CUSTOMER C1 WHERE
CC.CUSTACCKEY = 1 AND C1.PARENTKEY = CC.KEY AND C1.CUSTACCKEY = CC.CUSTACCKEY
GROUP BY ID, NAME, DECCOL
For Pagination
String count = " COUNT(1) ";
StringBuffer orgQuery = new StringBuffer(query);
orgQuery .delete(start+6,from);
orgQuery .insert(start+6, count);
ret = orgQuery.toString();
Since we need to docodes and group by clause as well with making the query paginated, hibernate was throwing the QueryException. After reading various information on internet, we identified some useful stuffs :
1. In case we want to use DECODE in hibernate , we can define formula in the hbm.xml file, where the expected values would come through the formula. The defined name will be used in the HQL.
< property
name="propertyName"
column="column_name"
type="typename"
update="true|false"
insert="true|false"
formula="arbitrary SQL expression"
access="field|property|ClassName"
lazy="true|false"
unique="true|false"
not-null="true|false"
optimistic-lock="true|false"
/>
2. Use the inline query, to avoid the decoding directly, and use the incoming column name in the group by clause. However with this option, we can not run the query as HQL, instead , we will have to run it as SQL.
SELECT ID, NAME, DECCOL FROM (SELECT ID, NAME, DECODE(CC.TYPE,'REG',C1.VAL,CC.VAL) AS DECCOL FROM CUSTOMER CC, CUSTOMER C1 WHERE
CC.CUSTACCKEY = 1 AND C1.PARENTKEY = CC.KEY AND C1.CUSTACCKEY = CC.CUSTACCKEY)
GROUP BY ID, NAME, DECCOL
3. Not to go for DECODE, in case if you want to make the query DB independent. We can use 'Case When' to avoid the dependency from the query.
SELECT ID, NAME, (CASE WHEN CC.TYPE = 'REG' THEN C1.VAL ELSE CC.VAL END) AS DECCOL FROM CUSTOMER CC, CUSTOMER C1 WHERE
CC.CUSTACCKEY = 1 AND C1.PARENTKEY = CC.KEY AND C1.CUSTACCKEY = CC.CUSTACCKEY)
GROUP BY ID, NAME, DECCOL
We can use any of the above approaches, to make the required DECODE functionality work !
