Normalising Legacy Data 3

[Welshbird]

I have an old legacy data-producing system that provides we data with some identifiers, and then 48 columns. The first 24 columns show units, and then the next 24 show values.

I had thought that maybe I could normalise these when loading with SQLLDR, but as these are comma-seperated rather than positional I thought maybe it would be best to load these into a temp table and then run a PL/SQL Loop to normalise them.

So, my loaded table looks like this:

GEOGRAPHY             CHAR(5)
CEG_PROD_CODE      VARCHAR2(14)
UM1                NUMBER(38)
UM2                NUMBER(38)
...
UM23               NUMBER(38)
UM24               NUMBER(38)
VM1                NUMBER
VM2                NUMBER
...
VM23               NUMBER
VM24               NUMBER
DATE_LOADED        DATE

The months that are provided in each of the 24 columns are a movable feast, but the month is loaded elsewhere, so I have at least worked out how to grab that.

This is what I have begun with:

DECLARE
 fee date;
 BEGIN
   select add_months(period_number,-24) into fee from ff_header;
      for i in 1..24
 loop
 insert into FF_board
 (GEOGRAPHY,CEG_CODE,MNTH,UNIT,VALUE)
 VALUES
 (select A.GEOGRAPHY, SUBSTR(A.CEG_PROD_CODE, 2,13),
 (SELECT TO_CHAR(ADD_MONTHS(FEE,i),'YYMM') FROM DUAL;),
 a.UM1, a.VM1
 from
 ff_results a, ff_territory b
 where a.brick = b.brick);
 end loop;
 end;

but already this doesn't work. I get the following error:
ERROR at line 10:
ORA-06550: line 10, column 3:
PL/SQL: ORA-00936: missing expression
ORA-06550: line 7, column 2:
PL/SQL: SQL Statement ignored
ORA-06550: line 12, column 54:
PLS-00103: Encountered the symbol ")" when expecting one of the following:
begin case declare end exit for goto if loop mod null pragma
raise return select update while with <an identifier>
<a double-quoted delimited-identifier

And I still haven't got my brain around the way to pick the column names in some kind of loopy-cursor type deal.

I strikes me that someone else must have done this before - so any pointers you guys could give me would be really helpful.


Fee

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

 

[Welshbird]

