Need to avoid commas in your SQL formula properties

dmcmullan · **Joined:** Tue Oct 11, 2005 3:18 pm **Posts:** 16

If your mapping contains a SQL formula property, make sure to write the SQL without the use of commas, otherwise the syntax will become corrupted just after the first comma, and an error will be thrown.

i.e. this formula with joins....

type="long" formula="( select p.facility_key from facility_association fa, pipeline_facility pf, facility p WHERE fa.facility_key = facility_key and pf.pipeline_facility_key = fa.related_facility_key and fa.active_flag = 'Y' and p.facility_key = pf.pipeline_facility_key) "

must be re-written with subselects just to avoid commas...

type="long" formula="( select p.facility_key from facility p where p.facility_key in (select pf.pipeline_facility_key from pipeline_facility pf where pf.pipeline_facility_key in (select fa.related_facility_key from facility_association fa where fa.facility_key = facility_key and fa.active_flag = 'Y')) ) "

BTW... it would be great if a formula could return a mapped class instead of a type... i.e...
class="Facility" formula="( select p.* from...."

sergey · **Posted:** Tue Oct 18, 2005 3:25 pm

Yes, the formula code in H2.1 has lots of limitations, so NH has all of them too.

xor · **Posted:** Wed Oct 19, 2005 2:58 am

dmcmullan, your formula query is also not perfect. If you'd write it using inner joins there will be no commas in it. It would look approximately like this:

Code:

select p.facility_key 
from facility_association fa
  inner join pipeline_facility pf 
    on pf.pipeline_facility_key = fa.related_facility_key
  inner join facility p 
    on p.facility_key = pf.pipeline_facility_key
WHERE 
  fa.facility_key = facility_key 
  and fa.active_flag = 'Y' 

The SQL syntax you've used is a bit outdated.