0

There is a set of records in a table which are in hierarchical order. I have to get the flows separatelyheirarchichal flow

As per the image, the separate flows will be like,

  • A -> B-> D
  • A -> B-> E
  • A->C-> F
  • A->C-> G

but the number of levels are dynamic and no. of records per parent node can be also dynamic that means for example, B can have D, E, H,O.. so on

also these A,B,C... are not primary keys of this table

so this each record (A,B,C...) has quantity and price columns.

what I want to do is, calculate the quantity*price of each level with its previous level's quantity* price value in the separate flows. As per this example,

------------------------------
part  sub_part price quantity
-----------------------------
G     C        3       1
F     C        1       1
E     B        4       3
D     B        3       1
C     A        5       2
B     A        2       1

final results of each flow should be,

  • A -> B-> D = 6
  • A -> B-> E = 24
  • A->C-> F = 10
  • A->C-> G = 30

no. of flows is not stable. this depends on the no. of records in hierarchy, which is dynamic

I tried with hierarchical queries but I cannot do the calculation in the same query. also tried saving the records in arrays but since they are dynamic, that didn't give expected results.

user2431205
  • 81
  • 1
  • 1
  • 7
  • 1
    Provide some data in proper sql format with table and expected result which would prompt anyone to try for a solution fast. – Sujitmohanty30 Aug 25 '20 at 17:21
  • Normally we can perform a hierarchical query using [connect by](https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/Hierarchical-Queries.html#GUID-0118DF1D-B9A9-41EB-8556-C6E7D6A5A84E) or recursive WITH clause, without any need for PL/SQL. – William Robertson Aug 25 '20 at 17:30
  • @Sujitmohanty30 I have added the table and expected results. can you give me a solution – user2431205 Aug 26 '20 at 05:11
  • @WilliamRobertson Can you please elaborate with a simple example – user2431205 Aug 26 '20 at 05:12
  • From your sample "final results" is seems that the hierarchy always starts with A. However, there is no part A. – William Robertson Aug 26 '20 at 08:08
  • yes.it is always from a sub_part the connection starts from. because that is the only value provided and has to find the rest of the flow depending on the sub_part – user2431205 Aug 26 '20 at 14:37

0 Answers0