Update a field based on position in table 1

[Welshbird]

This thread follows on from thread1177-1313000, as I decided that maybe I needed to import the data first and then try and update the fields as necessary....

OK - I have a table (now!) which contains the following information:

  NLS S  PROD_CODE        LEV
----- - ---------- ----------
    1 0          1          1
    2 0        101          3
    3 0      10101          5
    4 1     934794          7
    5 1       2600          7
    6 1    2729416          7
    7 1    2495877          7
    8 1    2495869          7
    9 1      90480          7
   10 1      65979          7
   11 1 8880002838          7
   12 1    1115765          7
   13 1 8880005027          7
   14 1    3190535          7
   15 1    2932861          7
   16 1     473058          7
   17 1      57117          7
   18 1     247650          7
   19 1     266882          7
   20 1    2500684          7

The last column is calcuated to tell me the level of detail that this line represents, so 1 is the top level, 3 a subtotal, 5 a lower level of subtotal and 7 the detail.

When I need to work out next is how to update a new column with the 'rollup' code.

So, for all of the lines with a 7, I need to add the code for the '5' line which preceeds them. And then for the 5's, to add the code for the 3 that preceeds them, and then for the 3, the code for the 1 that proceeds them.

So, I want to end up with this:

  NLS S  PROD_CODE        LEV      RollUp
----- - ---------- ---------- -----------
    1 0          1          1           
    2 0        101          3           1
    3 0      10101          5         101
    4 1     934794          7       10101
    5 1       2600          7       10101
    6 1    2729416          7       10101
    7 1    2495877          7       10101
etc

Any ideas?

Fee

The question should be [red]Is it worth trying to do?[/red] not [blue] Can it be done?[/blue]

 

[Welshbird]

Aha!

The lecagy system has managed to help me a little - for all 3's and 5's the rollup code is a substring of the first characters....

So now all is filled in, except those where the LEV = 7, which is purely based on position in the table ('NLS' iin this table - a sequence popluated on load) and sadly this is the most important bit.....

I'm trying to find a solution on my own, but I'm starting to struggle even more now, so any ideas would be flippin' marvellous.....

Fee

The question should be [red]Is it worth trying to do?[/red] not [blue] Can it be done?[/blue]

 

[Dagon]

Can't you just write a PL/SQL routine which works through the table in order of NLS using a cursor. You then just have to store the prod_code of the last level 5 and keep appending it to the level 7 records until you encounter the next level 1 record. You then just carry on to the level 5, put that in memory and loop through the next lot of level 7s etc.

 

[Welshbird]

At the moment I've got this far

update FF_MARKET a set rollup =
(select rollup from ff_market b
  where a.nls = b.nls+1
    and a.lev = 7)
where rollup is null
  and exists
(select rollup from ff_market b
 where a.nls = b.nls+1
    and a.lev = 7)

which works really well, but just for the next line down.

I was trying to avoid a PL/SQL routine, as I haven't yet worked out how to call that from an SQL script, which at the moment is my only way to go to 'automate' this with minimum user intervention.

Good idea though - thanks for that - I'll look into how I call a PL/SQL procedure from a SQL script - is that even possible??

Fee

The question should be [red]Is it worth trying to do?[/red] not [blue] Can it be done?[/blue]

 

[Dagon]

Do you mean SQL*Plus ? You just do it using:

declare
...
begin
...
end;
/

 

[Welshbird]

So far I have:

-- add in the subtotal code into the first detail record
update FF_MARKET a set rollup =
(select prod_code from ff_market b
  where a.nls = b.nls+1
    and a.lev = 7)
where rollup is null
  and exists
(select rollup from ff_market b
 where a.nls = b.nls+1
    and a.lev = 7
    and a.lev > b.lev)
-- copy the subtotal code into the following detail record
update FF_MARKET a set rollup =
(select rollup from ff_market b
  where a.nls = b.nls+1
    and a.lev = 7)
where rollup is null
  and exists
(select rollup from ff_market b
 where a.nls = b.nls+1
    and a.lev = 7 
    and b.lev = 7)

and it seems I need to loop the last section until there are no null records in the field 'rollup'

Getting there though!!! I feel like I am finally winning....



Fee

The question should be [red]Is it worth trying to do?[/red] not [blue] Can it be done?[/blue]

 

[Welshbird]

WOOHOO!

Dagn - you are marvellous

DECLARE
BEGIN
-- loop until all rollup fields are populated
  for ff_loop in 1..100
update FF_MARKET a set rollup =
(select rollup from ff_market b
  where a.nls = b.nls+1
    and a.lev = 7)
where rollup is null
  and exists
(select rollup from ff_market b
 where a.nls = b.nls+1
    and a.lev = 7 
    and b.lev = 7)
  end loop;
end if;
end;
/

I suppose I could loop the 'correct' number of times rather than an arbitrary 100, but hey - that is just tidying up now!

Hava purple thing. I really do appreciate your help.

Fee

The question should be [red]Is it worth trying to do?[/red] not [blue] Can it be done?[/blue]