Home » Developer & Programmer » Forms » Loop Problem (Forms,6i,XP)
Loop Problem [message #315405] Mon, 21 April 2008 07:19 Go to next message
gozuhair
Messages: 206
Registered: January 2008
Senior Member
Dear All

I have a sno (seriel no) column and weekday column in z1 table that contain 1000 records ,I have one more empty table name as "cust_info" that contain the same field sno (serial no) and weekday.(same datatype of sno and weekday between both table)

I want to insert "weekday" column data from z1 table into cust_info table through form by matching sno of z1,so i coded the following loop statement.


go_block('z1');
execute_query;
go_block('cust_info');
first_record;

Begin

	
	 	FOR I IN(
	 	select weekday
    from z1
    where sno=:z1.sno
    )
        LOOP
       :cust_info.weekday:=I.weekday;

       NEXT_RECORD;
       END LOOP;
       first_record;
     


Exception
	When no_data_found then
	next_block;
End;




In the above code only 1st record display and not displaying all records against sno.
Re: Loop Problem [message #315429 is a reply to message #315405] Mon, 21 April 2008 08:17 Go to previous messageGo to next message
Manoj.Gupta.91
Messages: 217
Registered: March 2008
Location: Delhi
Senior Member
Hi

what I understand from user problem is u have 2 tables Z1 and CUST_INFO


Z1
SNO WEEKDAY
1 SUNDAY
2 MONDAY
3 TUESDAY


CUST_INFO
SNO WEEKDAYS
1
2
3


u can try this .............


go_block('z1');
execute_query;

after this
write the following code in a button pressed

go_block('cust_info');
execute_query;

first_record;

WHILE TRUE LOOP

SELECT WEEKDAY
INTO :cust_info.weekday
FROM Z1
WHERE SNO = :CUST_INFO.SNO ;

EXIT WHEN :SYSTEM.LAST_RECORD = 'TRUE' ;

NEXT_RECORD ;

END LOOP ;

FIRST_RECORD ;


Although I don't recommend this solution as this can be achieved through SQL UPDATE statement


Re: Loop Problem [message #315612 is a reply to message #315405] Tue, 22 April 2008 03:26 Go to previous messageGo to next message
gozuhair
Messages: 206
Registered: January 2008
Senior Member
Thanks for the reply but still facing the same problem,my cursor still blink at first column.Now I am attatching my error message screen.


  • Attachment: error.JPG
    (Size: 52.83KB, Downloaded 130 times)
Re: Loop Problem [message #315788 is a reply to message #315405] Tue, 22 April 2008 15:31 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Quote:
I have one more empty table name as "cust_info"

If "cust_info" is empty, why don't you simply
INSERT INTO cust_info (sno, weekday)
  SELECT sno, weekday FROM z1;


Unless, of course, "empty table" doesn't mean the same for you and me.

On the other hand, if your tables look like as @manoj91 suggested (which we, of course, don't know as you refuse to follow OraFAQ Forum Guide's suggestions about providing CREATE TABLE and INSERT INTO sample data statements), there's no need to code it in such a "complicated" way; just as an example: create both blocks, write two triggers: the first one is WHEN-NEW-FORM-INSTANCE form-level trigger:
GO_BLOCK('z1');
EXECUTE_QUERY;

GO_BLOCK('cust_info');
EXECUTE_QUERY;

The second one is POST-QUERY "cust_info" block-level trigger:
SELECT MAX(z.weekday)
  INTO :cust_info.weekday
  FROM z1 z
  WHERE z.sno = :cust_info.sno;
(I've used the MAX function just to avoid possible NO-DATA-FOUND or TOO-MANY-ROWS error (which *should* be handled in a different manner - using the exception handler section; but, I guess that for testing purposes it will do the job).
Re: Loop Problem [message #315859 is a reply to message #315788] Wed, 23 April 2008 00:46 Go to previous messageGo to next message
gozuhair
Messages: 206
Registered: January 2008
Senior Member
Thanks its working,but i am unable to understand why this thing is not handled through loop statement.
Re: Loop Problem [message #316910 is a reply to message #315859] Mon, 28 April 2008 19:36 Go to previous messageGo to next message
djmartin
Messages: 10180
Registered: March 2005
Location: Canberra ACT Australia
Senior Member
Account Moderator
Have you solved your problem?

David
Re: Loop Problem [message #317070 is a reply to message #315405] Tue, 29 April 2008 07:12 Go to previous messageGo to next message
gozuhair
Messages: 206
Registered: January 2008
Senior Member
Dear David

I have solved the problem by using post query trigger on data block level but i am very much interested to know how can i perform this by using Loop statement.


Regards



Zuhair
Re: Loop Problem [message #317188 is a reply to message #317070] Tue, 29 April 2008 20:35 Go to previous message
djmartin
Messages: 10180
Registered: March 2005
Location: Canberra ACT Australia
Senior Member
Account Moderator
This looks like a master-detail relationship (please confirm). You are using Oracle Forms, not VB or Delphi or Access. Using a 'loop' in Oracle Forms is UNUSUAL. Don't fight the product and it will be less likely to bite your ass off.

David
Previous Topic: Multilingual Form
Next Topic: resetting serial number
Goto Forum:
  


Current Time: Thu Dec 08 18:28:30 CST 2016

Total time taken to generate the page: 0.10655 seconds