PHARGREAVES>create or replace
  2  PROCEDURE load_data IS
  3  l_str clob:= 'INSERT ALL ';
  4  start_date DATE := sysdate;
  5  BEGIN
  6  
  7    FOR i IN 1..24 LOOP
  8      
  9      l_str := l_str||' INTO FF_BOARD
 10                        VALUES (A.AREA'||
 11                              ', A.REGION'||
 12                              ', A.TERRITORY'||
 13                              ', B.UGA_SECT'||
 14                              ', B.REGION'||
 15                              ', B.TERRITORY'||
 16                              ', A.BRICK'||
 17                              ', CG'||
 18                              ', ''' ||TO_CHAR(ADD_MONTHS(start_date,-i),'YYMM')||
 19                           ''', a.UM'||i||
 20                              ', a.VM'||i||
 21                              ', sysdate)
 22                              ';
 23    END LOOP;
 24  
 25    l_str := l_str||'SELECT   A.AREA'||
 26                            ', A.REGION'||
 27                            ', A.TERRITORY'||
 28                            ', B.UGA_SECT'||
 29                            ', B.REGION'||
 30                            ', B.TERRITORY'||
 31                            ', A.BRICK'||
 32                            ', (SUBSTR(A.CEG_PROD_CODE, 2,13)) CG'||
 33                            ', a.UM1'||
 34                            ', a.VM1'||
 35                            ', a.UM3'||
 36                            ', a.VM3'||
 37                            ', a.UM4'||
 38                            ', a.VM4'||
 39                            ', sysdate'||
 40                       'FROM  ff_results a'||
 41                       'JOIN ff_territory b'||
 42                       'ON  a.brick = b.brick';
 43   
 44   end;
 45  /

Procedure created.

Elapsed: 00:00:00.13
PHARGREAVES>exec load_data

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.18
PHARGREAVES>set serveroutput on
PHARGREAVES>exec load_data

PL/SQL procedure successfully completed.

that seems happy.

Fee

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

 

[jimirvine]

OK,
now add the dbms_output back in and give it a try. (I think I know where this is going .

 

[Thargy]

Not the dreaded line-length limit, puhleeze!

T

Grinding away at things Oracular

 

[Welshbird]

Procedure created.

Elapsed: 00:00:00.15
PHARGREAVES>exec load_data
BEGIN load_data; END;

*
ERROR at line 1:
ORA-20000: ORU-10028: line length overflow, limit of 255 chars per line
ORA-06512: at "SYS.DBMS_OUTPUT", line 35
ORA-06512: at "SYS.DBMS_OUTPUT", line 115
ORA-06512: at "PHARGREAVES.LOAD_DATA", line 42
ORA-06512: at line 1


Elapsed: 00:00:00.19
PHARGREAVES>

Lully indeed. (If only I knew what to do with it now!)

Fee

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

 

[jimirvine]

lol

 

[Thargy]

jim,

you're a bad lad. Fancy exposing the poor unsuspecting public to such tawdry filth as this, and it's not even lunchtime yet - I'm feeling nauseous already.

T

Grinding away at things Oracular

 

[hoinz]

jimirvine,

I tried and played around a bit, and I got it to work when I reduced the lenght of l_str.
But I agree, because of the clob, the lenght should not have been the root cause.
And glad there is progress now!

 

[jimirvine]

@Fee

set up a dummy ff_table table to insert your values and get rid of dbms_output(l_str) and change it to
EXECUTE IMMEDIATE l_str;

then check the results in the table

 

[Welshbird]

Okey dokie:

 43   EXECUTE IMMEDIATE l_str;
 44   end;
 45  /

Warning: Procedure created with compilation errors.

Elapsed: 00:00:00.11
PHARGREAVES>show errors
Errors for PROCEDURE LOAD_DATA:

LINE/COL ERROR
-------- -----------------------------------------------------------------
42/2     PL/SQL: Statement ignored
42/20    PLS-00382: expression is of wrong type
PHARGREAVES>

Fee

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

 

[jimirvine]

Right!
comment out the execute immediate,
add in the following 2 lines

dbms_ouput.enable(6000);
dbms_output.put_line(l_str);

What happens now

 

[Welshbird]

 42                       'ON  a.brick = b.brick';
 43   --EXECUTE IMMEDIATE l_str;
 44   dbms_ouput.enable(6000);
 45  dbms_output.put_line(l_str);
 46   end;
 47  /

Warning: Procedure created with compilation errors.

Elapsed: 00:00:00.12
PHARGREAVES>show errors
Errors for PROCEDURE LOAD_DATA:

LINE/COL ERROR
-------- -----------------------------------------------------------------
43/2     PL/SQL: Statement ignored
43/2     PLS-00201: identifier 'DBMS_OUPUT.ENABLE' must be declared
PHARGREAVES>

Fee

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

 

[jimirvine]

my bad. dbms_output.enable, not ouput.enable

 

[Welshbird]

my bad - I have obviously past the point of being able to read it!

 40   --EXECUTE IMMEDIATE l_str;
 41   dbms_output.enable(6000);
 42  dbms_output.put_line(l_str);
 43*  end;
 44  /

Procedure created.

Elapsed: 00:00:00.17
PHARGREAVES>exec load_data
BEGIN load_data; END;

*
ERROR at line 1:
ORA-20000: ORU-10028: line length overflow, limit of 255 chars per line
ORA-06512: at "SYS.DBMS_OUTPUT", line 35
ORA-06512: at "SYS.DBMS_OUTPUT", line 115
ORA-06512: at "PHARGREAVES.LOAD_DATA", line 41
ORA-06512: at line 1


Elapsed: 00:00:00.34
PHARGREAVES>

Fee

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

 

[jimirvine]

OK, let's try this

drop that procedure. create the following

create or replace function f_load_data RETURN clob IS
l_str clob:= 'INSERT ALL ';
start_date DATE := sysdate;
BEGIN

  FOR i IN 1..24 LOOP
    
    l_str := l_str||' INTO FF_BOARD
                      VALUES (A.AREA'||
                            ', A.REGION'||
                            ', A.TERRITORY'||
                            ', B.UGA_SECT'||
                            ', B.REGION'||
                            ', B.TERRITORY'||
                            ', A.BRICK'||
                            ', CG'||
                            ', ''' ||TO_CHAR(ADD_MONTHS(start_date,-i),'YYMM')||
                         ''', a.UM'||i||
                            ', a.VM'||i||
                            ', sysdate)
                            ';
  END LOOP;

  l_str := l_str||'SELECT   A.AREA'||
                          ', A.REGION'||
                          ', A.TERRITORY'||
                          ', B.UGA_SECT'||
                          ', B.REGION'||
                          ', B.TERRITORY'||
                          ', A.BRICK'||
                          ', (SUBSTR(A.CEG_PROD_CODE, 2,13)) CG';
    FOR i IN 1..24 LOOP
      l_str := l_str||', a.UM'||i||', a.VM'||i;
    END LOOP;
    
    l_str := l_str||', sysdate '||
                     ' FROM  ff_results a'||
                     ' JOIN ff_territory b'||
                     ' ON  a.brick = b.brick';
RETURN l_str;
 end;

go into sqlplus
SQL> SET LONG 6000
SQL> SET LINES 250
SQL> VAR X CLOB
SQL> EXEC :X := f_load_data
SQL> PRINT X

This should print out the string. There'll be a few changes you'll likely need to change to get the sql right. Once it is correct you will be able to:

DECLARE
l_sql VARCHAR2(32767);
BEGIN
l_sql := f_load_data;
EXECUTE IMMEDIATE l_sql;
END;

(Or various other slightly less convoluted things, but this should work for you. I'm actually on holiday for the next week (not away anywhere, I'm decorating ) I'll be checking in occasionally, but not as much as if I was at work.
GL

 

[Welshbird]

Jim - I really do appreciate all of your help with this. I'll try it on Monday and post back how I get on.

Fee

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

 

[Welshbird]

*sigh*

I had more problems, so I thought I should walk around the office car park (with a cigarette!) until light might dawn.. and so I tried a completely different method.

So, this is my current plan
First create a normalised view:

create view vw_results as 
select 
FR_TYPE, AREA, REGION, TERRITORY, GSU_GROUP, BRICK, CEG_PROD_CODE, 
1 as MNTHSEQ, 
UM1, VM1 from ff_results 
UNION 
select 
FR_TYPE, AREA, REGION, TERRITORY, GSU_GROUP, BRICK, CEG_PROD_CODE, 
2 as MNTHSEQ, 
UM2, VM2 from ff_results 
UNION 
select 
FR_TYPE, AREA, REGION, TERRITORY, GSU_GROUP, BRICK, CEG_PROD_CODE, 
3 as MNTHSEQ, 
UM3, VM3 from ff_results 
…. 
UNION 
select 
FR_TYPE, AREA, REGION, TERRITORY, GSU_GROUP, BRICK, CEG_PROD_CODE, 
24 as MNTHSEQ, 
UM24, VM24 from ff_results;

Now, this ought to always work, as it doesn't care which month is which at this point, and therefore maintainance should be negligable.
I then create the table I need in which I look up the correct date and 'bung' it in along with the other standard info:

create table ff_board as
 (
 select
 a.AREA as FF_area,    
 a.REGION as FF_REGION,   
 a.TERRITORY as FF_TERRITORY,
 b.UGA_SECT,
 b.REGION,   
 b.TERRITORY,
 a.BRICK,
 substr(a.CEG_PROD_CODE, 2,13) as MP_CEG_CODE,
 c.mp_desc,
 c.mp_brand,
 to_char(add_months((add_months(d.period_number, -24)), a.mnthseq), 'YYMM') as MNTH,
 UM1 as UNIT,
 VM1 as VALUE
 from
 vw_results a,
 ff_Territory b,
 produit c,
 ff_header d
 where
 a.brick = b.brick
 and
 substr(a.CEG_PROD_CODE,2,13) = c.mp_ceg_code
 );

And that seems to work incredibly well, and take about 50 seconds, which isn't too bad, all things considered.

Appreciate all your help guys - stars all 'round!




Fee

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