OFBiz › OFBiz - User

Not unique table/alias

_‹ Previous Topic Next Topic _›

Classic

List

Threaded

5 messages Options

Jack Liu-2

Not unique table/alias

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

Re: Not unique table/alias

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.

BJ Freeman

Re: Not unique table/alias

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.

Jack Liu-2

RE: Not unique table/alias

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:

--
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.

BJ Freeman

Re: Not unique table/alias

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.