Never mind I did not read completely BJ Freeman sent the following on 6/18/2009 8:38 PM: > you have not defined what entity the alias SS is associated with. > like you did > <member-entity entity-alias="RC" > entity-name="Regioncountry" /> > > > Jack Liu sent the following on 6/18/2009 8:02 PM: >> Hi, All >> I am confused with how view-entity works. >> I want to do the following query: >> >> select ss.id >> from region r,regioncountry rc, countries c, customerinfo cu, >> subscribedservice ss >> where r.id=rc.regionid and rc.countryid=c.id and c.name=cu.country and >> cu.id=ss.customerid and r.service=ss.service >> and r.id=41 and r.service='Customer' >> >> So I define a view-entity as follows: >> >> <view-entity entity-name="RegionCustomerService" >> package-name="com.aicent.ccb"> >> <member-entity entity-alias="R" entity-name="Region" /> >> <member-entity entity-alias="RC" >> entity-name="Regioncountry" /> >> <member-entity entity-alias="C" entity-name="Countries" >> /> >> <member-entity entity-alias="CU" >> entity-name="Customerinfo" /> >> <member-entity entity-alias="SS" >> entity-name="Subscribedservice" /> >> >> <alias name="id" field="id" entity-alias="R" /> >> <alias name="service" field="service" entity-alias="R" >> /> >> <alias name="ssid" field="id" entity-alias="SS" /> >> >> <view-link entity-alias="R" rel-entity-alias="RC"> >> <key-map field-name="id" >> rel-field-name="regionid" /> >> </view-link> >> <view-link entity-alias="RC" rel-entity-alias="C"> >> <key-map field-name="countryid" >> rel-field-name="id" /> >> </view-link> >> <view-link entity-alias="C" rel-entity-alias="CU"> >> <key-map field-name="name" >> rel-field-name="country" /> >> </view-link> >> <view-link entity-alias="CU" rel-entity-alias="SS" > >> <key-map field-name="id" >> rel-field-name="customerid" /> >> </view-link> >> <view-link entity-alias="R" rel-entity-alias="SS" > >> <key-map field-name="service" >> rel-field-name="service" /> >> </view-link> >> </view-entity> >> >> Then an error occurred: >> >> Failure in findByCondition operation for entity [RegionCustomerService]: >> org.ofbiz.entity.GenericDataSourceException: SQL Exception while >> executing the following:SELECT R.ID, R.SERVICE, SS.ID FROM REGION R >> INNER JOIN REGIONCOUNTRY RC ON R.ID = RC.REGIONID INNER JOIN COUNTRIES C >> ON RC.COUNTRYID = C.ID INNER JOIN CUSTOMERINFO CU ON C.NAME = CU.COUNTRY >> INNER JOIN SUBSCRIBEDSERVICE SS ON CU.ID = SS.CUSTOMERID INNER JOIN >> SUBSCRIBEDSERVICE SS ON R.SERVICE = SS.SERVICE WHERE (R.ID = ? AND >> R.SERVICE = ?) (Not unique table/alias: 'SS'). Rolling back transaction. >> Exception: org.ofbiz.entity.GenericDataSourceException >> Message: SQL Exception while executing the following:SELECT R.ID, >> R.SERVICE, SS.ID FROM REGION R INNER JOIN REGIONCOUNTRY RC ON R.ID = >> RC.REGIONID INNER JOIN COUNTRIES C ON RC.COUNTRYID = C.ID INNER JOIN >> CUSTOMERINFO CU ON C.NAME = CU.COUNTRY INNER JOIN SUBSCRIBEDSERVICE SS >> ON CU.ID = SS.CUSTOMERID INNER JOIN SUBSCRIBEDSERVICE SS ON R.SERVICE = >> SS.SERVICE WHERE (R.ID = ? AND R.SERVICE = ?) (Not unique table/alias: >> 'SS') >> ---- cause >> --------------------------------------------------------------------- >> Exception: com.mysql.jdbc.exceptions.MySQLSyntaxErrorException >> Message: Not unique table/alias: 'SS' >> >> >> >> How should I define the view-entity to avoid the error according to my >> query above? >> thank you all. >> >> >> Best Regards, >> >> Jack Liu >> >> >
-- BJ Freeman http://www.businessesnetwork.com/automation http://bjfreeman.elance.com http://www.linkedin.com/profile?viewProfile=&key=1237480&locale=en_US&trk=tab_pro Systems Integrator.
