Hi,

When used the reserved words ROW_NUMBER ,PARTITION and OVER in formula query I am getting the error "ORA-00923: FROM keyword not found where expected".

This is because Hibernate prefixes my formula query and
doesn't seem to recognize the reserved words ROW_NUMBER PARTITION ,OVER , connect , start with etc.

Below is the my formula query.

<property
name="previousProcessDates"
type="java.lang.String"
formula="(select b.CREATE_DATE from (
(select substr(SYS_CONNECT_BY_PATH( CREATE_DATE, ','),2) CREATE_DATE
from ( select
distinct dtb.account_id,
dtb.CREATE_DATE,
count(*) OVER ( partition by account_id ) as cnt,
ROW_NUMBER () OVER ( partition by account_id order by account_id) as seq
from dmd_territory_backfill dtb
where dtb.account_id= account_id
and dtb.status = 'COMPLETE' ) a
where a.seq = a.cnt start with a.seq = 1 connect by prior a.seq+1= a.seq) b)

Hibernate prefixes the above mentioned reserved words with dmdterrito0_. Please see below.

select b.CREATE_DATE from ( (select substr(SYS_CONNECT_BY_PATH( dmdterrito0_.CREATE_DATE, ','),2)
dmdterrito0_.CREATE_DATE
from ( select distinct dtb.account_id, dtb.CREATE_DATE,
count(*) dmdterrito0_.OVER ( dmdterrito0_.partition by dmdterrito0_.account_id ) as dmdterrito0_.cnt,
dmdterrito0_.ROW_NUMBER () dmdterrito0_.OVER ( dmdterrito0_.partition by
dmdterrito0_.account_id
order by dmdterrito0_.account_id) as dmdterrito0_.seq
from dmd_territory_backfill dtb
where dtb.account_id= dmdterrito0_.account_id
and dtb.status = 'COMPLETE' ) dmdterrito0_.a
where a.seq = a.cnt dmdterrito0_.start dmdterrito0_.with a.seq = 1
dmdterrito0_.connect by dmdterrito0_.prior a.seq+1= a.seq) dmdterrito0_.b) )
as formula0_0_ from DMD.DMD_TERRITORY_BACKFILL dmdterrito0_
where dmdterrito0_.TERRITORY_ID in
(select this_.TERRITORY_ID from DMD.DMD_Territory this_ where this_.TERRITORY_ID i( ?) )

Is there any way to tell Hibernate not to do this?

Very greatful for any help on this issue.

Thanks
Kishore

I had this same problem and wrote a stored function to do sql for me and called the stored function from the formula.

I also have this issue. Is there a fix / workaround?

Hi Guys,

I am using the simple formula in hibernate, its not working, i got example from below link and i implemented same maner. but its storing the null value in batabase.

http://www.java2s.com/Code/Java/Hiberna ... tenate.htm

My sampele code :
name is -- 400#name#4001

<property name="name" type="java.lang.String" formula="SELECT SUBSTRING_INDEX(name, '#',1)">
<column name="name" />
</property>

Please can you help me any one to resolve this.

Regards,
Ramamohan.