SQL query, please help urgent

[technical_helps]

Hi,
I want some help in writing a SQL Query .Its besically a hierarchical query. Let me lay down the table structure first to explain my requirements better.

PORP_TABLE(NODE_LEVEL int, WBS_ID int, WBS_NUMBER varchar(60), LFT int,RGT int)

SELECT NODE_LEVEL, WBS_ID, LFT,RGT FROM PROPOSAL_WBS PW WHERE PROPOSAL_REV_ID = 7000

(SAMPLE DATA)

NODE WBS
LEVEL WBS_ID NUMBER LFT RGT
------------------------------------------------------------------------
0 7055 ROOT 1 24
1 7056 1 2 5
1 7088 2 6 9
2 7057 1.1 3 4
2 7089 2.1 7 8
2 7091 3.1 11 14
2 7103 3.2 15 16
2 7105 4.1 19 20
1 7090 3 10 17
3 7092 3.1.1 12 13
1 7104 4 18 23
2 7106 4.2 21 22


ALLOCATION_DETAIL( WBS_ID int, COST_ID int, PERIOD Date, AMOUNT Float)

sample data
WBS_ID , COST_ID , PERIOD , AMOUNT
------------------------------------------------------------
7057 100 01-jan-2005 5000
7057 100 01-feb-2005 2000
7057 100 01-mar-2005 1000
7057 100 01-apr-2005 6000
7057 100 01-may-2005 3000
7057 100 01-jun-2005 45000
7106 100 01-mar-2005 8000
7106 100 01-apr-2005 7000
7106 100 01-may-2005 9000


Now the PORP_TABLE has got the parents and childs. Only the leaf nodes in the hierarchy has the values stored in the ALLOCATION_DETAIL table. Now here is the scenario

In the example 7055 is the root WBS . The Leaf WBS are the one with max extension in the wbs number ( in this case it is 1.1, 2.1, 3.1.1, 3.2, 4.1 and 4.2)

Now the Starting period for each leaf node in the ALLOCATION_TABLE could be differrent . What that means is WBS 1.1 could start in Jan -2003 and WBS 3.1 Could be Jul-2005 . So the ending perios are also differrent for differrent WBS . Some can span 2 years some can 5 years.

So how to write a query so it retrieves the value for all the Wbs starting from the MIN ( PERIOD ) upto the MAX(PERIOD), and it should roll up also. Now there is No connect by Prior or any analytic functions available for this . THIS NEEDS TO BE DONE ONLY THROUGH TRADITIONAL SQL STATEMENT . And NO DB FUNCTIONS CAN BE USED .
Now if the WBS is a parent node then it should have the sum of all its child nodes for the COST category.

SO THE RESULT SET SHOULD BRING LIKE THIS

WBS_NUMBER, PERIOD_NUMER, COST_CATEGORY , AMOUNT
--------------------------------------------------------------------------------------------------
ROOT
......
......
1
......
......
1.1
......
......
2
......
......
2.1
......
......
3
......
......
3.1
......
......
3.1.1
......
......
3.2
......
......
4
......
......
4.1
......
......
4.2
......
......

[tamilselvan]

Now there is No connect by Prior or any analytic functions available for this . THIS NEEDS TO BE DONE ONLY THROUGH TRADITIONAL SQL STATEMENT .

Are you using Informix, the dead product?

Why don't you post the create and insert statements for all tables?

Tamil

[technical_helps]

No I am using Sybase ASE.

[davey23uk]

err this is an oracle forum

[technical_helps]

I know this is an Oracle Forum . But this is a SQL question. So any help will be appreciated.

[tamilselvan]

Why don't you post the create and insert statements for all tables?

Tamil

[technical_helps]

hi Tamil,
The table has got more fields, but I have selected few fields which are required. And the data is saved into these table through portals. I am trying to create a crosstab ( matrix) report using Crystal Report 10 . So it lists, the leaf WBS with their data for each cost category for all the periods ( so if for a perticular wbs , and period there is no amount then it lists 0 (zero) . And then the roll up for the parents . So basically the report should look like this ( with the hirarchy of wbs like root, then parent then child and leaf).

PERIOD_YEAR ----------------------------->
WBS NUMBER , COST_CATEGORY
|| || amount---------->
|| || |
|| || |
\/ \/ \/