CeDeT: OIF (Oracle is funny (that way)) #2

Scalar subquery as column not recognized by PL/SQL

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):

The test

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