QBE column ambiguously defined

[rguillen]

Hi,
I have the following query:
SELECT E.ROWID, E.J_LOCATION_ID, E.J_EVENT_ID, E.J_EVENT_DATE, T.J_EVENTTYPES_NUMBER, E.J_EVENT_MESSAGE, E.J_EVENT_COMMENT, E.J_EVENTSTATUS_ID, S.J_EVENTSTATUS_NUMBER
FROM TSYS.DD_F_EVENTS E, TSYS.DD_L_EVENTTYPES T, TSYS.DD_L_EVENTSTATUS S
WHERE (E.J_EVENTTYPES_ID = T.J_EVENTTYPES_ID) AND (E.J_EVENTSTATUS_ID = S.J_EVENTSTATUS_ID)
ORDER BY E.J_EVEN_DATE DESC

When I tried to define the QBE on Delphi it trow me the error: ORA-00918: column ambiguously defined.
CDAdvisorEvents.QBEDefinition.FieldByName('J_EVENTSTATUS_ID').Value:= ' 5';

I know that table E and table S have the same column J_EVENTSTATUS_ID but in select I'm qualification and refer to table e.

If I try to qualify in QBE definition it trow me an Exception.
Am I doing anything wrong?
Do you have any idea how to avoid this error?

I'm using RAD Studio XE4 Enterprise, DOA 4.1.3.5 with Oracle 11g R2

Thanks
Best Regards.

 

[Marco Kalter]

From the User's Guide:

Ambiguous column name when using joins
If a where clause is generated for a join select statement, this may result in ambiguous column names. Consider the following statement:

select empno, ename, deptno, dname
from emp, dept
where dept.deptno = emp.deptno
order by ename

When the user enters a value in the deptno field during QBE mode, the SQL statement will be modified as follows when it is executed:

select empno, ename, deptno from emp
where (dept.deptno = emp.deptno)
and deptno = 10
order by ename

The generated deptno column name in the where clause is ambiguous, as it is not clear if it belongs to the emp or dept table (both contain a deptno column). This will lead to an "ORA-00914: column ambiguously defined" error message. To resolve this, you can set the Origin property of the deptno field to 'emp.deptno'. This Origin property will be used during QBE to name the column in the where clause. To set the Origin field property at design-time, you must make the fields persistent. You can also set the Origin property at run-time to avoid persistent fields.

 

[rguillen]

Hi Marco,
I have fully qualified the query to:
SELECT TSYS.DD_F_EVENTS.ROWID, TSYS.DD_F_EVENTS.J_LOCATION_ID, TSYS.DD_F_EVENTS.J_EVENT_ID,
TSYS.DD_F_EVENTS.J_EVENT_DATE, TSYS.DD_L_EVENTTYPES.J_EVENTTYPES_NUMBER,
TSYS.DD_F_EVENTS.J_EVENT_MESSAGE, TSYS.DD_F_EVENTS.J_EVENT_COMMENT,
TSYS.DD_F_EVENTS.J_EVENTSTATUS_ID, TSYS.DD_L_EVENTSTATUS.J_EVENTSTATUS_NUMBER
FROM TSYS.DD_F_EVENTS, TSYS.DD_L_EVENTTYPES, TSYS.DD_L_EVENTSTATUS
WHERE (TSYS.DD_F_EVENTS.J_EVENTTYPES_ID = TSYS.DD_L_EVENTTYPES.J_EVENTTYPES_ID) AND
(TSYS.DD_F_EVENTS.J_EVENTSTATUS_ID = TSYS.DD_L_EVENTSTATUS.J_EVENTSTATUS_ID)
ORDER BY TSYS.DD_F_EVENTS.J_EVENT_DATE;

But I still having the same error.

CDAdvisorEvents.QBEMode:= True;
CDAdvisorEvents.QBEDefinition.FieldByName('J_LOCATION_ID').Value:= IntToStr(Location);

The error ocurred here CDAdvisorEvents.QBEDefinition.FieldByName('J_EVENTSTATUS_ID').Value:= ' 5';

and if I put the table name like this:
CDAdvisorEvents.QBEDefinition.FieldByName('TSYS.DD_F_EVENTS.J_EVENTSTATUS_ID').Value:= ' 5';

it give me a exception.

Where is my error?

Thanks Marco.

 

[Marco Kalter]

Did you set the TField.Origin property?