1. Anlage einer Hilfsview, die für jede empno aus emp die Summe aus sal und komm liefert (das ist inhaltlich natürlich nicht besonders aufregend – und keine View wert)
create or replace view sal_complete as select empno , ename , sal + nvl(comm, 0) sal from emp;
2. dazu formulieren wir eine Query, die uns die Angabe aus sal_complete über eine skalare Subquery liefert
select empno , ename , sal , comm , (select sal from sal_complete s where s.empno = t.empno) sal_comm from emp t; EMPNO ENAME SAL COMM SAL_COMM ---------- ---------- ---------- ---------- ---------- 7369 SMITH 800 800 7499 ALLEN 1600 300 1900 --> ok 7521 WARD 1250 500 1750 --> ok 7566 JONES 2975 2975 7654 MARTIN 1250 1400 2650 --> ok 7698 BLAKE 2850 2850 7782 CLARK 2450 2450 7788 SCOTT 3000 3000 7839 KING 5000 5000 7844 TURNER 1500 0 1500 --> ok 7876 ADAMS 1100 1100 7900 JAMES 950 950 7902 FORD 3000 3000 7934 MILLER 1300 1300 14 Zeilen ausgewählt. -- funktioniert also tadellos
3. anschließend fällt jemandem auf, dass es doch viel hübscher wäre, wenn die Spalte der View einen aussagekräftigeren Namen als "sal" bekäme.
create or replace view sal_complete as select empno , ename , sal + nvl(comm, 0) sal_comm from emp;
4. unsere Query läuft noch immer, liefert aber leider nicht mehr das gewünschte Ergebnis
select empno , ename , sal , comm , (select sal from sal_complete s where s.empno = t.empno) sal_comm from emp t; EMPNO ENAME SAL COMM SAL_COMM ---------- ---------- ---------- ---------- ---------- 7369 SMITH 800 800 7499 ALLEN 1600 300 1600 --> ??? 7521 WARD 1250 500 1250 --> ??? 7566 JONES 2975 2975 7654 MARTIN 1250 1400 1250 --> ??? 7698 BLAKE 2850 2850 7782 CLARK 2450 2450 7788 SCOTT 3000 3000 7839 KING 5000 5000 7844 TURNER 1500 0 1500 --> ??? 7876 ADAMS 1100 1100 7900 JAMES 950 950 7902 FORD 3000 3000 7934 MILLER 1300 1300 14 Zeilen ausgewählt.
5. Nach der Umbenennung von "sal" in "sal_comm" betrifft die Korrelation nur noch die Bedingung der skalaren Subquery während der Ergebniswert aus der äußeren Query stammt – denn nur dort gibt es eine Spalte "sal". Mit einem passenden Alias bekommt man stattdessen eine plausible Fehlermeldung:
select empno , ename , sal , comm , (select s.sal from sal_complete s where s.empno = t.empno) sal_comm from emp t (select s.sal * FEHLER in Zeile 2: ORA-00904: "S"."SAL": invalid identifier
Nachtrag 21.02.2016: Fast acht Jahre später hat mir Martin Berger jetzt die eigentliche Erklärung für das Verhalten geliefert: Oracle wandelt das IN in ein EXISTS um, bei dem die beiden fraglichen Spalten miteinander verglichen werden - wie ein CBO Trace (Event 10053) zeigt.