Hi,

I am using Hibernate with SQL queries for the data retrieval. Now in one case I need to compute the result columns with SQL functions. The SQL statement looks like:

SELECT distinct {obj}.DIOBJNR,
CASE WHEN {obj}.DITOBJNR = 1 THEN 'X' ELSE '' END AS ATTR01,
CASE WHEN {obj}.DITOBJNR = 5 THEN 'X' ELSE '' END AS ATTR02,
CASE WHEN {obj}.DITOBJNR = 6 THEN 'X' ELSE '' END AS ATTR03,
FROM LIVFIL.DISPOP AS {obj}
WHERE {obj}.DIWERART = 5
AND {obj}.DISTAT = 'A'

The goal is to distinct multiple rows in the table into one row with adding columns for each different row.

The table contains data like:

DIOBJNR DITOBJNR
1 1
1 5
1 6
2 5
... ...

The result of the statement should be

DIOBJNR ATTR01 ATTR02 ATTR03
1 X X X
2 X
...

The query is working when used directly with JDBC.

I did specify in the Hibernate class properties for each column: DIOBJNR, ATTR01, ATTR02, ATTR03 but I receive an Hibernate error "Column not found for property ATTR01"

Any ideas or hints how to solve this problem ?

Thanks


Jan

Hi,

I've read the forum/documentation and found a possible way of solving the problem. The idea is to use a formula for compution:

<property name="ATTR01"
type="java.lang.String"
update="false"
insert="false"
formula="case when DITOBJNR = 15 then 'X' else '' end" />

The problem is, that Hibernate is calculation a wrong SQL query wiht this configuration. The SQL query does looks like:

SELECT distinct obj.DIOBJNR,
obj.DIOBJNR as DIOBJNR0_,
obj.case obj.when obj.DITOBJNR = 15 obj.then 'X' obj.else '' obj.end as f0_0_, ...

Where does the "obj.case", "obj.when", "obj.then", "obj.else" and "obj.end" substitutions came from ?

Thanks


Jan

ok, so I just added these extra keyword to net.sf.hibernate.sql.Template.KEYWORDS.

You can patch your local Hibernate to do the same.

Sorry for the stupid question, but how to build Hibernate (not the samples) from the sources ?

cd hibernate-1.2
ant

Now it works !

Because as I understood the formula feature is new, the only error is only when using the Hibernate xDoclet, of course.

Who is maintaining/changing the xDoclet Hibernate integration ?