Ora-00918 Column Ambigously defined Error
When creaing an ORM Class that references the same associate class twice, the SQL statement ends up with an ambigous column.
Please consider the following Class Definition
[Entity]
[Table('CUSTOMER')]
[Sequence('Select GetSid() as Sid from dual')]
TCustomer = class(TModelBase)
private
[Column('SID',[cpRequired,cpNotNull,cpPrimaryKey],19,0)]
FSid: Int64;
[Column('TAX_AREA_SID',[],19,0)]
FTaxAreaSid: Nullable<Int64>;
[Column('TAX_AREA2_SID',[],19,0)]
FTaxArea2Sid: Nullable<Int64>;
[Spring.Persistence.Mapping.Attributes.Transient]
[ManyToOne(false, [ckCascadeAll], 'FTaxAreaSid')]
FTaxArea: TTaxAreaAssociate;
[Spring.Persistence.Mapping.Attributes.Transient]
[ManyToOne(false, [ckCascadeAll], 'FTaxArea2Sid')]
FTaxArea2: TTaxAreaAssociate;
function TCustomer.GetTaxArea: TNullableString;
begin
Result := nil;
if FTaxArea <> nil then
Result := FTaxArea.Name;
end;
function TCustomer.GetTaxArea2: TNullableString;
begin
Result := nil;
if FTaxArea2 <> nil then
Result := FTaxArea2.Name;
end;
public
constructor Create;override;
destructor Destroy; override;
property Sid: Int64 read FSid write SetSid;
property TaxAreaSid: Nullable<Int64> read FTaxAreaSid write SetTaxAreaSid;
property TaxArea2Sid: Nullable<Int64> read FTaxArea2Sid write SetTaxArea2Sid;
property TaxArea: TNullableString read GetTaxArea write SetTaxArea;
property TaxArea2: TNullableString read GetTaxArea2 write SetTaxArea2;
end;
Notice that the same associate class TTaxAreaAssociate is referenced twice because there are actually two tax_area_sid fields in the customer record.
When the SQL Statement is generated though it does not treat these as two separate instances of the Tax_Area table but only one and therefore gives the same alias for both.
Here is the SQL that is generated
SELECT t2."SID",
t2."TAX_AREA_SID",
t2."TAX_AREA2_SID",
t1."SID" AS t1$SID,
t1."TAX_AREA_NAME" AS t1$TAX_AREA_NAME,
t1."SID" AS t1$SID,
t1."TAX_AREA_NAME" AS t1$TAX_AREA_NAME
FROM CUSTOMER t2
LEFT OUTER JOIN TAX_AREA t1
ON t1."SID" = t2."TAX_AREA_SID"
LEFT OUTER JOIN TAX_AREA t1
ON t1."SID" = t2."TAX_AREA2_SID"
Comments (8)
-
reporter -
repo owner That is not the only code that is causing the issue (remember that this does not even work on Oracle because it does not accept the AS for a table alias?)
But what is correct is that TSQLAliasGenerator causing the problem. But the problem here originates from the TManyToOneRelation.BuildColumnName method. The alias needs to be generated for each TSQLTable instance. Unfortunately the code for many-to-one relations need to be refactored to allow that.
-
reporter Yes correct it is the TOracleSQLGenerators version of this method without the as keyword that I should have referenced.
-
reporter Is this on the radar screen? This is a showstopper for us. If not then I will need to come up with a solution myself. I would appreciate if you could just let me know if it is being worked on? Thanks,
-
repo owner - changed status to open
Yes, but not easy to fix - there is some issue in the architecture for many-to-one relations. Working on it in the session-refactoring branch.
-
repo owner - changed status to resolved
-
reporter Thanks so much for this. I am integrating your changes now.
A couple if API issues. We use GetMemberValueDeep and GetClassMembers from the now defunct TRttiExplorer class.You have replaced these with two internal functions in the EntityCache.TEntityData class with:
GetMemberValueDeep - an Embedded Function GetAttributes a private Class Function
Would it be possible to make both of these public off of the TEntityData class. I have moved GetAttributes to public section and unembedded the GetMemberValueDeep function and made it public for now so that we can use these functions without duplicating the code.
Thanks again for all your hard work,
Todd.
--
-
repo owner No, they are private for exactly this reason: preventing anyone from using them - they are implementation detail and not public API. We can change them in the future without worrying about breaking anyone's code.
- Log in to comment
Seems like this is the issue.
The TSqlAliasGenerator is just generating one alias per table.name and since the table name of course is the same for these two seperate joins then it returns the same one.
Isn't SQL Generation Fun!