PL/SQl question, oracle 9i

Ok guys, we have a situation here in our project. We have an order which customer placed, and it gets split into multiple orders. We need to find out how many orders it got split into.

Table structure is like:

Order_Orig       Order_next  Sr #
1                     2        1
2                     3        2
3                     4        3

So like in the example above we have order 1 wh开发者_如何转开发ich was placed with the customer and it got split into 1,2,3,4 orders and got stored in the table in the above format. So how do we find out how many orders was the initial order split into?

TIA

Maybe this can steer you in the right direction by using Hierarchical Queries.

here are some detailed examples!

Hierarchical queries

As davidsr says, you do need to use a hierarchical query.

If you want to know simply how many orders a particular order was split into then you can use:

SELECT MAX(level) + 1 AS sub_order_number
  FROM <order_table>
 START WITH order_orig = <orig_order_id>
 CONNECT BY PRIOR order_next = order_orig;

Using the table data you supplied and using an of 1, you would get a sub_order_number of 4, the orig_order_id of 1 was split into 4 orders.

If you want to see those orders you could use:

SELECT order_orig,
       order_next,
       level
  FROM <order_table>
 START WITH order_orig = <orig_order_id>
 CONNECT BY PRIOR order_next = order_orig;

Using the same criteria as above you would get:

ORDER_ORIG ORDER_NEXT LEVEL
         1          2     1
         2          3     2
         3          4     3       

If you were to change the to 2 then you'd get:

ORDER_ORIG ORDER_NEXT LEVEL
         2          3     1
         3          4     2  

You can test this out for youself, I ran it all as a single query using:

WITH t AS (SELECT 1 AS order_orig,
                  2 AS order_next,
                  1 AS Sr# FROM DUAL
           UNION
           SELECT 2 AS order_orig,
                  3 AS order_next,
                  2 AS Sr# FROM DUAL
           UNION
           SELECT 3 AS order_orig,
                  4 AS order_next,
                  3 AS Sr# FROM DUAL
          ) 
SELECT order_orig,
       order_next,
       level
  FROM t
 START WITH order_orig = 2
 CONNECT BY PRIOR order_next = order_orig;

For more info on hierarchical queries, see the link in davidsr's answer.

Hope this helps....