Oracle DB union two select statements which return multiple rows

join;oracleunion

I am trying to create a union inside a stored procedure which unions two select statements. One of the select statement returns two or more rows. Thus I get the error "exact fetch returns more than requested number of rows", which occurs of course because I have more than 1 row being retunrned from the second select statement.

This is my stored procedure:

DECLARE

timer_before timestamp;
timer_after timestamp;
timer_duration number;

referencelistid number(20);
referencename varchar(20);
startdate timestamp;
enddate timestamp;
creationuser varchar(20);
creationdate timestamp;
changeuser varchar(20);
changedate timestamp;
username varchar(20);
changetime timestamp;

BEGIN
timer_before :=systimestamp;
--DBMS_OUTPUT.put_line('Start at: ' || timer_before);

SELECT 
    REFERENCE_LIST_ID, 
    REFERENCE_NAME, 
    START_DATE, 
    END_DATE, 
    CREATION_USER, 
    CREATION_DATE, 
    CHANGE_USER, 
    CHANGE_DATE, 
    USERNAME, 
    CHANGE_TIME 
into 
    referencelistid, 
    referencename, 
    startdate, 
    enddate, 
    creationuser, 
    creationdate, 
    changeuser, 
    changedate, 
    username, 
    changetime 
from 
    tbrd_reference_lists 
where 
    reference_list_id = 0000
UNION SELECT 
    REFERENCE_LIST_ID, 
    REFERENCE_NAME, 
    START_ DATE, 
    END_DATE, 
    CREATION_USER, 
    CREATION_DATE, 
    CHANGE_USER, 
    CHANGE_DATE, 
    USERNAME, 
    CHANGE_TIME 
from 
    tbrd_reference_lists_h 
where 
    reference_list_id = 0000;

timer_after :=systimestamp;
--DBMS_OUTPUT.put_line('End at: ' || timer_after);

select
  extract(second from  timer_after-timer_before) into timer_duration
from
  dual;

DBMS_OUTPUT.put_line('Delete Duration: (s): ' || timer_duration);
DBMS_OUTPUT.put_line('Delete Duration: (ms) ' || timer_duration*1000);

END;

I tried to search for a solution, and pipeline functions look like an option. Is there anything simpler that I can use to achieve this

Thanks
Pulkit

Best Answer

Code Review

Always define variables types with %TYPE where it makes sense.

This way, when the table changes, you don't have to go back and fix code.

Bulk Collect

When you are receiving multiple rows from a query, you need to BULK COLLECT INTO a collection.

Defining a Record, then a Table Type is one way to define a Collection Type. If you were selecting all columns (eg SELECT *) then TYPE many_rows_t TABLE OF tablename%rowtype would be another way.

DECLARE
  timer_before      TIMESTAMP;
  timer_after       TIMESTAMP;
  timer_duration    NUMBER;

  TYPE one_row_t IS RECORD (
    referencelistid   tbrd_reference_lists.reference_list_id%TYPE -- number(20),
   ,referencename     tbrd_reference_lists.reference_name%TYPE -- varchar(20),
   ,startdate         tbrd_reference_lists.start_date%TYPE -- timestamp,
   ,enddate           tbrd_reference_lists.end_date%TYPE -- timestamp,
   ,creationuser      tbrd_reference_lists.creation_user%TYPE -- varchar(20),
   ,creationdate      tbrd_reference_lists.creation_date%TYPE -- timestamp,
   ,changeuser        tbrd_reference_lists.change_user%TYPE -- varchar(20),
   ,changedate        tbrd_reference_lists.change_date%TYPE -- timestamp,
   ,username          tbrd_reference_lists.username%TYPE -- varchar(20),
   ,changetime        tbrd_reference_lists.change_time%TYPE -- timestamp
  );
  TYPE many_rows_t IS
    TABLE OF one_row_t;

  some_buffer       many_rows_t;
BEGIN
  timer_before   := systimestamp;

