Home » SQL & PL/SQL » SQL & PL/SQL » Dynamic while loop
Dynamic while loop [message #383709] Thu, 29 January 2009 18:44 Go to next message
ora_newbie111
Messages: 5
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 #383711 is a reply to message #383709] Thu, 29 January 2009 19:06 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
So we can help you & please be consistent & correct in your postings.

Post DDL for tables.
Post DML for test data.

Post expected/desired results.
Re: Dynamic while loop [message #383727 is a reply to message #383711] Thu, 29 January 2009 20:29 Go to previous messageGo to next message
ora_newbie111
Messages: 5
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 #383728 is a reply to message #383727] Thu, 29 January 2009 20:30 Go to previous messageGo to next message
ora_newbie111
Messages: 5
Registered: January 2009
Junior Member
however, I tried to reorganize the text...but it seem will be left alignment after preview.
Re: Dynamic while loop [message #383730 is a reply to message #383709] Thu, 29 January 2009 20:39 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
If you'd actually read the Posting Guidelines
http://www.orafaq.com/forum/t/88153/0/
in the section labeled:
How to format your post?


Please keep in mind that rows in a Table have NO inherent order.
In other words the terms 2nd, 3rd rows are meaningless.
Rows in a Table are like balls in a basket.
Which ball in the basket is the 1st ball & which ball is the next ball?

[Updated on: Thu, 29 January 2009 21:00]

Report message to a moderator

Re: Dynamic while loop [message #383731 is a reply to message #383727] Thu, 29 January 2009 21:03 Go to previous messageGo to next message
ora_newbie111
Messages: 5
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 #383735 is a reply to message #383709] Thu, 29 January 2009 22:18 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
UPDATE TEMP SET FLAG = NULL WHERE SEQ < 6;
Re: Dynamic while loop [message #383754 is a reply to message #383735] Thu, 29 January 2009 23:55 Go to previous messageGo to next message
ora_newbie111
Messages: 5
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 Go to previous message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
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
Previous Topic: Pass Checkbox Parameters from HTML Form to a stored procedure (merged)
Next Topic: oracle utility
Goto Forum:
  


Current Time: Fri Dec 09 00:12:01 CST 2016

Total time taken to generate the page: 0.12167 seconds