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 |
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. |
In reply to this post by Jack Liu-2
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. |
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:[hidden email]] Sent: 2009年6月19日 11:41 To: [hidden email] 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. |
In reply to this post by BJ Freeman
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:[hidden email]] > Sent: 2009年6月19日 11:41 > To: [hidden email] > 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. |
Free forum by Nabble | Edit this page |