Home » SQL & PL/SQL » SQL & PL/SQL » Using variable to Update columns in a loop
Using variable to Update columns in a loop [message #288799] Tue, 18 December 2007 14:11 Go to next message
monasingh
Messages: 229
Registered: May 2006
Location: Mumbai
Senior Member

Hi,
I have a table where there 52 qty columns named as p1_qty, p2_qty...p52_qty. Each column representing the sum of qty for one week.
I have to update the table for the 52 qty columns by doing the SUM of qty for a week.
I want to use something like a loop wherein I can put the Update statement. I am using the column name p1_qty...p52_qty by a variable named v_num.

  1   DECLARE
  2   v_num VARCHAR2(10) := 0;
  3   v_wk  NUMBER := 0;
  4   v_clause varchar(200) ;
  5   BEGIN
  6   v_num := 1;
  7   FOR i IN 1..52
  8   LOOP
  9   v_wk := v_wk + 7;
 10   v_num := 'p'||i||'_qty';
 11   UPDATE springs.si_sop_aggr_fgsr c
 12                 SET c.TO_NUMBER(v_num) = (
 13                              SELECT SUM(b.qty/1000) qty
 14                                FROM springs.si_sop_fgitem a,
 15                                     springs.si_sop_fgsr b
 16                               WHERE a.item = b.item
 17                                 AND c.sbu = a.division
 18                                 AND c.customer = a.sopcust
 19                                 AND c.program = a.program
 20                                 AND c.sopgrp = a.sopgrp
 21                                 AND c.cloth_type = a.sopclothtype
 22                                 AND c.fin_type = a.color_group
 23                                 AND c.fin_size = a.sized
 24                                 AND c.mfg_type = a.sopmfgtype
 25                                 AND c.prod_type = a.product_type
 26                                 AND b.scheddate between sysdate and (sysdate + v_wk)
 27                               GROUP BY c.sbu, c.customer, c.program, c.sopgrp,
 28                                     c.cloth_type, c.fin_type, c.fin_size,
 29                                     c.mfg_type, c.prod_type);
 30   END LOOP;
 31*  END;
 32  /
                               AND c.program = a.program
                                       *
ERROR at line 19:
ORA-06550: line 12, column 31:
PL/SQL: ORA-00927: missing equal sign
ORA-06550: line 11, column 2:
PL/SQL: SQL Statement ignored


Please help me on this and let me know if my process of solving this problem is correct.

Thanks,
Mona
Re: Using variable to Update columns in a loop [message #288800 is a reply to message #288799] Tue, 18 December 2007 14:23 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Use "execute immediate".
Of course you know this a bad design.
Of course you know some years have days in 53 weeks.

Regards
Michel
Re: Using variable to Update columns in a loop [message #288807 is a reply to message #288800] Tue, 18 December 2007 14:57 Go to previous messageGo to next message
monasingh
Messages: 229
Registered: May 2006
Location: Mumbai
Senior Member

  1   DECLARE
  2   v_num VARCHAR2(10) := 0;
  3   v_wk  NUMBER := 0;
  4   v_clause varchar(200) ;
  5   BEGIN
  6   v_num := 1;
  7   FOR i IN 1..52
  8   LOOP
  9   v_wk := v_wk + 7;
 10   v_num := 'p'||i||'_qty';
 11   execute immediate 'UPDATE springs.si_sop_aggr_fgsr c
 12                 SET c.TO_NUMBER(v_num) = (
 13                              SELECT SUM(b.qty/1000) qty
 14                                FROM springs.si_sop_fgitem a,
 15                                     springs.si_sop_fgsr b
 16                               WHERE a.item = b.item
 17                                 AND c.sbu = a.division
 18                                 AND c.customer = a.sopcust
 19                                 AND c.program = a.program
 20                                 AND c.sopgrp = a.sopgrp
 21                                 AND c.cloth_type = a.sopclothtype
 22                                 AND c.fin_type = a.color_group
 23                                 AND c.fin_size = a.sized
 24                                 AND c.mfg_type = a.sopmfgtype
 25                                 AND c.prod_type = a.product_type
 26                                -- AND b.scheddate between sysdate and (sysdate + v_wk)
 27                               GROUP BY c.sbu, c.customer, c.program, c.sopgrp,
 28                                     c.cloth_type, c.fin_type, c.fin_size,
 29                                     c.mfg_type, c.prod_type)';
 30   END LOOP;
 31*  END;
SQL> /
 DECLARE
*
ERROR at line 1:
ORA-00927: missing equal sign
ORA-06512: at line 11

Please suggest me a better design.

Re: Using variable to Update columns in a loop [message #288814 is a reply to message #288799] Tue, 18 December 2007 15:23 Go to previous messageGo to next message
joy_division
Messages: 4644
Registered: February 2005
Location: East Coast USA
Senior Member
monasingh wrote on Tue, 18 December 2007 15:11

H
  1   DECLARE
  2   v_num VARCHAR2(10) := 0;
  3   v_wk  NUMBER := 0;
  4   v_clause varchar(200) ;
  5   BEGIN
  6   v_num := 1;
  7   FOR i IN 1..52
  8   LOOP
  9   v_wk := v_wk + 7;
 10   v_num := 'p'||i||'_qty';
 11   UPDATE springs.si_sop_aggr_fgsr c
 12                 SET c.TO_NUMBER(v_num) = (
 13                              SELECT SUM(b.qty/1000) qty

etc.



This does not make sense. UPDATE is used to update a column in a table. This is is updating a variable, and a TO_NUMBER of a variable at that (not sure what that means). Additionally, if you were using a TO_NUMBER of a column, it would be TO_NUMBER(alias.column) not alias.TO_NUMBER(column)

[Updated on: Tue, 18 December 2007 15:25]

Report message to a moderator

Re: Using variable to Update columns in a loop [message #288815 is a reply to message #288807] Tue, 18 December 2007 15:31 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SET c.'||v_num||' = (

A better design: a new column indicating the week, one qty per row.

Regards
Michel
Re: Using variable to Update columns in a loop [message #289031 is a reply to message #288815] Wed, 19 December 2007 05:31 Go to previous messageGo to next message
monasingh
Messages: 229
Registered: May 2006
Location: Mumbai
Senior Member

Hi,
I have executed the attached code...it updating the table but taking very long time.

[code]
Please advice

Mona

[Updated on: Wed, 19 December 2007 05:58]

Report message to a moderator

Re: Using variable to Update columns in a loop [message #289038 is a reply to message #289031] Wed, 19 December 2007 05:55 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
The query or the loop?

Regards
Michel
Re: Using variable to Update columns in a loop [message #289059 is a reply to message #289038] Wed, 19 December 2007 06:58 Go to previous messageGo to next message
monasingh
Messages: 229
Registered: May 2006
Location: Mumbai
Senior Member

Sorry missed the code
DECLARE
v_num VARCHAR2(10) := 0;
v_wk  NUMBER := 0;
BEGIN
v_num := 1;
FOR i IN 1..52
LOOP
v_wk := v_wk + 7;
v_num := 'p'||i||'_qty';
EXECUTE IMMEDIATE 'UPDATE springs.si_sop_aggr_fgsr c
              SET '||v_num||' = (
                           SELECT SUM(b.qty/1000) qty
                             FROM springs.si_sop_fgitem a,
                                  springs.si_sop_fgsr b
                            WHERE a.item = b.item
                              AND c.sbu = a.division
                              AND c.customer = a.sopcust
                              AND c.program = a.program
                              AND c.sopgrp = a.sopgrp
                              AND c.cloth_type = a.sopclothtype
                              AND c.fin_type = a.color_group
                              AND c.fin_size = a.sized
                              AND c.mfg_type = a.sopmfgtype
                              AND c.prod_type = a.product_type
                              AND b.scheddate between sysdate and (sysdate + '||v_wk||')
                            GROUP BY c.sbu, c.customer, c.program, c.sopgrp,
                                  c.cloth_type, c.fin_type, c.fin_size,
                                  c.mfg_type, c.prod_type)';
END LOOP;
END;


This code runs and gives required value but is quite slow. When I ran it only for 2 times instead of 52 times, it took around 4 minutes.

Please suggest something so that this can be faster.

Thanks,
Mona
Re: Using variable to Update columns in a loop [message #289064 is a reply to message #289059] Wed, 19 December 2007 07:30 Go to previous message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Try to use only one SQL statement, something like:
UPDATE springs.si_sop_aggr_fgsr c
SET (p1_qty, p2_qty, ..., p52_qty) = 
( SELECT 
SUM(case b.scheddate between sysdate and sysdate+1*7 then b.qty end/1000),
SUM(case b.scheddate between sysdate and sysdate+2*7 then b.qty end/1000),
...
SUM(case b.scheddate between sysdate and sysdate+52*7 then b.qty end/1000) 
                             FROM springs.si_sop_fgitem a,
                                  springs.si_sop_fgsr b
                            WHERE a.item = b.item
                              AND c.sbu = a.division
                              AND c.customer = a.sopcust
                              AND c.program = a.program
                              AND c.sopgrp = a.sopgrp
                              AND c.cloth_type = a.sopclothtype
                              AND c.fin_type = a.color_group
                              AND c.fin_size = a.sized
                              AND c.mfg_type = a.sopmfgtype
                              AND c.prod_type = a.product_type
GROUP BY c.sbu, c.customer, c.program, c.sopgrp,
                                  c.cloth_type, c.fin_type, c.fin_size,
                                  c.mfg_type, c.prod_type)

Regards
Michel

[Updated on: Wed, 19 December 2007 08:17]

Report message to a moderator

Previous Topic: Date Selection
Next Topic: Pls help -Multiple sub units with the same name
Goto Forum:
  


Current Time: Sun Dec 11 02:39:36 CST 2016

Total time taken to generate the page: 0.08393 seconds