Hi,
I have a fairly complex sql query containing a subquery to be used in a formula:
Code:
@Formula("( select subqry.rank from ("+
"SELECT ph1.PH_ID,ph1.PH_PROTOCOL_ID,"+
"DENSE_RANK () OVER (PARTITION BY ph1.ph_protocol_id
ORDER BY ph1.ph_history_date asc ) rank"+
" FROM pm_protocol_history ph1)subqry "+
"where subqry.ph_id=PH_ID )")
int versionNumber;
This does not work as the sql generated is:
Code:
( select subqry.rank from (SELECT ph1.PH_ID,ph1.PH_PROTOCOL_ID,protocolhi0_.DENSE_RANK () protocolhi0_.OVER (protocolhi0_.PARTITION BY ph1.ph_protocol_id ORDER BY ph1.ph_history_date asc ) protocolhi0_.rank FROM pm_protocol_history ph1)protocolhi0_.subqry where subqry.ph_id=protocolhi0_.PH_ID ) as formula0_1_
Ok my dialect (oracle9) doesn't know about rank, partition and over no big deal I added it.
However after this I still get an incorrect query were not know column are prepended by hibernate with the main entity name. Kind of expected but really not the behaviour I want.
I ended up doing this:
in my dialect:
Code:
for (int i=0;i<10;i++)
registerFunction( "priv_ns_"+i, new StandardSQLFunction("priv_ns_"+i));
and in my entity mapping using 'priv_ns_[0-9]' as aliasing names:
Code:
@Formula("( select priv_ns_1.priv_ns_2 from ("+
"SELECT ph1.PH_ID,ph1.PH_PROTOCOL_ID,"+
"DENSE_RANK () OVER (PARTITION BY ph1.ph_protocol_id ORDER BY ph1.ph_history_date asc ) priv_ns_2"+
" FROM pm_protocol_history ph1)priv_ns_1 "+
"where priv_ns_1.ph_id=PH_ID )")
int versionNumber;
Basically reserving names in the dialect. I know this is a HACK, however I was wondering if there were a better way of doing this, or if subquery aliasing will be supported anytime, well at least for formulas.
Thanks in advance.
Login
FAQ
Search
