Pass parameters automatically to oracle plsql function

Question

I have wrote two plsql functions for Stock and Sales Comparison. one as find_prodcuts_sold and other as find_usage_from_stock.

I can get different between stock and sales by decrease find_prodcuts_sold function from find_usage_from_stock function. for this i should pass From date and To date to these two functions. (From date and To date are taken from stock_date column in stock table). then my functions return values for given date range.

Now i want to create a line chart using my functions to get different between stock and slaes. chart should build automatically. with out user pass From date and To date.

Example stock_date column from stock table.

stock_date

30-JAN-12
26-JAN-12
24-JAN-12
23-JAN-12
18-JAN-12
15-JAN-12
13-JAN-12
12-JAN-12
11-JAN-12
08-JAN-12
06-JAN-12

I want to pass above dates as below to my functions automatically.

From        To
26-JAN-12   30-JAN-12
24-JAN-12   26-JAN-12
23-JAN-12   24-JAN-12
18-JAN-12   23-JAN-12
15-JAN-12   18-JAN-12
13-JAN-12   15-JAN-12
12-JAN-12   13-JAN-12
11-JAN-12   12-JAN-12
08-JAN-12   11-JAN-12
06-JAN-12   08-JAN-12

how could i do this ?

A.B.Cade · Accepted Answer · 2012-01-31 09:45:08Z

2

You can use LAG or LEAD analytic functions:

select stock_date, lead(stock_date, 1, null) over (order by stock_date) next_date
from stock_table

then use the result of the query for your input, i.e.:

SELECT find_usage_from_stock(t.product_id, t.start_date, t.end_date) as usage_from_stock, 
       find_prodcuts_sold(t.product_id, t.start_date, t.end_date) as prodcuts_sold, 
       t.product_id, t.start_date
FROM (select stock_date as start_date, 
             lead(stock_date, 1, null) over (order by stock_date) as end_date, 
             product_id
      from stock_table) t

Note: I used null as the empty value in the lead function, perhaps you'll need to put something else

edited Jan 31, 2012 at 9:45

answered Jan 31, 2012 at 6:44

A.B.Cade

16.9k1 gold badge39 silver badges55 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

Bishan Over a year ago

A.B.Cade, your answer is correct. but i have no idea about how to pass dates return from your answer as parameters to my functions. how can i do that ? there are two functions as below. find_usage_from_stock(p_id,from_date,to_date); find_prodcuts_sold(p_id,from_date,to_date);

A.B.Cade Over a year ago

how are you running these function ? using a script (plsql) ? creating a DB view ? basiccaly you can use them in a select statement, ie- select find_usage_from_stock(p_id,t.stock_date,t.next_date) from (select stock_date, lead(stock_date, 1, null) over (order by stock_date) next_date from stock_table) t

Bishan Over a year ago

A.B.Cade, i use my functions to create chart in oracle apex.

A.B.Cade Over a year ago

@Bishan, I'm not familiar with oracle apex, but from what i've seen in some tutorials, you can build a chart from a sql query. If I'm right then the example in my prev comment can be a start (I didn't know what to put in p_id)

Bishan Over a year ago

A.B.Cade, Thanks for your prev example. My question is not solve yet. but your prev example gave me a start to my work. sorry for the p_id :) it is product_id

|

Collectives™ on Stack Overflow

Pass parameters automatically to oracle plsql function

1 Answer 1

8 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Related