Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Chris Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Normalising Legacy Data 3

Status
Not open for further replies.
Welshbird

Welshbird

IS-IT--Management
Jul 14, 2000
7,378
DE
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:
Code: 
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:
Code: 
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]
 
Sort by date Sort by votes
No I'm not! But when I remove it it starts complaining about the (SELECT... on line 10.

I still can't get my head around how I have a loop for the months and possibly some sort of cursor to move through the columns.

Can I use the loop as a wildcard in a select from user_tab_columns or something? I might give that a try.

Fee

The question should be [red]Is it worth trying to do?[/red] not [blue] Can it be done?[/blue]
 
Upvote 0 Downvote
Hi,
I reckon you can probably do this kind of thing in a single statement.
Here is a rough demo on how. If you supply create table and insert scripts for ALL tables involved along with explanations and desired results, I'm sure that we can help further.

Code: 
DROP TABLE piv;
DROP TABLE rev_piv;

CREATE TABLE piv ( Week_id NUMBER
                  ,sales_mon NUMBER
                  ,sales_tue NUMBER
                  ,sales_wed NUMBER
                  ,sales_thu NUMBER
                  ,sales_fri NUMBER
                  ,sales_sat NUMBER
                  ,sales_sun NUMBER);
                  
INSERT INTO piv VALUES(1, 10,20,30,40,50,60,70);
INSERT INTO piv VALUES(2, 110,210,310,410,510,610,740);
INSERT INTO piv VALUES(3, 120,220,320,420,520,620,710);
INSERT INTO piv VALUES(4, 130,230,330,430,530,630,720);
INSERT INTO piv VALUES(5, 140,240,340,440,540,640,730);


CREATE TABLE rev_piv(week_id NUMBER
                    ,day VARCHAR2(3)
                    ,sales NUMBER
                    );
                    
INSERT ALL
  INTO rev_piv
  VALUES(week_id, 'Mon', sales_mon)
  INTO rev_piv
  VALUES(week_id, 'Tue', sales_tue)
  INTO rev_piv
  VALUES(week_id, 'Wed', sales_wed)
  INTO rev_piv
  VALUES(week_id, 'Thu', sales_thu)
  INTO rev_piv
  VALUES(week_id, 'Fri', sales_fri)
  INTO rev_piv
  VALUES(week_id, 'Sat', sales_sat)
  INTO rev_piv
  VALUES(week_id, 'Sun', sales_sun)
    SELECT * FROM piv;
 
Upvote 0 Downvote
That sounds like a plan! - But I don't know what my column names will be - I have to create those dynamically I think.

But, I might have got my head around the movable columns names:
this is what I have now
Code: 
DECLARE
 fee date;
 v_field varchar2(30);
 u_field varchar2(5);
 BEGIN
   select add_months(period_number,-24) into fee from ff_header;
      for i in 1..24
 loop
 v_field := 'UM'||i;
 u_field := 'VM'||i;
 insert into FF_board
 (FF_AREA,FF_REGION,FF_TERRITORY,UGA_SECT,REGION,TERRITORY,BRICK,CEG_CODE,MNTH,UNIT,VALUE)
 VALUES
 (select A.AREA, A.REGION, A.TERRITORY, B.UGA_SECT, B.REGION, B.TERRITORY, A.BRICK,
 SUBSTR(A.CEG_PROD_CODE, 2,13),
 (SELECT TO_CHAR(ADD_MONTHS(FEE,i),'YYMM') FROM DUAL;),
 a.u_field, a.v_field
 from
 ff_results a, ff_territory b
 where a.brick = b.brick);
 end loop;
 end;
and I have some faith that my u_field and v_field might work out.

