Home » SQL & PL/SQL » SQL & PL/SQL » Dynamic while loop
| Dynamic while loop [message #383709] |
Thu, 29 January 2009 18:44  |
ora_newbie111
Messages: 11
Registered: January 2009
|
Junior Member |
|
|
I am newbie in PL/SQL development. Currently I am practising on the while loop. But I have face a problem. I am going to show you the sample data as following:
Intial data:
<Seq> <Group> <Code> <Flag>
1 AB3 A001 Y
2 AB1 A001
3 AB1 A002
4 AB2 A002 Y
5 AB2 A003 Y
6 AB4 A004 Y
7 AB4 A005 Y
Expected result after checking:
<Seq> <Group> <Code> <Flag>
1 AB3 A001
2 AB1 A001
3 AB1 A002
4 AB2 A002
5 AB2 A003
6 AB4 A004 Y
7 AB4 A005 Y
Refer from Initial data, second and third records are being unflag in initial stage. 4th record will be unflagged as well due to third record with code A002 being being unflagged. 5th record will being unflagged as well due to same group with 4th record.
1st record will be unflagged due to second record with code A001 being unflagged. 6th & 7th record should remain as original due to there is no any record with code A004, A005 being unflagged in other group.
The logic should select those record which are unflagged, from there, check the code and search from the table any records with same code, then unflag it, once one of the record being unflaged, unflag all records with same category.
I have tried to do it, but only can do until the level 1 which is unflagged the 4th and 5th record. Or all the records being unflagged which is not what I want. Any of you can help this scenario...thanks a lot.
|
|
|
|
|
|
| Re: Dynamic while loop [message #383727 is a reply to message #383711] |
Thu, 29 January 2009 20:29  |
ora_newbie111
Messages: 11
Registered: January 2009
|
Junior Member |
|
|
I am newbie in PL/SQL development. Currently I am practising on the while loop. But I have face a problem. I am going to show you the sample data as following:
Table name: temp
<Seq> <Group> <Code> <Flag>
1 AB3 A001 Y
2 AB1 A001
3 AB1 A002
4 AB2 A002 Y
5 AB2 A003 Y
6 AB4 A004 Y
7 AB4 A005 Y
Table name: grp_code
<loop> <group> <code>
2 AB2 A002
2 AB2 A003
Table name: code
<group> <code>
AB2 A002
AB2 A003
Expected result after checking: temp
<Seq> <Group> <Code> <Flag>
1 AB3 A001
2 AB1 A001
3 AB1 A002
4 AB2 A002
5 AB2 A003
6 AB4 A004 Y
7 AB4 A005 Y
PL/SQL code:
CURSOR cur_not_flag IS
SELECT * FROM temp
WHERE (flag = ' ' OR flag IS null);
CURSOR cur_flag IS
SELECT * FROM temp
WHERE (flag = 'Y' OR flag IS NOT null);
CURSOR cur_grp_code IS
SELECT * FROM grp_code;
CURSOR cur_code IS
SELECT * FROM code;
1 BEGIN
2 count := 1;
3 loop := 'Y';
4 BEGIN
5 FOR rec_not_flag IN cur_not_flag
6 LOOP
7 INSERT INTO grp_code VALUES (count, rec_not_flag.group,rec_not_flag.code);
8 END LOOP;
9 EXCEPTION
10 WHEN NO_DATA_FOUND THEN
11 NULL;
12 END;
-------------------------------
BEGIN
13 FOR rec_grp_code IN cur_grp_code
LOOP
14 FOR rec_flag IN cur_flag
15 LOOP
16 UPDATE temp SET flag = ' '
17 WHERE code = rec_grp_code.code
18 AND flag = 'Y';
19 DELETE FROM grp_code
20 WHERE code = rec_grp_code.code
21 AND group = rec_grp_code.group;
22 INSERT INTO grp_code VALUES(count,rec_flag.code,rec_flag.group);
23 INSERT INTO code VALUES (rec_flag.code, rec_flag.group);
24 END LOOP;
25 FOR rec_code IN cur_code
26 LOOP
27 UPDATE temp SET flag = ' '
28 WHERE group = rec_code.group
29 AND code = rec_code.code;
30 INSERT INTO grp_code VALUES (count, rec_code.code, rec_code.grp_code);
31 END LOOP;
32 END LOOP;
33 END;
Line 1-12 : select those record which are unflagged to grp_code table
Line 13: open the grp_code table, update the temp table to unflag the record which is same code with those record that point to grp_code table.
line 22: insert the group code to the code table
line 27: update the temp file and unflag the whole batch of the group if one of the code is being unflagged.
line 30: for those record being unflagged, insert the record to the grp_code table, and do the looping again, to unflag all related code from temp table
Table name: temp
<Seq> <Group> <Code> <Flag>
1 AB3 A001 Y
2 AB1 A001
3 AB1 A002
4 AB2 A002 Y
5 AB2 A003 Y
6 AB4 A004 Y
7 AB4 A005 Y
Expected result after checking: temp
<Seq> <Group> <Code> <Flag>
1 AB3 A001
2 AB1 A001
3 AB1 A002
4 AB2 A002
5 AB2 A003
6 AB4 A004 Y
7 AB4 A005 Y
Refer from Initial data, second and third records are being unflag in initial stage. 4th record will be unflagged as well due to third record with code A002 being being unflagged. 5th record will being unflagged as well due to same group with 4th record.
1st record will be unflagged due to second record with code A001 being unflagged. 6th & 7th record should remain as original due to there is no any record with code A004, A005 being unflagged in other group.
The logic should select those record which are unflagged, from there, check the code and search from the table any records with same code, then unflag it, once one of the record being unflaged, unflag all records with same category.
I have tried to do it, but only can do until the level 1 which is unflagged the 4th and 5th record. Or all the records being unflagged which is not what I want. Any of you can help this scenario...thanks a lot.
|
|
|
|
|
|
|
|
| Re: Dynamic while loop [message #383731 is a reply to message #383727] |
Thu, 29 January 2009 21:03 |
ora_newbie111
Messages: 11
Registered: January 2009
|
Junior Member |
|
|
I am newbie in PL/SQL development. Currently I am practising on the while loop. But I have face a problem. I am going to show you the sample data as following:
Table name: temp
<Seq> <Group> <Code> <Flag>
1 AB3 A001 Y
2 AB1 A001
3 AB1 A002
4 AB2 A002 Y
5 AB2 A003 Y
6 AB4 A004 Y
7 AB4 A005 Y
Table name: grp_code
<loop> <group> <code>
2 AB2 A002
2 AB2 A003
Table name: code
<group> <code>
AB2 A002
AB2 A003
Expected result after checking: temp
<Seq> <Group> <Code> <Flag>
1 AB3 A001
2 AB1 A001
3 AB1 A002
4 AB2 A002
5 AB2 A003
6 AB4 A004 Y
7 AB4 A005 Y
PL/SQL code:
CURSOR cur_not_flag IS
SELECT * FROM temp
WHERE (flag = ' ' OR flag IS null);
CURSOR cur_flag IS
SELECT * FROM temp
WHERE (flag = 'Y' OR flag IS NOT null);
CURSOR cur_grp_code IS
SELECT * FROM grp_code;
CURSOR cur_code IS
SELECT * FROM code;
1 BEGIN
2 count := 1;
3 loop := 'Y';
4 BEGIN
5 FOR rec_not_flag IN cur_not_flag
6 LOOP
7 INSERT INTO grp_code VALUES (count, rec_not_flag.group,rec_not_flag.code);
8 END LOOP;
9 EXCEPTION
10 WHEN NO_DATA_FOUND THEN
11 NULL;
12 END;
-------------------------------
BEGIN
13 FOR rec_grp_code IN cur_grp_code
LOOP
14 FOR rec_flag IN cur_flag
15 LOOP
16 UPDATE temp SET flag = ' '
17 WHERE code = rec_grp_code.code
18 AND flag = 'Y';
19 DELETE FROM grp_code
20 WHERE code = rec_grp_code.code
21 AND group = rec_grp_code.group;
22 INSERT INTO grp_code VALUES(count,rec_flag.code,rec_flag.group);
23 INSERT INTO code VALUES (rec_flag.code, rec_flag.group);
24 END LOOP;
25 FOR rec_code IN cur_code
26 LOOP
27 UPDATE temp SET flag = ' '
28 WHERE group = rec_code.group
29 AND code = rec_code.code;
30 INSERT INTO grp_code VALUES (count, rec_code.code, rec_code.grp_code);
31 END LOOP;
32 END LOOP;
33 END;
Line 1-12 : select those record which are unflagged to grp_code table
Line 13: open the grp_code table, update the temp table to unflag the record which is same code with those record that point to grp_code table.
line 22: insert the group code to the code table
line 27: update the temp file and unflag the whole batch of the group if one of the code is being unflagged.
line 30: for those record being unflagged, insert the record to the grp_code table, and do the looping again, to unflag all related code from temp table
Table name: temp
<Seq> <Group> <Code> <Flag>
1 AB3 A001 Y
2 AB1 A001
3 AB1 A002
4 AB2 A002 Y
5 AB2 A003 Y
6 AB4 A004 Y
7 AB4 A005 Y
Expected result after checking: temp
<Seq> <Group> <Code> <Flag>
1 AB3 A001
2 AB1 A001
3 AB1 A002
4 AB2 A002
5 AB2 A003
6 AB4 A004 Y
7 AB4 A005 Y
Refer from Initial data, second and third records are being unflag in initial stage. 4th record will be unflagged as well due to third record with code A002 being being unflagged. 5th record will being unflagged as well due to same group with 4th record.
1st record will be unflagged due to second record with code A001 being unflagged. 6th & 7th record should remain as original due to there is no any record with code A004, A005 being unflagged in other group.
The logic should select those record which are unflagged, from there, check the code and search from the table any records with same code, then unflag it, once one of the record being unflaged, unflag all records with same category.
I have tried to do it, but only can do until the level 1 which is unflagged the 4th and 5th record. Or all the records being unflagged which is not what I want. Any of you can help this scenario...thanks a lot.
[Updated on: Fri, 30 January 2009 00:28] by Moderator Report message to a moderator |
|
|
|
|
|
| Re: Dynamic while loop [message #383754 is a reply to message #383735] |
Thu, 29 January 2009 23:55 |
ora_newbie111
Messages: 11
Registered: January 2009
|
Junior Member |
|
|
Cannot be hard coded that to unflag those record that less than sequence number 6. The looping should be like this
1) select those record which are unflagged,
2) from there, check the code and search from the table any records with same code, then unflag it.
3) once one of the record being unflaged, unflag all records with same group.
with this reason, the logic should not be hard coded.
|
|
|
|
| Re: Dynamic while loop [message #383764 is a reply to message #383731] |
Fri, 30 January 2009 00:35 |
 |
Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
| Quote: | The logic should select those record which are unflagged, from there, check the code and search from the table any records with same code, then unflag it, once one of the record being unflaged, unflag all records with same category.
|
What is a category, I only see seq, group, code and flag.
Post a Test case: create table and insert statements along with the result you want with these data.
Regards
Michel
|
|
|
|
Goto Forum:
Current Time: Mon Feb 17 15:32:13 CST 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
