CeDeT: OIF (Oracle is funny (that way)) #2
The use of a scalar subquery as a column is not recognized by PL/SQL. You may choose to use a subquery as part of the FROM-clause (to define a table) instead. This is demonstrated below using SQL*Plus (notice the different output from two SELECTs):
SQL> SELECT * FROM v$version WHERE ROWNUM<=2;
BANNER
----------------------------------------------------------------
Oracle8i Enterprise Edition Release 8.1.7.2.0 - Production
PL/SQL Release 8.1.7.2.0 - Production
SQL> CREATE TABLE master_table(
2 id NUMBER NOT NULL,
3 description VARCHAR2(255) NOT NULL,
4 CONSTRAINT master_table_pk PRIMARY KEY(id)
5 );
Tabel er oprettet.
SQL> CREATE TABLE detail_table(
2 id NUMBER NOT NULL,
3 no NUMBER NOT NULL,
4 details VARCHAR2(255) NOT NULL,
5 CONSTRAINT detail_table_pk PRIMARY KEY(id,no),
6 CONSTRAINT detail_table_fk FOREIGN KEY(id) REFERENCES master_table(id)
7 );
Tabel er oprettet.
SQL> INSERT INTO master_table(id,description) VALUES(1,'Master Record 1');
1 række er oprettet.
SQL> INSERT INTO master_table(id,description) VALUES(2,'Master Record 2');
1 række er oprettet.
SQL> INSERT INTO master_table(id,description) VALUES(3,'Master Record 3');
1 række er oprettet.
SQL> INSERT INTO detail_table(id,no,details) VALUES(1,1,'Detail Record 1,1');
1 række er oprettet.
SQL> INSERT INTO detail_table(id,no,details) VALUES(1,2,'Detail Record 1,2');
1 række er oprettet.
SQL> INSERT INTO detail_table(id,no,details) VALUES(1,3,'Detail Record 1,3');
1 række er oprettet.
SQL> INSERT INTO detail_table(id,no,details) VALUES(1,4,'Detail Record 1,4');
1 række er oprettet.
SQL> INSERT INTO detail_table(id,no,details) VALUES(2,1,'Detail Record 2,1');
1 række er oprettet.
SQL> COMMIT;
Bekræftelse er udført.
SQL> COLUMN description FORMAT a20;
SQL> SELECT mt.*,dt.cnt
2 FROM (SELECT id,COUNT(*) cnt
3 FROM detail_table
4 GROUP BY id) dt,
5 master_table mt
6 WHERE dt.id=mt.id
7 ORDER BY mt.description;
ID DESCRIPTION CNT
---------- -------------------- ----------
1 Master Record 1 4
2 Master Record 2 1
SQL> SELECT mt.*,(SELECT COUNT(*)
2 FROM detail_table dt
3 WHERE dt.id=mt.id) cnt
4 FROM master_table mt
5 ORDER BY mt.description;
ID DESCRIPTION CNT
---------- -------------------- ----------
1 Master Record 1 4
2 Master Record 2 1
3 Master Record 3 0
SQL> SET SERVEROUTPUT ON SIZE 20000;
SQL> DECLARE -- start anonymous PL/SQL block
2 CURSOR mt_cur IS
3 SELECT mt.*,dt.cnt
4 FROM (SELECT id,COUNT(*) cnt
5 FROM detail_table
6 GROUP BY id) dt,
7 master_table mt
8 WHERE dt.id=mt.id
9 ORDER BY mt.description;
10 mt_rec mt_cur%ROWTYPE;
11 BEGIN
12 OPEN mt_cur;
13 LOOP
14 FETCH mt_cur INTO mt_rec;
15 EXIT WHEN mt_cur%NOTFOUND;
16 DBMS_OUTPUT.PUT_LINE('ID: '||mt_rec.id||' - DESCRIPTION: '||mt_rec.description||' - COUNT: '||mt_rec.cnt);
17 END LOOP;
18 CLOSE mt_cur;
19 END;
20 /
ID: 1 - DESCRIPTION: Master Record 1 - COUNT: 4
ID: 2 - DESCRIPTION: Master Record 2 - COUNT: 1
PL/SQL-procedure er udført.
SQL> DECLARE -- start anonymous PL/SQL block
2 CURSOR mt_cur IS
3 SELECT mt.*,(SELECT COUNT(*)
4 FROM detail_table dt
5 WHERE dt.id=mt.id) cnt
6 FROM master_table mt
7 ORDER BY mt.description;
8 mt_rec mt_cur%ROWTYPE;
9 BEGIN
10 OPEN mt_cur;
11 LOOP
12 FETCH mt_cur INTO mt_rec;
13 EXIT WHEN mt_cur%NOTFOUND;
14 DBMS_OUTPUT.PUT_LINE('ID: '||mt_rec.id||' - DESCRIPTION: '||mt_rec.description||' - COUNT: '||mt_rec.cnt);
15 END LOOP;
16 CLOSE mt_cur;
17 END;
18 /
SELECT mt.*,(SELECT COUNT(*)
*
FEJL i linie 3:
ORA-06550: line 3, column 18:
PLS-00103: Encountered the symbol "SELECT" when expecting one of the following:
( - + mod not null others <an identifier>
<a double-quoted delimited-identifier> <a bind variable> avg
count current exists max min prior sql stddev sum variance
execute forall time timestamp interval date
<a string literal with character set specification>
<a number> <a single-quoted SQL string>
ORA-06550: line 5, column 38:
PLS-00103: Encountered the symbol "CNT" when expecting one of the following:
; return returning and or