I still get the following error thoguh.
Code: 
 (select A.AREA, A.REGION, A.TERRITORY, B.UGA_SECT, B.REGION, B.TERRITORY, A.BRICK,
  *
ERROR at line 14:
ORA-06550: line 14, column 3:
PL/SQL: ORA-00936: missing expression
ORA-06550: line 11, column 2:
PL/SQL: SQL Statement ignored
ORA-06550: line 16, 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

If I could do with a single select though, I'd be delighted!

I'll post some table structures.


Fee

The question should be [red]Is it worth trying to do?[/red] not [blue] Can it be done?[/blue]
 
Upvote 0 Downvote
The main table structure is in the first post, but I just put ... to show that the fields are repeated number 1~24 in both cases.

Fee

The question should be [red]Is it worth trying to do?[/red] not [blue] Can it be done?[/blue]
 
Upvote 0 Downvote
willif,

am I correct in thinking that you want to do something like..

Code: 
INSERT INTO WILLIF_TARGET
   (SELECT GEOGRAPHY, CEG_PROD_CODE, UM1,VM1
      FROM WILLIF_LOADED);

INSERT INTO WILLIF_TARGET
   (SELECT GEOGRAPHY, CEG_PROD_CODE, UM2,VM2
      FROM WILLIF_LOADED);
...

INSERT INTO WILLIF_TARGET
   (SELECT GEOGRAPHY, CEG_PROD_CODE, UM24,VM24
      FROM WILLIF_LOADED);

or have I got the wrong end o' the stick.

T


Grinding away at things Oracular
 
Upvote 0 Downvote
That's EXACTLY what I want to do, but I want to bung it in a loop.

Fee

The question should be [red]Is it worth trying to do?[/red] not [blue] Can it be done?[/blue]
 
Upvote 0 Downvote
OK, I've run a simple select from the middle bit of my PL/SQL like this:
Code: 
(select A.AREA, A.REGION, A.TERRITORY, B.UGA_SECT, 
 B.REGION, B.TERRITORY, A.BRICK, 
 (SUBSTR(A.CEG_PROD_CODE, 2,13)), 
 (SELECT TO_CHAR(ADD_MONTHS('01-Mar-04',1),'YYMM')FROM DUAL),
 a.UM1, a.VM1, sysdate
 from  ff_results a, ff_territory b 
 where a.brick = b.brick)
and that works, so there must be some summat or nuthin; about PL/SQL and my select, or PL/SQL and my variables....

Phooey.

Fee

The question should be [red]Is it worth trying to do?[/red] not [blue] Can it be done?[/blue]
 
Upvote 0 Downvote
willif,

got to go now, but the script below might be useful to run some tests. I'll do further battle tomorrow.

Code: 
DROP TABLE WILLIF_LOADED;
DROP TABLE WILLIF_TARGET;

CREATE TABLE WILLIF_LOADED
(
 GEOGRAPHY          VARCHAR2(5),
 CEG_PROD_CODE      VARCHAR2(14),
UM1   NUMBER(38),
UM2   NUMBER(38),
UM3   NUMBER(38),
UM4   NUMBER(38),
UM5   NUMBER(38),
UM6   NUMBER(38),
UM7   NUMBER(38),
UM8   NUMBER(38),
UM9   NUMBER(38),
UM10   NUMBER(38),
UM11   NUMBER(38),
UM12   NUMBER(38),
UM13   NUMBER(38),
UM14   NUMBER(38),
UM15   NUMBER(38),
UM16   NUMBER(38),
UM17   NUMBER(38),
UM18   NUMBER(38),
UM19   NUMBER(38),
UM20   NUMBER(38),
UM21   NUMBER(38),
UM22   NUMBER(38),
UM23   NUMBER(38),
UM24   NUMBER(38),
VM1   NUMBER(38),
VM2   NUMBER(38),
VM3   NUMBER(38),
VM4   NUMBER(38),
VM5   NUMBER(38),
VM6   NUMBER(38),
VM7   NUMBER(38),
VM8   NUMBER(38),
VM9   NUMBER(38),
VM10   NUMBER(38),
VM11   NUMBER(38),
VM12   NUMBER(38),
VM13   NUMBER(38),
VM14   NUMBER(38),
VM15   NUMBER(38),
VM16   NUMBER(38),
VM17   NUMBER(38),
VM18   NUMBER(38),
VM19   NUMBER(38),
VM20   NUMBER(38),
VM21   NUMBER(38),
VM22   NUMBER(38),
VM23   NUMBER(38),
VM24   NUMBER(38),
DATE_LOADED DATE
);

CREATE TABLE WILLIF_TARGET
(
 GEOGRAPHY          VARCHAR2(5),
 CEG_PROD_CODE      VARCHAR2(14),
 MONTH_NUMBER       NUMBER(2,0),
 MONTH_VALUE        NUMBER (38),
 DATE_LOADED        DATE
);

See you anon

T

Grinding away at things Oracular
 
Upvote 0 Downvote
(After much helpful training from Tharg in the past) I'm trying to narrow down where my problem might be.

So, I've tried running the insert as SQL for one month:
Code: 
insert into FF_board
(select A.AREA, A.REGION, A.TERRITORY, B.UGA_SECT, 
 B.REGION, B.TERRITORY, A.BRICK, 
 (SUBSTR(A.CEG_PROD_CODE, 2,13)), 
 (SELECT TO_CHAR(ADD_MONTHS('01-Mar-04',1),'YYMM')FROM DUAL),
 a.UM1, a.VM1, sysdate
 from  ff_results a, ff_territory b 
 where a.brick = b.brick)
All works perfectly.

So, I thought I should test the loop and the variables:
Code: 
DECLARE
 fee date;
 v_field varchar2(4);
 u_field varchar2(4);
 mnth varchar(4);
 BEGIN
   select add_months(period_number,-24) into fee from ff_header;
      for i in 1..24
 loop
 v_field := 'UM'||i;
 u_field := 'VM'||i;
 SELECT TO_CHAR(ADD_MONTHS(FEE,i),'YYMM')into mnth FROM DUAL;
 DBMS_OUTPUT.PUT_LINE(mnth);
 DBMS_OUTPUT.PUT_LINE(v_field);
 DBMS_OUTPUT.PUT_LINE(u_field);
 end loop;
end;
/
and this shows me exactly what I expect.

So, not knowing where I am going next, I tried changing the to_char(add_months) bit of the original PL/SQL:
Code: 
DECLARE
 fee date;
 v_field varchar2(4);
 u_field varchar2(4);
 mnth varchar(4);
 BEGIN
 	 select add_months(period_number,-24) into fee from ff_header;
      for i in 1..24
 loop
 v_field := 'UM'||i;
 u_field := 'VM'||i;
 SELECT TO_CHAR(ADD_MONTHS(FEE,i),'YYMM')into mnth FROM DUAL; 	
 insert into FF_board 
 (FF_AREA,FF_REGION,FF_TERRITORY,UGA_SECT,REGION,TERRITORY,BRICK,CEG_CODE,MNTH,UNIT,VAL,DATE_LOADED)
 VALUES
 (select A.AREA, A.REGION, A.TERRITORY, B.UGA_SECT, 
 B.REGION, B.TERRITORY, A.BRICK, 
 (SUBSTR(A.CEG_PROD_CODE, 2,13)),mnth,
 a.u_field, a.v_field, sysdate  from  ff_results a, ff_territory b 
 where a.brick = b.brick);
 end loop;
end;
and it is still telling me that the (SELECT A.AREA... line is the error
MSG:
ERROR at line 16:
ORA-06550: line 16, column 3:
PL/SQL: ORA-00936: missing expression
ORA-06550: line 13, column 2:
PL/SQL: SQL Statement ignored

AS I have tested these bits all individually, I can't see what bit doesn't work.

(Maybe it's time I went home! I just caught myself reading this and thinking 'Surely its An area, not A Area....)


Fee

The question should be [red]Is it worth trying to do?[/red] not [blue] Can it be done?[/blue]
 
Upvote 0 Downvote
Right, I've removed some of the errors by stripping some of the code away. I can't tell you I understand why this works, but now at least I have a different error:
Code: 
DECLARE
 fee date;
 v_field varchar2(6);
 u_field varchar2(6);
 mnth char(4);
 phil date;
 BEGIN
   select add_months(period_number,-24) into fee from ff_header;
      for i in 1..24
 loop
 v_field := 'A.UM'||i;
 u_field := 'A.VM'||i;
 SELECT ADD_MONTHS(FEE,i) into phil FROM DUAL;
 SELECT to_char(phil, 'YYMM') into mnth from dual;
 insert into FF_board
 select A.AREA, A.REGION, A.TERRITORY, B.UGA_SECT,
 B.REGION, B.TERRITORY, A.BRICK,
 (SUBSTR(A.CEG_PROD_CODE, 2,13)),
 mnth, u_field, v_field, sysdate  from  ff_results A, ff_territory B
 where a.brick = b.brick;
 end loop;
end;
/
and now I get the following error:
DECLARE
*
ERROR at line 1:
ORA-01722: invalid number
ORA-06512: at line 15

Now, from what I have read this implies I am trying to insert a non-number into a numeric field. So, I have manually ran the insert for every single month, and that has worked very happily, so I can't believe I am trying to do that.

If anyone has any suggestions as to what on earth is going on, I'd be ever so grateful.


Fee

The question should be [red]Is it worth trying to do?[/red] not [blue] Can it be done?[/blue]
 
Upvote 0 Downvote
Fee,

Could you please post your results of "DESCRIBE ff_board"?

[santa]Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
 
Upvote 0 Downvote
Code: 
PHARGREAVES>desc ff_board                                    
 Name                      Null?    Type                     
 ------------------------- -------- ------------------------ 
 FF_AREA                            CHAR(3)                  
 FF_REGION                          CHAR(3)                  
 FF_TERRITORY                       CHAR(3)                  
 UGA_SECT                           VARCHAR2(10)             
 REGION                             NUMBER                   
 TERRITORY                          NUMBER                   
 BRICK                              VARCHAR2(4)              
 CEG_CODE                           VARCHAR2(13)             
 MNTH                               CHAR(4)                  
 UNIT                               NUMBER                   
 VALUE                              NUMBER                   
 DATE_LOADED                        DATE

and incase it helps, this is the describe of the source table:
Code: 
PHARGREAVES>desc ff_results                        
 Name                       Null?    Type          
 ----------------------------------- --------------
 FR_TYPE                             CHAR(2)       
 AREA                                CHAR(3)       
 REGION                              CHAR(3)       
 TERRITORY                           CHAR(3)       
 GSU_GROUP                           CHAR(3)       
 BRICK                               VARCHAR2(4)   
 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


Fee

The question should be [red]Is it worth trying to do?[/red] not [blue] Can it be done?[/blue]
 
Upvote 0 Downvote
Fee, it's no help, but I used to work with a Peter Hargreaves. No relation I should think.

I want to be good, is that not enough?
 
Upvote 0 Downvote
No indeed - he's a Phil.

(and he's no help either!)

Fee

The question should be [red]Is it worth trying to do?[/red] not [blue] Can it be done?[/blue]
 
Upvote 0 Downvote
Hi, looking at this again this morning, I still believe that it is unnecessary to build this in a pl/sql loop.
Assuming that the numbers after the columns map as follows:

vm1 = April '04
vm2 = May '04
vm3 = June '04
...
vm12 = March '05
...
vm24 = March '06
This should be 'doable' in a single sql statement as follows:
Code: 
INSERT ALL
INTO FF_BOARD
VALUES (A.AREA
      , A.REGION
      , A.TERRITORY
      , B.UGA_SECT
      , B.REGION
      , B.TERRITORY
      , A.BRICK
      , (SUBSTR(A.CEG_PROD_CODE, 2,13))
      , TO_CHAR(ADD_MONTHS('01-Mar-04',1),'YYMM')
      , a.UM1
      , a.VM1
      , sysdate)
INTO FF_BOARD
VALUES (A.AREA
      , A.REGION
      , A.TERRITORY
      , B.UGA_SECT
      , B.REGION
      , B.TERRITORY
      , A.BRICK
      , (SUBSTR(A.CEG_PROD_CODE, 2,13))
      , '0404'
      , a.UM1
      , a.VM1
      , sysdate)
INTO FF_BOARD
VALUES (A.AREA
      , A.REGION
      , A.TERRITORY
      , B.UGA_SECT
      , B.REGION
      , B.TERRITORY
      , A.BRICK
      , (SUBSTR(A.CEG_PROD_CODE, 2,13))
      , '0405'
      , a.UM2
      , a.VM2
      , sysdate)
INTO FF_BOARD
VALUES (A.AREA
      , A.REGION
      , A.TERRITORY
      , B.UGA_SECT
      , B.REGION
      , B.TERRITORY
      , A.BRICK
      , (SUBSTR(A.CEG_PROD_CODE, 2,13))
      , '0406'
      , a.UM3
      , a.VM3
      , sysdate)
INTO FF_BOARD
VALUES (A.AREA
      , A.REGION
      , A.TERRITORY
      , B.UGA_SECT
      , B.REGION
      , B.TERRITORY
      , A.BRICK
      , (SUBSTR(A.CEG_PROD_CODE, 2,13))
      , '0306'
      , a.UM24
      , a.VM24
      , sysdate)
SELECT  A.AREA
      , A.REGION
      , A.TERRITORY
      , B.UGA_SECT
      , B.REGION
      , B.TERRITORY
      , A.BRICK
      , (SUBSTR(A.CEG_PROD_CODE, 2,13))
      , a.UM1
      , a.VM1
      , a.UM3
      , a.VM3
      , a.UM4
      , a.VM4
...
      , a.UM24
      , a.VM24
      , sysdate
 FROM  ff_results a
 JOIN ff_territory b
 ON  a.brick = b.brick
Obviously the ... represents the need for you to add the appropriate blocks for the missing months.

Now if you insist on using a pl/sql loop to do this, then use the loop to build a dynamic string (which would end up looking exactly the same as the statement above)and then execute the string using execute immediate. Do not execute this as multiple statements.

HTH
 
Upvote 0 Downvote
D&B couple of errors
Code: 
INSERT ALL
INTO FF_BOARD
VALUES (A.AREA
      , A.REGION
      , A.TERRITORY
      , B.UGA_SECT
      , B.REGION
      , B.TERRITORY
      , A.BRICK
      , cg
      , '0404'
      , a.UM1
      , a.VM1
      , sysdate)
INTO FF_BOARD
VALUES (A.AREA
      , A.REGION
      , A.TERRITORY
      , B.UGA_SECT
      , B.REGION
      , B.TERRITORY
      , A.BRICK
      , cg
      , '0405'
      , a.UM2
      , a.VM2
      , sysdate)
INTO FF_BOARD
VALUES (A.AREA
      , A.REGION
      , A.TERRITORY
      , B.UGA_SECT
      , B.REGION
      , B.TERRITORY
      , A.BRICK
      , cg
      , '0406'
      , a.UM3
      , a.VM3
      , sysdate)
INTO FF_BOARD
VALUES (A.AREA
      , A.REGION
      , A.TERRITORY
      , B.UGA_SECT
      , B.REGION
      , B.TERRITORY
      , A.BRICK
      , cg
      , '0407'
      , a.UM4
      , a.VM4
      , sysdate)
INTO FF_BOARD
VALUES (A.AREA
      , A.REGION
      , A.TERRITORY
      , B.UGA_SECT
      , B.REGION
      , B.TERRITORY
      , A.BRICK
      , (SUBSTR(A.CEG_PROD_CODE, 2,13))
      , '0603'
      , a.UM24
      , a.VM24
      , sysdate)
SELECT  A.AREA
      , A.REGION
      , A.TERRITORY
      , B.UGA_SECT
      , B.REGION
      , B.TERRITORY
      , A.BRICK
      , (SUBSTR(A.CEG_PROD_CODE, 2,13)) CG
      , a.UM1
      , a.VM1
      , a.UM3
      , a.VM3
      , a.UM4
      , a.VM4
...
      , a.UM24
      , a.VM24
      , sysdate
 FROM  ff_results a
 JOIN ff_territory b
 ON  a.brick = b.brick
 
Upvote 0 Downvote
Thanks for that Jim - I'll give that a go.

I don't really have a desperate desire to build this in a loop; I was just really trying to automate the date insertion, and I know this will end up being used by someone who has even less Oracle knowledge than me!



Fee

The question should be [red]Is it worth trying to do?[/red] not [blue] Can it be done?[/blue]
 
Upvote 0 Downvote
So this isn't a one off? How often will it be run? are you looking to do some for of change data capture or simply emptying the target table and reloading each time?
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

TheLazyPig
  • Locked
  • Question
Oracle Data Validation 1
Replies
4
Views
247
TheLazyPig
TheLazyPig
rk68
  • Locked
  • Question
inserting multi lingual data
Replies
0
Views
143
rk68
rk68
EM1107
  • Locked
  • Question
How to convert CLOB data into number and compare it to other table.
Replies
4
Views
637
carp
carp
Andrzejek
  • Locked
  • Question
Show value from DATA_DEFAULT field 1
Replies
2
Views
871
Andrzejek
Andrzejek
rk68
  • Locked
  • Question
Data fetch issue thru PL/SQL developer
Replies
4
Views
203
rk68
rk68

Part and Inventory Search

Sponsor

Back
Top