Re: SELECT works in SQL, but fails in PL/SQL

From: Chrysalis <cellis_at_iol.ie>
Date: 1997/03/30
Message-ID: <333EB33A.3EA@iol.ie>#1/1

Ben Ryan wrote:
>
> A SELECT statement which works fine in SQL is failing in PL/SQL.
> The SELECT statement uses sub-queries in the FROM clause
> (which means it is 7.3 specific?).
>
> A minimal SQL script, showing the problem, appears below. It was
> run on RDBMS 7.3.2.1.0 on SCO OS5.0.2. The stored
> procedure gives a runtime error of ORA-01410: Invalid ROWID.
>
> Assuming the bug is in the RDBMS 7.3.2, can anyone suggest a
> work-around SELECT statement. My best shot so far is to break
> it into two SELECT statements and join the results using PL/SQL
> logic. Can anyone do it in one SELECT statement?
>
> TIA
>
> -------
>
> DROP TABLE details;
> DROP TABLE master;
> CREATE TABLE master (pk NUMBER NOT NULL,
> group_val CHAR(1)
> CONSTRAINT check_group_val CHECK (group_val IN ('A','B'))
> );
>
> CREATE TABLE details (pk NUMBER NOT NULL,
> master_fk NUMBER);
>
> INSERT INTO master VALUES(1,'A');
> INSERT INTO details VALUES(101,1);
>
> INSERT INTO master VALUES (2,'B');
> INSERT INTO details VALUES(201,1);
> INSERT INTO details VALUES(202,2);
>
> INSERT INTO master VALUES (3,'A');
> COMMIT;
>
> SELECT a.group_val,a.totalcnt,b.hasdetails_cnt
> FROM (SELECT group_val,COUNT(*) totalcnt
> FROM master
> GROUP BY group_val) a,
> (SELECT group_val,COUNT(*) hasdetails_cnt
> FROM master m
> WHERE EXISTS (SELECT NULL FROM details d WHERE d.master_fk =
> m.pk)
> GROUP BY group_val) b
> WHERE a.group_val = b.group_val (+);
>
> CREATE OR REPLACE PROCEDURE junk
> AS
> CURSOR c_junk IS
> SELECT a.group_val,a.totalcnt,b.hasdetails_cnt
> FROM (SELECT group_val,COUNT(*) totalcnt
> FROM master
> GROUP BY group_val) a,
> (SELECT group_val,COUNT(*) hasdetails_cnt
> FROM master m
> WHERE EXISTS (SELECT NULL FROM details d WHERE
> d.master_fk=m.pk)
> GROUP BY group_val) b
> WHERE a.group_val = b.group_val (+);
> ignore c_junk%ROWTYPE;
> BEGIN
> FOR r IN c_junk LOOP
> ignore := r;
> END LOOP;
> END;
> /
> show errors
> EXECUTE junk;
>
> Ben Ryan - VanCity C.U.
> Vancouver, B.C.
> Canada

Ben,
The simplest solution seems to be to create two views:

create view a as

	SELECT group_val,COUNT(*) totalcnt
     FROM  master

GROUP BY group_val;

create view b as

	SELECT group_val,COUNT(*) hasdetails_cnt
     FROM  master m
    WHERE EXISTS
    	(SELECT NULL FROM details d
    	 WHERE  d.master_fk=m.pk)

GROUP BY group_val;

The query (which may be executed from anywhere, including PL/SQL) is then,
simply:

SELECT a.group_val,a.totalcnt,b.hasdetails_cnt FROM a, b
WHERE a.group_val = b.group_val(+);

A more efficient solution, however, might be to maintain an extra column (has_details) in the MASTER table. This could be simply done with a trigger.

The query then becomes:

SELECT group_val, count(*) totalcnt

,sum(decode(has_details,'Y',1,null)) has_details FROM master
GROUP BY group_val;

If maintaining a (theoretically-)redundant column does not appeal, it would be possible to create a function to return 1 or 0 (or null) according as the master has or has not corresponding details and sum this in place of the decode function above.

Thus,

create or replace function has_details (fk number)

      return number as
      cursor C is select 1 from details where master_fk = fk;
      dummy number := 0;
   begin
      open C; fetch C into dummy; close C;
      return dummy;

end;

Now,
SELECT group_val, count(*) totalcnt

,sum(has_details(pk)) has_details
FROM master
GROUP BY group_val;

Both the above solutions require only one pass of the MASTERS table instead of two.

Hope this helps.

Chrysalis. Received on Sun Mar 30 1997 - 00:00:00 CST

Re: SELECT works in SQL, but fails in PL/SQL - 7.3 specific