Master-Detail and aliased field names => ora-00904

[waeberd]

Dear Crlab-Team,

I have a problem with ODAC-Master-Detail linking and I hope you can help me.

Here's how to hit that problem:

1) Open the "MasterDetail" Demo in the ODAC win32 demos
2) Change quMaster like this:
SELECT d.DeptNo as myAliasDeptNo, D.*,RowId FROM Dept D
3) Change quDetail like this:
SELECT e.DeptNo as myAliasDeptNo, E.*,RowId
FROM Emp E
4) Select quDetail in the Objectinspector and edit its MASTERFIELDS property
5) => in the Dialog link the two "myAliasDeptNo" and save the whole thing

Now if I run the demo, I get an "ora-00904 invalid column name".


The problem is that ODAC is building a dynamic WHERE clause with the
aliased field names, which leads to an invalid sql-statement.

Of course in this demo, the alias-stuff is not needed at all, but there
are situations, where aliasing field names is essential.

Please find the modified MasterDetail-project here:
http://www.waeberd.ch/files/MasterDetaildw.zip

Any hint is very welcome, thanks a lot!

Best regards,

Daniel

[Challenger]

You should either specify actual names of the fields in the DetailFields property or use local Master/Detail relationship.

[waeberd]

Hi,

Thanks for your quick answer!

You should either specify actual names of the fields in the DetailFields property ...

This doesn't work for prefixed fieldnames like "d.DeptNo". ODAC wants to build a parameter using this name and the "." makes this parameter name invalid. In my case, prefixing the field name is necessary to avoid ambiguously named fields.

... or use local Master/Detail relationship.

you mean resolve the master-detail relationship by putting everything into one single query? Would be a workaround.

Regards, Daniel

[Challenger]

Such code works without any problems:

Code: Select all

  quDetail.DetailFields := 'e.DeptNo';
  quDetail.MasterFields := 'myAliasDeptNo';

Or am I missing something?
Another approach is to set the MasterSource property of the Detail dataset, clear the MasterFields and DetailFields properties and set SQL in the following way:

Code: Select all

SELECT e.DeptNo as myAliasDeptNo, E.*,RowId FROM Emp E 
where e.DeptNo = :myAliasDeptNo

For information about local Master/Detail please refer to ODAC help (TOraDataSet.Options.LocalMasterDetail property).

[waeberd]

Thank you, your solutions work fine! The second solution also works in the FastReport wrapper of the ODAC-queries, where the problem really came from.

My problem was that I was trying to link the fields only using the property editor, which has less possibilities!

EDIT:
as I am still using ODAC 5.50, the LocalMaterDetail option does not exist yet. Time for upgrading I guess

Problem should be solved, otherwise I will come back!

Thanks again!

Daniel