Home » SQL & PL/SQL » SQL & PL/SQL » extract pairs
extract pairs [message #433801] Fri, 04 December 2009 05:45 Go to next message
joshua82
Messages: 31
Registered: December 2009
Member
Hi!
I have a problem with PL/SQL. I need to do a subtraction between pairs of values in a column.
Like this:

VALUES_MARKET
12
13
15
18
17
18

and create a table like this:

RESULT
1
3
1

(13-12=1; 18-15=3 and so on..). I'm creating a function but I don't know how..
Thanks!
Re: extract pairs [message #433803 is a reply to message #433801] Fri, 04 December 2009 05:46 Go to previous messageGo to next message
Its_me_ved
Messages: 979
Registered: October 2009
Location: India
Senior Member
Search for LAG/LEAD
Re: extract pairs [message #433809 is a reply to message #433803] Fri, 04 December 2009 06:08 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
LAG/LEAD leaves you with some fiddly processing to handle first and last values.
I'd do it with First_Value and Last_Value:
create table test_105  (ord number, VALUES_MARKET number);
insert into test_105 values (1,12);
insert into test_105 values (2,13);
insert into test_105 values (3,15);
insert into test_105 values (4,18);
insert into test_105 values (5,17);
insert into test_105 values (6,18);

select first_val,last_val
from (select first_value(values_market) over (partition by grp order by ord rows between unbounded preceding and unbounded following) first_val
            ,last_value(values_market) over (partition by grp order by ord rows between unbounded preceding and unbounded following)  last_val
            ,row_number() over (partition by grp order by ord) rnum
      from  (select ord
                   ,values_market
                   ,ceil(row_number() over (order by ord)/2) grp
             from  test_105))
where rnum = 1;
Re: extract pairs [message #433930 is a reply to message #433809] Sat, 05 December 2009 02:46 Go to previous messageGo to next message
Frank
Messages: 7901
Registered: March 2000
Senior Member
I was thinking about another solution.
Since the problem only involves pairs, you would not be bothered by last & first rows.
What about something like this? (Note: used JRowbottom's testcase)
SQL> select diff
  2  from   (select values_market - lag(values_market) over (order by ord) as diff
  3          ,      rownum as rn
  4          from   test_105
  5          order  by ord
  6         )
  7  where  mod(rn, 2) = 0
  8  order  by rn
  9  /

      DIFF
----------
         1
         3
         1

[Edit: Added order by in main query]

[Updated on: Sat, 05 December 2009 02:47]

Report message to a moderator

Re: extract pairs [message #434095 is a reply to message #433930] Mon, 07 December 2009 03:06 Go to previous messageGo to next message
joshua82
Messages: 31
Registered: December 2009
Member
Thanks to all.
I solved with Frank's solution.
Re: extract pairs [message #434098 is a reply to message #433930] Mon, 07 December 2009 03:20 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Elegant solution.
Re: extract pairs [message #434430 is a reply to message #434098] Wed, 09 December 2009 04:51 Go to previous messageGo to next message
joshua82
Messages: 31
Registered: December 2009
Member
I resume this topic because I have a problem with Frank's solution: I create a function like this:

select diff from(
select valmer - lag(valmer) over (order by num) as diff, rownum as rn
        from   (select valmer, rownum num 
               from (select a_function(valmer,x) as valmer 
                     from derivative 
                     where cod = '7155' group by valmer))
               order  by num
               )
 where  mod(rn, 2) = 0
 order  by rn;


where a_function is execute for every values in valmer which return a number and x is a constant defined before.
This situation creates an error when I'm compiling..

[Updated on: Wed, 09 December 2009 04:52]

Report message to a moderator

Re: extract pairs [message #434431 is a reply to message #434430] Wed, 09 December 2009 04:52 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
And that error would be.......
Re: extract pairs [message #434433 is a reply to message #434431] Wed, 09 December 2009 04:55 Go to previous messageGo to next message
joshua82
Messages: 31
Registered: December 2009
Member
No, is not a compiling error, but when I'm testing return: "not a GROUP BY expression"
Re: extract pairs [message #434434 is a reply to message #434431] Wed, 09 December 2009 04:56 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Come to that, knowing what a_function does would probably help.
Re: extract pairs [message #434437 is a reply to message #434434] Wed, 09 December 2009 05:06 Go to previous messageGo to next message
joshua82
Messages: 31
Registered: December 2009
Member
a_function takes valmer (is a stock valuation) and x and return the price of correlated finance option (for everyday stock valuation).

If I do this in a new window
select a_function(valmer,3) as valmer from derivative 
where cod = '7155' group by valmer 

this return:

VALMER
------
6,12
3,77
2,35
Re: extract pairs [message #434438 is a reply to message #434437] Wed, 09 December 2009 05:08 Go to previous messageGo to next message
joshua82
Messages: 31
Registered: December 2009
Member
and then I need the difference: 3.77 - 6.12, and so on..
Re: extract pairs [message #434439 is a reply to message #434437] Wed, 09 December 2009 05:09 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Quote:
This situation creates an error when I'm compiling..


And the error would be.......
Re: extract pairs [message #434444 is a reply to message #434439] Wed, 09 December 2009 05:17 Go to previous messageGo to next message
joshua82
Messages: 31
Registered: December 2009
Member
ORA-00979: not a GROUP BY expression

Re: extract pairs [message #434450 is a reply to message #434444] Wed, 09 December 2009 05:35 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
The structure doesn't error on 10.2.0.4

SQL> 
SQL> create or replace function a_function (p_in  number,p_in2 number) return number as
  2  begin
  3    return p_in;
  4  end;
  5  /

Function created.

SQL> 
SQL> create table test_derivative (valmer number, cod  varchar2(4));

Table created.

SQL> 
SQL> insert into test_derivative values (1,'7155');

1 row created.

SQL> insert into test_derivative values (2,'7155');

1 row created.

SQL> insert into test_derivative values (3,'7155');

1 row created.

SQL> 
SQL> select diff from(
  2  select valmer - lag(valmer) over (order by num) as diff, rownum as rn
  3          from   (select valmer, rownum num 
  4                 from (select a_function(valmer,3) as valmer 
  5                       from test_derivative 
  6                       where cod = '7155' group by valmer))
  7                 order  by num
  8                 )
  9   where  mod(rn, 2) = 0
 10   order  by rn;

      DIFF
----------
         1
Re: extract pairs [message #434547 is a reply to message #434430] Wed, 09 December 2009 11:59 Go to previous message
Frank
Messages: 7901
Registered: March 2000
Senior Member
Since the only function the group by performs in your (oversimplified?) query is to remove duplicates, try to replace it with distinct.
Previous Topic: table row lock question (merged)
Next Topic: Regarding Procedure Troubleshooting
Goto Forum:
  


Current Time: Sat Feb 08 07:57:04 CST 2025