Oracle : Dynamic View in a Dynamic Join

[abraxas]

Hello

Was trying your product. Installed the Personal Ed. delivered at EDUC2008. Tried to define a Dynamic View and using that in a Dynamic Join.

Here is the problem. Based on the standard Oracle SCOTT Schema:
1. Define a Dynamic View on EMP using all attributes (it does not depend whether using "*" in the select statement or using only the needed fields)
2. Define a Dynamic join with the created View as main Table that joins to Parent DEPT
3. Try to run/display the Dynamic Join => Oracle Error 00907

The Select Statement for the Dynamic Join is:
SELECT *
FROM ((SELECT E.EMPNO, E.ENAME, E.JOB, E.MGR, E.HIREDATE, E.SAL, E.COMM, E.DEPTNO
FROM EMP E) DAI_
LEFT JOIN DEPT J2 ON DAI_.DEPTNO = J2.DEPTNO)

which cannot be run in SQLPlus either.

Configuration:
- Dynamic AI Personal Running in Win2K Virtual Machine
- Connecting to a Oracle 9 Server with Oracle 10 client
- Using the Oracle Database connection (not the OleDB Provider)
- Using the delivered WebServer

Paolo

[admin]

Hi Paolo,

I think we have recreated the issue.

For me, that this happens only when you make a dynamic join using a dynamic view as the first join (main table) but not if I swap so the Dynamic Views is listed as the second view.

This works

Code: Select all

SELECT *
FROM (SCOTT.DEPT DAI_
LEFT JOIN (SELECT S.EMPNO, S.ENAME, S.JOB, S.MGR, S.HIREDATE, S.SAL, S.COMM, S.DEPTNO

FROM SCOTT.EMP S) J2 ON DAI_.DEPTNO = J2.DEPTNO)

But this does not

Code: Select all

SELECT *
FROM ((SELECT S.EMPNO, S.ENAME, S.JOB, S.MGR, S.HIREDATE, S.SAL, S.COMM, S.DEPTNO

FROM SCOTT.EMP S) DAI_
LEFT JOIN SCOTT.DEPT J2 ON DAI_.DEPTNO = J2.DEPTNO)

Thanks for reporting this

Bo

[abraxas]

Thanks for the reply

I must note that your solution does not yield the intended result since you still are doing a "left outer join" from DEPT and there is a DEPT without an EMP.

If of course it would be possible to define a "right outer" then the result would be as expected (as in the not working version with left outer from EMP).

And of course an "inner join" would be also possible here since there is no EMP winthout DEP

Regards, Paolo

[admin]

Hi Paolo,

You are right - but I was not really trying to offer a solution - I just needed to verify that we both saw the same behaviour - I should have been more clear on that.

Best regards

Bo

[Carsten Sørensen]

Hi Paolo,

Tracing the issue that you reported a little further we found that:

SELECT *
FROM (SELECT S.EMPNO, S.ENAME, S.JOB, S.MGR, S.HIREDATE, S.SAL, S.COMM, S.DEPTNO FROM SCOTT.EMP S) DAI_
LEFT JOIN SCOTT.DEPT J2 ON DAI_.DEPTNO = J2.DEPTNO

Works fine with Oracle. I.e. if we do not enclose the FROM selection with a () (as we currently do), then it will work with the left join of a Dynamic AI SQL View directly out of the JOINBUILDER.

We will test on the change to see impact on other DB engines - otherwise we will make it "Oracle specific" - and will include a fix for next release.

Thanks for letting us know!

best regards
Carsten