--DBMS_OUTPUT.put_line('Start at: ' || timer_before);
  SELECT
    reference_list_id
   ,reference_name
   ,start_date
   ,end_date
   ,creation_user
   ,creation_date
   ,change_user
   ,change_date
   ,username
   ,change_time
  BULK COLLECT INTO
    some_buffer
  FROM tbrd_reference_lists
  WHERE
    reference_list_id   = 0000
  UNION
  SELECT
    reference_list_id
   ,reference_name
   ,start_date
   ,end_date
   ,creation_user
   ,creation_date
   ,change_user
   ,change_date
   ,username
   ,change_time
  FROM tbrd_reference_lists_h
  WHERE
    reference_list_id   = 0000;

  timer_after    := systimestamp;

--DBMS_OUTPUT.put_line('End at: ' || timer_after);
  SELECT
    EXTRACT( SECOND FROM timer_after - timer_before )
  INTO
    timer_duration
  FROM dual;

  dbms_output.put_line( 'Delete Duration: (s): ' || timer_duration );
  dbms_output.put_line( 'Delete Duration: (ms) ' || timer_duration * 1000 );
END;

Limiting memory consumption

The above example is OK if you are receiving a few rows. But, if you are receiving a few Million rows, you may run into memory problems.

Instead, you'll want to LIMIT how many rows you fetch at one time.

To do this, you need to use a CURSOR and FETCH a limited number of rows at a time. Please take the time to read this Oracle Magazine article and the Oracle Documentation.

DECLARE
  timer_before      TIMESTAMP;
  timer_after       TIMESTAMP;
  timer_duration    NUMBER;

  CURSOR C IS 
    SELECT
      reference_list_id
     ,reference_name
     ,start_date
     ,end_date
     ,creation_user
     ,creation_date
     ,change_user
     ,change_date
     ,username
     ,change_time
    FROM tbrd_reference_lists
    WHERE
      reference_list_id   = 0000
    UNION
    SELECT
      reference_list_id
     ,reference_name
     ,start_date
     ,end_date
     ,creation_user
     ,creation_date
     ,change_user
     ,change_date
     ,username
     ,change_time
    FROM tbrd_reference_lists_h
    WHERE
      reference_list_id   = 0000;    

  TYPE many_rows_t is table of C%rowtype;
  some_buffer       many_rows_t;
BEGIN
  timer_before   := systimestamp;

  OPEN C;

  loop
    FETCH C bulk collect into some_buffer limit 100;

    /* process data */

    exit when C%notfound;
  end loop;

  timer_after    := systimestamp;

--DBMS_OUTPUT.put_line('End at: ' || timer_after);
  SELECT
    EXTRACT( SECOND FROM timer_after - timer_before )
  INTO
    timer_duration
  FROM dual;

  dbms_output.put_line( 'Delete Duration: (s): ' || timer_duration );
  dbms_output.put_line( 'Delete Duration: (ms) ' || timer_duration * 1000 );
EXCEPTION
  -- Prevent cursor leakage
  when others then
    if C%isopen then
      close c;
    end if;

    raise;
END;

STORED PROCEDURE

