| fetching records from one table and populating to multi record block [message #154120] |
Thu, 05 January 2006 15:35  |
vinodkumarn
Messages: 60
Registered: March 2005
|
Member |
|
|
I have a table A and now i am trying to fetch records from table A and populating into a multi record block based on table B. i have the following code. here i have to use all the block column names(almost 50) to assign values.
Is there any way where we can populate without naming the each block column names.
only difference between table A and table B is table A have one extra column called template_id and rest are same
PROCEDURE POPULATE_DETAILS IS
cursor cur_fetch is
select *
from A
where template_id=:parameter.template_id;
c cur_fetch%rowtype;
begin
go_block('procurement_action');
if :parameter.template_id is not null then
last_record;
next_record;
open cur_fetch;
loop
fetch cur_fetch into c;
exit when cur_fetch%notfound;
:B.fy := c.fy;
:B.code := c.code;
:B.nbr := c.nbr;
---
---
(like this i have around 50 columns.........)
next_record;
end loop;
first_record;
end if;
end;
Thanks
Vinod
|
|
|
|
|
|
| Re: fetching records from one table and populating to multi record block [message #154301 is a reply to message #154138] |
Fri, 06 January 2006 11:51  |
vinodkumarn
Messages: 60
Registered: March 2005
|
Member |
|
|
I think i did not write my question properly
The code i have written above is working perfectly fine. my question is there any other methods to do the same work, without assigning cursor values to each block.item name, bcos i have 50 columns to be populated and so i need to write this assignment statement from cursor value to block.item_name 50 times
is there anything where i can populate the entire row at a time and put it in the multi record block. all the column names from the table from where i am populating and the block.item names are same.
i hope i have told my question properly
Thanks
Vinod
|
|
|
|
| Re: fetching records from one table and populating to multi record block [message #154386 is a reply to message #154301] |
Sat, 07 January 2006 15:20 |
 |
Littlefoot
Messages: 21818
Registered: June 2005
Location: Croatia, Europe
|
Senior Member
Account Moderator |
|
|
Perhaps just another idea: do not use cursor at all, but insert data like this:
INSERT INTO table_b (col1, col2, ..., col50)
SELECT col1, col2, ..., col50
FROM table_a WHERE
template_id = :parameter.template_id;
Unfortunatelly, table_a <> table_b UNLESS you do the following (of course, if it is possible and won't cost you much later):
ALTER TABLE table_b ADD template_id datatype;
Then the first query is even easier to write:
INSERT INTO table_b SELECT * FROM table_a;
Now, having desired records in table_b, perform EXECUTE_QUERY in the form.
|
|
|
|
|
|
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
