the key is the error Not unique table/alias: 'SS' the is usually a human error not ofbiz
I can not, however off the top of my head point you to the what is causing it. Jack Liu sent the following on 6/18/2009 8:56 PM: > Thanks for your reply. > I defined every entity member's alias. > > I think maybe OFBiz's entity-engine have something wrong with how to translate > view-entity to sql query under some exceptional condictions. > > > -----Original Message----- > From: BJ Freeman [mailto:[email protected]] > Sent: 2009年6月19日 11:41 > To: [email protected] > Subject: Re: Not unique table/alias > > 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.
