Home » SQL & PL/SQL » SQL & PL/SQL » How to achieve this ?
How to achieve this ? [message #351131] Mon, 29 September 2008 07:33 Go to next message
rak007
Messages: 107
Registered: October 2006
Location: Mumbai / Pune, India
Senior Member
This query may seem to be simple for some but i am not able to get how to achieve this. The scene is that i have a Lines table (say tab_lines) having 70 to 80 columns(approx). Now in this table i have a few columns starting at col8 to col65 (these are actual column names). Now my problem is that i need to look at the values in each of these columns (i.e. 8 to 65) for a value and do processing accordingly. This has to be done for each row in the table.

In gist, in need to check the value of each of the columns from col8 to col65 for each row in the tab_lines table. How do i do the looping to achieve this ? I have a rough idea that it will be nested loops wherein i will make a cursor that will select all the rows from the tab_lines table and open this cursor in a FOR loop. But after this i dont know how to loop through each of the columns (i.e. col8 to col65) in this row.

I have a lines table tab_lines which has 70 - 80 cols

The table structure is some what like this

Header_Id number;
Line_Id number;
col8 number;
col9 number;
.......
col65 number;

Now i want to loop through all the columns (col8 to col65) in the table tab_lines for each of the row. Its something like this

For each row in tab_lines loop

     Loop through col 8 to col65
          <insert data into some xyz table> --this is processing
     end loop

end loop


So effectively on the basis of each value that i retrieve i need to insert data in a particular table. This is what i meant by processing. And i need to select the data for each of the cols (8 to 65) for each of the row. I mean for a single row there will be 57 times looping (65-8) and i will need to insert 57 rows in table xyz for each row in tab_lines
Re: How to achieve this ? [message #351133 is a reply to message #351131] Mon, 29 September 2008 07:41 Go to previous messageGo to next message
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
Senior Member
Why you wan't to do it by a cursor when this can be done
by a single insert with select statement.

insert into
 <targettable> select col8,..col65 from <sourcetable>


Or you arr so lazy to type the column names
then use some dynamic sql and make this statement
by querying user_tab_columns table.

Regards,
Rajat
Re: How to achieve this ? [message #351142 is a reply to message #351133] Mon, 29 September 2008 08:49 Go to previous messageGo to next message
rak007
Messages: 107
Registered: October 2006
Location: Mumbai / Pune, India
Senior Member
The table where i will be isnerting the data is a completely different table and i just need the values of the cols from col8 to col65 to derive to a conclusion whether i need to do the isnertion or not. I do not have to insert the values of col8 to col65
Re: How to achieve this ? [message #351144 is a reply to message #351131] Mon, 29 September 2008 09:16 Go to previous messageGo to next message
Michel Cadot
Messages: 64133
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What you want is to convert "columns to rows", search for this or for "unpivot".

Regards
Michel
Re: How to achieve this ? [message #351147 is a reply to message #351144] Mon, 29 September 2008 09:21 Go to previous messageGo to next message
rak007
Messages: 107
Registered: October 2006
Location: Mumbai / Pune, India
Senior Member
No i do not want to convert columns to rows.

I want to do some processing based on the values from the col8 to col65 of the table tab_lines for each row. Later on i will insert some totally unrelated data in a different table.
Re: How to achieve this ? [message #351153 is a reply to message #351147] Mon, 29 September 2008 09:29 Go to previous messageGo to next message
Michel Cadot
Messages: 64133
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Post an example, your requirements are clear as mud.
You don't need an example with 60 columns, post one with 3.

Regards
Michel
Re: How to achieve this ? [message #351158 is a reply to message #351153] Mon, 29 September 2008 09:41 Go to previous messageGo to next message
rak007
Messages: 107
Registered: October 2006
Location: Mumbai / Pune, India
Senior Member
Example:

Table structure of raks

col1      number;
col2      number;
col3      number;


declare
   l_value varchar2(100);
     
   CURSOR cr_1 
   IS
   SELECT * from raks;

BEGIN
FOR cr_1_rec IN cr_1 LOOP
    --I dont know how to make the below loop work for each of
    --the colums 1 to 3 for each row retrieved by the cursor cr_1
    FOR i in 1..3 loop
        --Here i need to retrieve the value of col(from 1 to 3)
        --so that i can use this data as a basis for my 
        --processing purpose

        --This stmt will print the values for reference and i
        -- i intend to take the output in l_value variable
        dbms_output.put_line('l_value: ' || l_value); 
    END LOOP;
END loop;   
end;

[Updated on: Mon, 29 September 2008 09:44]

Report message to a moderator

Re: How to achieve this ? [message #351163 is a reply to message #351158] Mon, 29 September 2008 09:55 Go to previous message
Michel Cadot
Messages: 64133
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I don't want your pseudo-code, I want the requirements.
Post create table, insert statements for input table. Create table statement for the output table and what should be inside after execution.

Regards
Michel
Previous Topic: how to remove distinct key word from the table
Next Topic: Reading explain plans
Goto Forum:
  


Current Time: Thu Dec 08 04:06:17 CST 2016

Total time taken to generate the page: 0.38489 seconds