| How to add the value to different columns? [message #325059] |
Wed, 04 June 2008 23:09  |
svloke
Messages: 23
Registered: May 2007
Location: Mumbai
|
Junior Member |
|
|
During the execution of explicit cursor, I need to insert the returned value of the same query into different columns.
The column names are v1_50, v2_50,.....v10_50. First value in col v1_50, the second value in the same row in col v2_50, the third value in col v3_50 of same row and so on.
Please guide.
svloke
|
|
|
|
|
|
| Re: How to add the value to different columns? [message #325139 is a reply to message #325121] |
Thu, 05 June 2008 02:16  |
svloke
Messages: 23
Registered: May 2007
Location: Mumbai
|
Junior Member |
|
|
Dear Littlefoot,
The need of explicit cursor arises because there are some calculations to be inserted to a column, based upon the user enterd values, before generating a report.
The parameters for the report are passed from objects on the form(in the oracle form builder and Not parameter form in the oracle report builder). the cursor is to be executed from the form object(on oracle form builder) and the returned values are to be inserted in the temp table. Then from that temp table using a simple SELECT statment, the report is to be generated.
I hope you have understood my problem.
svloke
[Updated on: Thu, 05 June 2008 02:17] Report message to a moderator |
|
|
|
| Re: How to add the value to different columns? [message #325172 is a reply to message #325139] |
Thu, 05 June 2008 04:22 |
 |
Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe
|
Senior Member
Account Moderator |
|
|
OK, so it seems that | LF | create a loop and insert cursor record values into a table.
|
might, actually, work. Something like this:
FOR cur_r IN (SELECT c1, c2, c3 FROM some_table WHERE condition)
LOOP
INSERT INTO temp_table
(col1, col2, col3)
VALUES
(cur_r.c1, cur_r.c2, cur_r.c3);
END LOOP;
Although, if it is possible, use pure SQL INSERT statement and avoid cursor completely, such as
INSERT INTO temp_table
(col1, col2, col3)
(SELECT c1, c2, c3
FROM some_table
WHERE condition
); |
|
|
|
|
|
|
|
| Re: How to add the value to different columns? [message #325204 is a reply to message #325193] |
Thu, 05 June 2008 05:13 |
svloke
Messages: 23
Registered: May 2007
Location: Mumbai
|
Junior Member |
|
|
I have already tried to modify it sir. The code I used was
INSERT INTO sam_rep2
(v1_100, v2_50)
(SELECT v1_100, v2_50
FROM sam_rep1)
I didn't use any WHERE condition.
The v1_100 and v2_50 columns in both the tables are exactly same.
svloke
|
|
|
|
| Re: How to add the value to different columns? [message #325236 is a reply to message #325204] |
Thu, 05 June 2008 06:12 |
|
Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe
|
Senior Member
Account Moderator |
|
|
Your query says: take all v1_100 and v2_50 columns from table "sam_rep1" and put them into v1_100 and v2_50 columns in "sam_rep2" table.
Shortly:
sam_rep1 sam_rep2
-------- --------
v1_100 -> v1_100
v2_50 -> v2_50 | Quote: | The v1_100 and v2_50 columns in both the tables are exactly same.
|
Yes, and it is expected as your INSERT statement does exactly what you've said.
If that's NOT expected behaviour, what would you like to do, then?
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
