CODEWITHSUNDEEP
oracle-databasefunctionplsqloracle10goracle-apex
Bishan
Bishan

Reputation: 15702

Pass parameters automatically to oracle plsql function

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 ?

Upvotes: 1

Views: 415

Answers (1)

A.B.Cade
A.B.Cade

Reputation: 16905

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

Upvotes: 2

Related Questions