Home » SQL & PL/SQL » SQL & PL/SQL » loop with condition
loop with condition [message #434127] Mon, 07 December 2009 05:41 Go to next message
joshua82
Messages: 31
Registered: December 2009
Member
Hi!
I'm in a situation like this:

COD
------
7150
C454
L780
..and so on (n rows in table code).

I need to do a loop like this:
FOR i IN 1..n LOOP
do something (a SELECT) on another table but use consistent code (for example when i is 1, I use cod = 7150, when i = 2, I use cod = C454, ..). Example: SELECT * FROM another_table WHERE cod_other_table = cod(rownum = i)
END LOOP;

Is it possible?
Thanks
Re: loop with condition [message #434130 is a reply to message #434127] Mon, 07 December 2009 05:59 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Is this what you're looking for?
If not, be sure to explain why not in slightly more detail than you went into in your original post.

BEGIN
  FOR REC IN (select rownum i 
                    ,COD
              from   code_table) LOOP
    <do things in loop for each value of i>

  END LOOP;
END;


Re: loop with condition [message #434132 is a reply to message #434127] Mon, 07 December 2009 06:01 Go to previous messageGo to next message
Michel Cadot
Messages: 64098
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1/ A table is a heap and as no order -> you have to define an order to get the rows from your source table otherwise you'll get undeterministic result

2/ Depending of what you want to do, you don't have to loop on the first query, a single one may be sufficient.

You have to explain in more details what you want to achieve.

Regards
Michel
Re: loop with condition [message #434143 is a reply to message #434132] Mon, 07 December 2009 07:42 Go to previous messageGo to next message
joshua82
Messages: 31
Registered: December 2009
Member
I try to be more detailed: I have a table called 'val' like this:

DATE || CODE || VALUE

12-07-09 || C7272 || 1.80
12-07-09 || C1500 || 2.20
12-07-09 || E1313 || 5.20
12-04-09 || C7272 || 1.75
12-04-09 || C1500 || 2.25
12-04-09 || E1313 || 5.17
and so on...where code represents a stock (there are n different codes, but this number changes during time).

I need to do a loop and for every different code, I do some operations on value (a simple example is a variance(value))

[Updated on: Mon, 07 December 2009 07:44]

Report message to a moderator

Re: loop with condition [message #434148 is a reply to message #434143] Mon, 07 December 2009 08:08 Go to previous messageGo to next message
Michel Cadot
Messages: 64098
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You do not need to loop on a cursor to calculate a variance, Oracle has a built-in function for this.

Regards
Michel
Re: loop with condition [message #434151 is a reply to message #434143] Mon, 07 December 2009 08:12 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Quote:
I need to do a loop and for every different code, I do some operations on value (a simple example is a variance(value))


Assuming the 'somethings' are things that you can't easily do in SQL, then the skeleton solution I posted should do.
Re: loop with condition [message #434155 is a reply to message #434130] Mon, 07 December 2009 08:36 Go to previous messageGo to next message
joshua82
Messages: 31
Registered: December 2009
Member
Ok! But in this section

JRowbottom wrote on Mon, 07 December 2009 05:59

<do things in loop for each value of i>


I need to use the code which is related to i..but how?
For example: if i = 1 is related to code = 7150 but how can I do
SELECT variance(value) WHERE code = ???


Thanks
Re: loop with condition [message #434156 is a reply to message #434155] Mon, 07 December 2009 08:37 Go to previous messageGo to next message
Michel Cadot
Messages: 64098
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If things can be done in SQL you do not need to loop.
If things cannot be done in SQL then use JRowbottom's skeleton.

Regards
Michel
Re: loop with condition [message #434158 is a reply to message #434156] Mon, 07 December 2009 09:15 Go to previous messageGo to next message
joshua82
Messages: 31
Registered: December 2009
Member
BEGIN
  FOR rec IN (select rownum i from (select distinct(code) from val)) 
  LOOP
  select variance(value) into single_variance 
from val 
where code = (select code from (select code, rownum num  
from (select distinct(code)  from val)) 
  where num = rec);  ---the compilation error is here

  insert into table10 values (single_variance); 
  END LOOP;


Compilation error is: <<Error: PLS-00382: expression is of wrong type>>
I'm going crazy! Laughing

[Updated on: Mon, 07 December 2009 10:10] by Moderator

Report message to a moderator

Re: loop with condition [message #434160 is a reply to message #434158] Mon, 07 December 2009 09:16 Go to previous messageGo to next message
BlackSwan
Messages: 25032
Registered: January 2009
Location: SoCal
Senior Member
It make no sense to compare a scalar value to a record.

Re: loop with condition [message #434163 is a reply to message #434160] Mon, 07 December 2009 09:26 Go to previous messageGo to next message
joshua82
Messages: 31
Registered: December 2009
Member
BlackSwan wrote on Mon, 07 December 2009 09:16
It make no sense to compare a scalar value to a record.

I know.
If I replace "rec" with "2", everything is ok! But what I need to do is to associate num with a value that change during the loop..
Re: loop with condition [message #434165 is a reply to message #434163] Mon, 07 December 2009 09:28 Go to previous messageGo to next message
BlackSwan
Messages: 25032
Registered: January 2009
Location: SoCal
Senior Member
BEGIN
  FOR rec IN (select rownum i from (select distinct(code) from val)) 
  LOOP
  select variance(value) into single_variance 
from val 
where code = (select code 
from (select code, rownum num  from (select distinct(code)  
from val)) 
  where num = rec.val2);  -- where val2 is a scalar NUMBER field in rec

  insert into table10 values (single_variance); 
  END LOOP;

[Updated on: Mon, 07 December 2009 10:12] by Moderator

Report message to a moderator

Re: loop with condition [message #434167 is a reply to message #434158] Mon, 07 December 2009 09:41 Go to previous messageGo to next message
cookiemonster
Messages: 12403
Registered: September 2008
Location: Rainy Manchester
Senior Member
joshua82 wrote on Mon, 07 December 2009 15:15
BEGIN
  FOR rec IN (select rownum i from (select distinct(code) from val)) 
  LOOP
  select variance(value) into single_variance 
from val 
where code = (select code 
from (select code, rownum num  
from (select distinct(code)  from val)) 
  where num = rec);  ---the compilation error is here

  insert into table10 values (single_variance); 
  END LOOP;


Compilation error is: <<Error: PLS-00382: expression is of wrong type>>
I'm going crazy! Laughing


That code doesn't make a lot of sense. Without an order by on both queries you not might get any data back from that since the rownums from the first query could correspond to completely different codes from the second query.

Is this what you're trying to do:
BEGIN

  FOR rec IN (select distinct(code) from val) LOOP

    select variance(value) 
    into single_variance 
    from val 
    where code = rec.code
    AND ROWNUM = 1;

    insert into table10 values (single_variance); 
  
END LOOP;

Ie get a random variance for each distinct code?

If not you need to explain your problem in more detail because your code above isn't going to work.

[Updated on: Mon, 07 December 2009 10:11] by Moderator

Report message to a moderator

Re: loop with condition [message #434168 is a reply to message #434167] Mon, 07 December 2009 09:50 Go to previous messageGo to next message
joshua82
Messages: 31
Registered: December 2009
Member
As you probably undestand I'm a newbie about Oracle. I appreciate yours patience.
I'm creating a function to do this: where must I declare the record rec?
Re: loop with condition [message #434169 is a reply to message #434158] Mon, 07 December 2009 09:52 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
BEGIN
  FOR rec IN (select rownum i from (select distinct(code) from val)) 
  LOOP
  select variance(value) into single_variance 
from val 
where code = (select code 
from (select code, rownum num  from (select distinct(code)  
from val)) 
  where num = rec);  ---the compilation error is here

  insert into table10 values (single_variance); 
  END LOOP;


Truely, my mind boggles at the thought processes that led to this.

You need to go and read up on cursor for loops.
In the meantime, try this:
BEGIN
  FOR rec IN (select rownum i
                    ,code
              from (select distinct(code) code 
                    from   val)) LOOP

  select variance(value) into single_variance 
  from val 
  where code = rec.code;  ---the compilation error is here

  insert into table10 values (single_variance); 
  END LOOP;


You'd be even better off doing it in a single sql statement:

INSERT INTO table10 
select  variance(value)
from    val
group by code;

[Updated on: Mon, 07 December 2009 10:11] by Moderator

Report message to a moderator

Re: loop with condition [message #434170 is a reply to message #434168] Mon, 07 December 2009 09:54 Go to previous messageGo to next message
BlackSwan
Messages: 25032
Registered: January 2009
Location: SoCal
Senior Member
>I'm creating a function to do this
Too bad for us that you have failed to clearly define/explain exactly what "this" is.

You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
Go to the URL above click the link "Posting Guidelines"
Go to the section labeled "Practice" & do as directed.

What are the inputs?
What is the desired/expected results?
What is the specific transformation that need to occur to take input & generate results?
Re: loop with condition [message #434179 is a reply to message #434168] Mon, 07 December 2009 10:16 Go to previous message
joshua82
Messages: 31
Registered: December 2009
Member
joshua82 wrote on Mon, 07 December 2009 09:50
As you probably undestand I'm a newbie about Oracle. I appreciate yours patience.

I'm quoting myself.

As JRowbottom said, it can be solved with a simple group by. Thanks to everybody
Previous Topic: Number of unique keys in the table
Next Topic: If Statement / Variable as an SQL Column name
Goto Forum:
  


Current Time: Fri Dec 02 12:17:23 CST 2016

Total time taken to generate the page: 0.10415 seconds