DELIMITER $$
DROP PROCEDURE IF EXISTS GetDataFromData $$
CREATE PROCEDURE GetDataFromData (GivenData VARCHAR(255))
ThisStoredProcedure:BEGIN

    DECLARE rv,q,queue,queue_children VARCHAR(1024);
    DECLARE queue_length,front_id,pos INT;

    SET rv = '';

    DROP TABLE IF EXISTS DataFromData;
    CREATE TEMPORARY TABLE DataFromData LIKE mytable;

    SELECT COUNT(1) INTO @FoundCount FROM mytable WHERE data = GivenData;

    IF @FoundCount = 1 THEN
        SELECT id INTO @FoundID FROM mytable WHERE data = GivenData;

        SET rv = @FoundID;
        SET queue = @FoundID;
        SET queue_length = 1;

        WHILE queue_length > 0 DO
            SET front_id = FORMAT(queue,0);
            IF queue_length = 1 THEN
                SET queue = '';
            ELSE
                SET pos = LOCATE(',',queue) + 1;
                SET q = SUBSTR(queue,pos);
                SET queue = q;
            END IF;
            SET queue_length = queue_length - 1;

            SELECT IFNULL(qc,'') INTO queue_children FROM
            (SELECT GROUP_CONCAT(otherid) qc FROM mytable WHERE id = front_id) A;

            IF LENGTH(queue_children) = 0 THEN
                IF LENGTH(queue) = 0 THEN
                    SET queue_length = 0;
                END IF;
            ELSE
                IF LENGTH(rv) = 0 THEN
                    SET rv = queue_children;
                ELSE
                    SET rv = CONCAT(rv,',',queue_children);
                END IF;
                IF LENGTH(queue) = 0 THEN
                    SET queue = queue_children;
                ELSE
                    SET queue = CONCAT(queue,',',queue_children);
                END IF;
                SET queue_length = LENGTH(queue) - LENGTH(REPLACE(queue,',','')) + 1;
            END IF;
        END WHILE;

        SET @sql = CONCAT('INSERT INTO DataFromData SELECT * FROM mytable WHERE id in (',rv,')');
        PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;
    END IF;

    SELECT * FROM DataFromData;

END $$
DELIMITER ;

SAMPLE DATA FROM YOUR QUESTION

DROP DATABASE IF EXISTS magistermundus;
CREATE DATABASE magistermundus;
USE magistermundus
CREATE TABLE IF NOT EXISTS `mytable` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `data` VARCHAR(255),
    `otherid` int(11),
    PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
INSERT INTO mytable (data,otherid) VALUES ('stuff',2);
INSERT INTO mytable (data,otherid) VALUES ('x',4);
INSERT INTO mytable (data,otherid) VALUES ('y',NULL);
INSERT INTO mytable (data,otherid) VALUES ('z',NULL);
SELECT * FROM mytable;

SAMPLE DATA FROM YOUR QUESTION LOADED

mysql> DROP DATABASE IF EXISTS magistermundus;
Query OK, 1 row affected (0.03 sec)

mysql> CREATE DATABASE magistermundus;
Query OK, 1 row affected (0.01 sec)

mysql> USE magistermundus
Database changed
mysql> CREATE TABLE IF NOT EXISTS `mytable` (
    ->     `id` int(11) NOT NULL AUTO_INCREMENT,
    ->     `data` VARCHAR(255),
    ->     `otherid` int(11),
    ->     PRIMARY KEY (id)
    -> ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.03 sec)

mysql> INSERT INTO mytable (data,otherid) VALUES ('stuff',2);
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO mytable (data,otherid) VALUES ('x',4);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO mytable (data,otherid) VALUES ('y',NULL);
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO mytable (data,otherid) VALUES ('z',NULL);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM mytable;
+----+-------+---------+
| id | data  | otherid |
+----+-------+---------+
|  1 | stuff |       2 |
|  2 | x     |       4 |
|  3 | y     |    NULL |
|  4 | z     |    NULL |
+----+-------+---------+
4 rows in set (0.00 sec)

mysql>

STORED PROCEDURE EXECUTED

mysql> CALL GetDataFromData('stuff');
+----+-------+---------+
| id | data  | otherid |
+----+-------+---------+
|  1 | stuff |       2 |
|  2 | x     |       4 |
|  4 | z     |    NULL |
+----+-------+---------+
3 rows in set (0.02 sec)

Query OK, 0 rows affected (0.02 sec)

mysql>

CAVEAT # 1

Writing this using UNION SELECT was not practical since the depth of the tree of data is not known. This code will handle any depth of data.

CAVEAT # 2

This code does not handle circular references in data (Where (id,otherid) and (otherid,id) coexist in this table).

Mysql – SELECT UNION from multiple tables with same schema in MySQL

Yes, there is a less redundant/verbose way -- PARTITION a single table by year. Then a single SELECT will do the job.

But, that may not even be wise. What queries benefit from your splitting it into multiple tables?