Hi,

I have a problem with a HQL where-clause.
When Hibernate execute this the generated SQL is wrong

Here the HQL form debug-output:
SELECT
count(*)
FROM
de.lido.vu.model.Angebotkopf
WHERE
JJJJMM BETWEEN 200801 and 200801
and LFDNR BETWEEN 0 and 99999
and SPLITT BETWEEN 0 and 99
and WMTYP BETWEEN ' ' and 'ZZZZ'
and FARBEN BETWEEN ' ' and 'ZZ'
and FACHB BETWEEN ' ' and 'ZZ'
and nvl(substr(FACHB,3,1),'')||' ' BETWEEN ' ' and 'Z '
and SCHAFT BETWEEN '-' and 'ZZ'
and MNB BETWEEN 100 and 600
and anwendung BETWEEN '00' and '0999'

The intersting code is marked bold.


And here the SQL that Hibernate generated

SELECT
count(*) as col_0_0_
FROM
oralp2.DS_ANGKOPF angebotkop0_
WHERE
(
JJJJMM BETWEEN 200801
and 200801
)
and
(
LFDNR BETWEEN 0 and 99999
)
and
(
SPLITT BETWEEN 0 and 99
)
and
(
WMTYP BETWEEN ' ' and 'ZZZZ'
)
and
(
FARBEN BETWEEN ' ' and 'ZZ'
)
and
(
FACHB BETWEEN ' ' and 'ZZ'
)
and
(
(
nvl(substr(FACHB, 3, 1)||'')||' '
)
BETWEEN ' ' and 'Z '
)
and
(
SCHAFT BETWEEN '-' and 'ZZ'
)
and
(
MNB BETWEEN 100 and 600
)
and
(
angebotkop0_.anwendung BETWEEN '00' and '0999'
)

The interesting code which is wrong is also markd bold.
My question is where comes the first ||? In the HQL this was an comma.
Why ist Hibernate change this to ||.
I have to do such an beast because I have to support Oracle and PostgreSQL. Both databases differ in the result of a blank substr.
Have anyone and idea.

Thank you

Regards

Wolfgang