Home » Developer & Programmer » Reports & Discoverer » How to add the value to different columns?
How to add the value to different columns? [message #325059] Wed, 04 June 2008 23:09 Go to next message
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 #325121 is a reply to message #325059] Thu, 05 June 2008 01:32 Go to previous messageGo to next message
Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Is your question Reports or Discoverer related? I don't know Discoverer, but - where do you execute this "explicit cursor"? In one of reports' triggers? Why did you decide to do it that way? I mean, what's wrong with an ordinary SQL query as report's data source? Why would you need a cursor?

What "columns" are you talking about? Table columns? If so, does it mean that you'd like to INSERT these values into a database? As you use a cursor, create a loop and insert cursor record values into a table.

I'm afraid I have no idea what you are talking about /forum/fa/1600/0/
Re: How to add the value to different columns? [message #325139 is a reply to message #325121] Thu, 05 June 2008 02:16 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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
  );
It will be more efficient than any PL/SQL solution. Once again - if it is possible.
Re: How to add the value to different columns? [message #325184 is a reply to message #325172] Thu, 05 June 2008 04:44 Go to previous messageGo to next message
svloke
Messages: 23
Registered: May 2007
Location: Mumbai
Junior Member
Sorry!
its not working. When I tried it, the selected columns were inserted into some_table as they are in the parent table.
Re: How to add the value to different columns? [message #325193 is a reply to message #325184] Thu, 05 June 2008 04:58 Go to previous messageGo to next message
Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
That's what you said, isn't it?
Quote:
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.

Anyway, what prevents you to modify my code and adjust it according to your needs?
Re: How to add the value to different columns? [message #325204 is a reply to message #325193] Thu, 05 June 2008 05:13 Go to previous messageGo to next message
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 Go to previous message
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?
Previous Topic: Discoverer Viewer cannot DRILL TO LINK
Next Topic: Page not displaying
Goto Forum:
  


Current Time: Mon Dec 02 08:47:22 CST 2024