Oracle/PLSQL Recursion

Question

I have a table like:

+------+------+--------+------+
|      |      |  UOM   |      |
| ITEM | UOM  | BEFORE | SOLD |
+------+------+--------+------+
|  1   | Case |  Box   |  Y   |
|  2   | Each |  Oz    |      |
|  1   | Box  |  Each  |  Y   |
|  1   | Each |  Oz    |      |
|  2   | Case |  Box   |  Y   |
|  2   | Box  |  Each  |  Y   |
+------+------+--------+------+

How can I query it so I get results like:

+------+-------+------+
| ITEM |  UOM  | SOLD |
+------+-------+------+
|  1   | Case  |  Y   |
|  1   | Box   |  Y   |
|  1   | Each  |  Y   |
|  1   | Oz    |      |
|  2   | Case  |  Y   |
|  2   | Box   |  Y   |
|  2   | Each  |  Y   |
|  2   | Oz    |      |
+------+-------+------+

Listing each unit of measure that the item is sold, descending from the highest UOM down to the lowest, where sold is null?

Thanks for the help!

Belayer · Accepted Answer · 2019-11-26 23:21:07Z

2

Besides Recursive CTE ("recursive subquery factoring" in Oracle speak) Oracle provides a hierarchical query via "connect by". So with that you get:

 select item_id, uom, sold
   from (select item_id, uom_before uom, sold, 0 r
           from items
          where sold is null
         union all
         select item_id,uom,uom_before, rownum r
           from items
         connect by  prior  item_id = item_id
                     and  prior uom =  uom_before
           start with sold is null 
        )
order by item_id, r desc;

answered Nov 26, 2019 at 23:21

Belayer

15.1k2 gold badges13 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Shaun Peterson Over a year ago

Instead of rownum you could also use 'level' in the same way for ordering.

Belayer Over a year ago

True, but they are both internally generated pseudo columns, so 6 of one 1/2 dozen of the other ( i.e. it basically makes no difference).

Shaun Peterson Over a year ago

Totally agree +1ed your answer as it is correct, I have a (probably irrational) dislike of rownum so just wanted to present the other option.

anonimitie Over a year ago

Terrific! Thank you!

Acroyear · Accepted Answer · 2019-11-26 21:38:24Z

0

No recursion needed. UNION and outer join will do.

SELECT a.item, a.uom, b.sold
FROM
  (SELECT item, uom
   FROM uom_table
   UNION
   SELECT item, uom_before
   FROM uom_table) a,
  (SELECT * FROM (
     SELECT item, uom, sold
     FROM uom_table
     UNION
     SELECT item, uom_before uom, sold
     FROM uom_table)
   WHERE sold IS NOT NULL) b
WHERE a.item = b.item (+)
AND a.uom = b.uom (+)
ORDER BY a.item, b.sold;

edited Nov 26, 2019 at 21:38

answered Nov 26, 2019 at 21:33

Acroyear

1,4614 gold badges25 silver badges40 bronze badges

Collectives™ on Stack Overflow

Oracle/PLSQL Recursion

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related