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  |
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 #288807 is a reply to message #288800] |
Tue, 18 December 2007 14:57   |
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   |
joy_division
Messages: 4963 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 #289059 is a reply to message #289038] |
Wed, 19 December 2007 06:58   |
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  |
 |
Michel Cadot
Messages: 68737 Registered: March 2007 Location: Saint-Maur, France, https...
|
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
|
|
|
Goto Forum:
Current Time: Sat Feb 15 16:23:07 CST 2025
|