Re: Newbie needs help

Hi, Scott.

It seems like you're close on this first attempt. I don't think you need the sum or the group by. Try this:

CREATE OR REPLACE FUNCTION awt_ma

   (p_symbol IN VARCHAR2, p_seq_no IN NUMBER, p_len IN NUMBER)  

   RETURN NUMBER IS m_avg NUMBER;
BEGIN   Select AVG(close) into m_avg from awt_stock_price     where symbol = p_symbol
    and seq_no between p_seq_no and (p_seq_no - p_len);

  RETURN m_avg;

END awt_ma;
/

I think that should do it. If not, let me know what "this didn't work" means in terms of Oracle errors or getting a different result than you expect or what.

Hope this helps.
-Jeff Guttadauro

On 7 May 1999 15:55:25 GMT, Scott T. wrote:

>I am a newbie that needs help with writing a function. I have gotten
>stuck somewhere that I can't see at the moment. As you see I have
>tried many conotations, including cursor for loop not included here.
>I am just asking for some one to point the glasring errors so I can
>get on my way to learning.
>the function is to return a number value for a moving average
>
>CREATE TABLE awt_stock_price
>(Symbol varchar2(5)
>, Close_Date date
>, Seq_No number
>, Open number(8,4)
>, Hi number(8,4)
>, Lo number(8,4)
>, Close number(8,4)
>, Volume number(12)
>, PRIMARY KEY (Symbol, Close_Date));
>
>CREATE OR REPLACE function awt_ma
> (p_symbol in varchar2, p_seq_no in number
> ,p_len in number)
>
>RETURN NUMBER IS
>m_avg number;
>BEGIN
>Select avg(sum(close)) into m_avg From awt_stock_price
>Where symbol = p_symbol
>And seq_no Between p_seq_no And p_seq_no - p_len
>group by symbol;
>Return m_avg;
>END awt_ma;
>/
>
>
>this didn't work
>RETURN NUMBER IS
>BEGIN
>Select close into m_avg From awt_stock_price
>Where symbol = p_symbol
>And seq_no Between p_seq_no And p_seq_no - p_len;
>Return Avg(Close);
>END;
>
>this didn't work
>RETURN NUMBER IS
>m_avg number;
>BEGIN
>Select close into m_avg From awt_stock_price
>Where symbol = p_symbol
>And seq_no Between p_seq_no And p_seq_no - p_len;
>Return (m_avg = Avg(Close));
>END awt_ma;
>
>this didn't work
>CREATE OR REPLACE function awt_ma
> (p_symbol in varchar2, p_seq_no in number
> ,p_len in number)
>RETURN NUMBER IS
>m_avg number;
>BEGIN
>Select avg(sum(close)) into m_avg From awt_stock_price
>Where symbol = p_symbol
>And seq_no In (Select seq_no from awt_stock_price where seq_no between
>p_seq_no
> And p_seq_no - p_len)
>group by symbol;
>Return m_avg;
>END awt_ma;
>/
>
Received on Fri May 07 1999 - 13:54:47 CDT