Oracle 11g: How To Select 'Extra' Rows in SQL Query

0
votes

Using Oracle 11g, and working in an engineering document context, I need to select a collection of rows that include 'extra' rows for prior revs and multiple document sheets.

Specifically, our database has a record for only the latest revision of each engineering document. We need a row returned for each revision and each sheet of these records.

We have data like this:

DRAW_NUM        REV NUM_SHTS
LD-111-639-01   2   3

We Need Data Like This:

DRAW_NUM        REV SHT
LD-111-639-01   1   1
LD-111-639-01   1   2
LD-111-639-01   1   3
LD-111-639-01   2   1
LD-111-639-01   2   2
LD-111-639-01   2   3

My latest SQLPlus query attempt uses an external table to list numbers from 0 to Rev and 0 to Num_Shts, and the code looks like this:

SELECT DISTINCT prefix || '-' || lpad(series,3,0) || '-' || lpad(base,3,0) || '-' || lpad(suffix,2,0) Draw_Num,
(select n from numbers where n >=0 and n <= md_draw.rev) Rv,
(select n from numbers where n >=0 and n <= md_draw.num_shts) Sht
FROM md_draw WHERE Dwg_Date <= TO_DATE('1-JUN-02')
ORDER BY Draw_Num, Rv, Sht
/

But produces the following error:

ORA-01427: single-row subquery returns more than one row

Is there an easy way to do this?

3
sqloracle
Do you need to add rows to the table, or just to "select" them? select does not insert more rows into your base table. Then, in your explanation there is no mention of dates of any kind, but in your query attempt there is a date, what's the story with that? Please update your post to state ALL the requirements upfront, don't add them one by one. What else needs to be in the new rows? It seems pretty pointless to just add "shells" for revisions if you have no info for them. - mathguy
Thx @mathguy. Our table lists only the latest revision of each document. My requirement is to produce a listing of all revs and all sheets of each document in order to process and retire our historical aperture card collection, which includes a card for each revision and each sheet. I'm not altering the table, just producing a listing, and only last modified date of the latest revision is required. - Barry Prentiss

3 Answers

1
votes

Your two sub-queries are returning multiple rows, you will need to specify which single item you want with either an aggregate function of some kind (Min(), Max(), etc...):

SELECT DISTINCT prefix || '-' || lpad(series,3,0) || '-' || lpad(base,3,0) || '-' || lpad(suffix,2,0) Draw_Num,
(select Max(n) from numbers where n >=0 and n <= md_draw.rev) Rv,
(select Max(n) from numbers where n >=0 and n <= md_draw.num_shts) Sht
FROM md_draw WHERE Dwg_Date <= TO_DATE('1-JUN-02')
ORDER BY Draw_Num, Rv, Sht

Or, if you need all of them, you should use JOINs instead of sub-queries:

SELECT DISTINCT prefix || '-' || lpad(series,3,0) || '-' || lpad(base,3,0) || '-' || lpad(suffix,2,0) Draw_Num, 
       n1.n As Rv, n2.n As Shts
FROM md_draw 
JOIN numbers n1 
ON n1.n BETWEEN 0 AND md_draw.rev
JOIN numbers n2
ON n2.n BETWEEN 0 AND md_draw.num_shts
WHERE Dwg_Date <= TO_DATE('1-JUN-02')
ORDER BY Draw_Num, Rv, Sht

You may need to tweak that last query, I didn't have time to run it through sqlfiddle or anything, but it should give you a basic idea of what to do. Just an observation, I don't see what relation both rev and num_shts has to do with n in your numbers table.

0
votes

That's because of the subquery below which is producing more than 1 row. If there is a relation between md_draw and numbers then consider doing a JOIN

(select n from numbers where n >=0 and n <= md_draw.rev) Rv
0
votes

Oracle Setup:

CREATE TABLE table_name ( DRAW_NUM, REV, NUM_SHTS ) AS
SELECT 'LD-111-639-01', 2, 3 FROM DUAL UNION ALL
SELECT 'LD-111-639-02', 0, 2 FROM DUAL UNION ALL
SELECT 'LD-111-639-03', 7, 0 FROM DUAL UNION ALL
SELECT 'LD-111-639-04', 1, 2 FROM DUAL;

Query - Using a recursive sub-query factoring clause:

WITH rsqfc ( draw_num, rev, num_shts, rv, sht ) AS (
  SELECT draw_num, rev, num_shts, 1, 1
  FROM   table_name
  WHERE  REV > 0 AND num_shts > 0
UNION ALL
  SELECT draw_num, rev, num_shts,
         CASE sht WHEN num_shts THEN rv+1 ELSE rv END,
         CASE sht WHEN num_shts THEN 1 ELSE sht+1 END
  FROM   rsqfc
  WHERE  rv < rev
  OR     sht < num_shts
)
SELECT draw_num, rv AS rev, sht
FROM   rsqfc
ORDER BY draw_num, rv, sht;

Query - Using Hierarchical Queries:

SELECT t.draw_num,
       r.COLUMN_VALUE AS rev,
       n.COLUMN_VALUE AS sht
FROM   table_name t,
       TABLE(
         CAST(
           MULTISET(
             SELECT LEVEL
             FROM   DUAL
             WHERE  t.REV > 0
             CONNECT BY LEVEL <= t.REV
           ) AS SYS.ODCINUMBERLIST
         )
       ) r,
       TABLE(
         CAST(
           MULTISET(
             SELECT LEVEL
             FROM   DUAL
             WHERE  t.NUM_SHTS > 0
             CONNECT BY LEVEL <= t.NUM_SHTS
           ) AS SYS.ODCINUMBERLIST
         )
       ) n;

Output:

DRAW_NUM             REV        SHT
------------- ---------- ----------
LD-111-639-01          1          1 
LD-111-639-01          1          2 
LD-111-639-01          1          3 
LD-111-639-01          2          1 
LD-111-639-01          2          2 
LD-111-639-01          2          3 
LD-111-639-04          1          1 
LD-111-639-04          1          2