cost allocation by sql query

[Former Member]

Hello Experts,

I want to allocate cost in top down approach via SQL i.e
user input should be
1.Expense amount for node 1 (which need to be allocate in lower nodes)
2. Percentage of allocation for each nodes

              Node 1
      Node 2             node3

node4        node 5     node 6    node 7

if node 1 gets 100 rupee.then it should get allocated in node 2 and node 3 in 40-60%(User input).
also it should get allocated further in node 4 and 5 basis user input percentage and also in node 6 and 7.

Thanks

[Former Member · Accepted solution]

for a starting point checkout:

Breck has blogged in detail on these topics here:

• Example: RECURSIVE UNION Tree Traversal
• Example: RECURSIVE UNION Inverted ORDER BY

or the answers to question:

How do I build a result set of a tree structure in a depth-first search?

[Former Member · Accepted solution]

Thank you Martin.

I tried to apply the same in my query.

links were useful.

also, i need SQL to prompt for user input.

1. on the basis of Expense amount for node 1

2. % allocation i.e if node 2 gets 45% then node 3 should get 55% similerly for rest of the node.

i tried something like this:-

SELECT tab1.amt * (tab1.per / 100), tab1.amt * ( (100 - tab1.per) / 100)

FROM DUAL, (SELECT amount.amt, percentage.per FROM DUAL) tab1;

but i am getting 2 problem while running it in MS-access

1.MS access throwing an error MS access can't find input table or query DUAL

2. it is not recursive.

kindly suggest

Thanks

[former_member188493 · Accepted solution]

This is not a forum for MS Access or Oracle. This is a forum for SQL Anywhere which is an actual product from SAP.