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.
