I want to do something which i know can be done but How?
I was thinking of using a public variable, but cant seem to find how to declare one, fill it and (re-)use it in a query....
What i want to do is this:
I have a list/table of incomming product and a table of sales.
I would like to calculate the # in stock
I allready have a junction view:
ProdID, Date, In, Out
1,01-09-2004, 20, 15
1,02-09-2004, 25, 10
1,03-09-2004, 16, 18
1,04-09-2004, 11, 20
In the end it should have a stock count of 9 with this layout:
ProdID, Date, In, Out, InStock
1,01-09-2004, 20, 15, 5
1,02-09-2004, 25, 10, 20
1,03-09-2004, 16, 18, 18
1,04-09-2004, 11, 20, 9
This might just sound like Homework, but let me assure you its not. Due to changes at the office i have had move from Access to Oracle, tho i am familiar and can manage most tasks (with some help of the forum and books) i cant seem to find how to work this.... In access its simple ... But now do it in Oracle
sum(stock_in-stock_out) over (partition by prodid order by stock_date)
This example gives the cumulative sum of (stock_in-stock_out) for each record, in order of stock_date, and within prodid (partition clause). i.e. the sum resets to 0 when the prodid changes from 1 to 2.
BTW, I have previously posted the code for a package to generate pivot queries here For the example above you would run this:
SQL> exec pivot.print_pivot_query('DEPTNO','JOB','EMP',1)
, sum(DECODE(JOB,'ANALYST', 1)) as "ANALYST"
, sum(DECODE(JOB,'CLERK', 1)) as "CLERK"
, sum(DECODE(JOB,'MANAGER', 1)) as "MANAGER"
, sum(DECODE(JOB,'SALESMAN', 1)) as "SALESMAN"
group by DEPTNO
order by DEPTNO
PL/SQL procedure successfully completed.