| Loop Problem [message #315405] |
Mon, 21 April 2008 07:19  |
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  |
Manoj.Gupta.91
Messages: 239
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 #315788 is a reply to message #315405] |
Tue, 22 April 2008 15:31 |
 |
Littlefoot
Messages: 21823
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 simplyINSERT 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; |
|
|
|
|
|
|
|
| Re: Loop Problem [message #317070 is a reply to message #315405] |
Tue, 29 April 2008 07:12 |
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 |
 |
djmartin
Messages: 10181
Registered: March 2005
Location: Surges Bay TAS 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
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
