CODEWITHSUNDEEP
recursionplsql
anonimitie
anonimitie

Reputation: 39

Oracle/PLSQL Recursion

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!

Upvotes: 1

Views: 75

Answers (2)

Belayer
Belayer

Reputation: 14886

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;

Upvotes: 2

Acroyear
Acroyear

Reputation: 1460

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;

Upvotes: 0

Related Questions