New to Oracle. Help with Function Syntax 2

[rose4567]

I wrote a function in SQL that returns a table of running numbers and totals. I don't know if it's possible to return a table in oracle or not. I know there are some differences between Oracle and SQL syntax so I started to modify my SQL function and pasted below. I am hoping someone can assist with the syntax.

Basically, I am trying to create a temporary table where I can count and sum. With the SQL Function I was able to call to the Max(TotalDollar) field in a subquery, but my output was a table. As I said, I'm not sure I can use "TABLE" as a return type in Oracle so all I really need is an OUTPUT of Max(TotalDollar) so long as I can call to the Function with the parameters I specified.

Any help is greatly appreciated!!
______________________________________________________

--MY ATTEMPT AT THE ORACLE SYNTAX--

CREATE FUNCTION fnFIFO (site IN varchar2, stock IN varchar2)
RETURNS NUMBER(15,5)
curRunningTotal IS CURSOR
Report IS
( tran_ts DATE,
site_id VARCHAR2,
stock_item_nbr VARCHAR2,
unit_cost NUMBER(15,5),
qty INTEGER,
qty_on_hand INTEGER,
RunningTotal INTEGER,
TotalDollar NUMBER(15,5)
);
curRunningTotal IS CURSOR
basequery IS SELECT IV_TRANSACTIONS.TRAN_TS, IV_TRANSACTIONS.SITE_ID, IV_TRANSACTIONS.STOCK_ITEM_NBR,
COALESCE (SUM(IV_TRANSACTIONS.UNIT_COST), 0) unit_cost,
SUM(IV_TRANSACTIONS.QUANTITY - COALESCE (IV_TRANSACTIONS.RECEIPT_QTY_ISSUED, 0)) qty,
COALESCE((SELECT SUM(QTY_ON_HAND) Expr1
FROM STOCK_ITEM_LOCATIONS
WHERE (SITE_ID = site) AND (STOCK_ITEM_NBR = stock)
GROUP BY SITE_ID, STOCK_ITEM_NBR), 0) qty_on_hand
FROM IV_TRANSACTIONS
WHERE IV_TRANSACTIONS.SITE_ID = site and IV_TRANSACTIONS.STOCK_ITEM_NBR = stock
GROUP BY IV_TRANSACTIONS.TRAN_TS, IV_TRANSACTIONS.SITE_ID, IV_TRANSACTIONS.STOCK_ITEM_NBR
ORDER BY IV_TRANSACTIONS.TRAN_TS ASC, IV_TRANSACTIONS.SITE_ID, IV_TRANSACTIONS.STOCK_ITEM_NBR;

tran_ts DATE;
site_id VARCHAR2;
stock_item_nbr VARCHAR2;
unit_cost NUMBER(15,5);
qty INTEGER;
qty_on_hand INTEGER;
RunningTotal INTEGER :=0;
TotalDollar NUMBER(15,5) :=0;

BEGIN OPEN basequery;
FETCH basequery
INTO tran_ts,
site_id,
stock_item_nbr,
unit_cost,
qty,
qty_on_hand;
OPEN curRunningTotal;
FETCH NEXT FROM curRunningTotal;
LOOP EXIT WHEN RunningTotal <= qty_on_hand;
IF RunningTotal >= qty_on_hand
THEN TotalDollar := TotalDollar + ((qty - (RunningTotal - qty_on_hand)) * unit_cost)
ELSE
TotalDollar := TotalDollar + (qty * unit_cost);
INSERT Report
VALUES(tran_ts, site_id, stock_item_nbr, unit_cost, qty, qty_on_hand, RunningTotal, TotalDollar)
FETCH NEXT FROM curRunningTotal
INTO tran_ts,
site_id,
stock_item_nbr,
unit_cost,
qty,
qty_on_hand
END LOOP;
CLOSE curRunningTotal;
RETURN Max(TotalDollar);
END fnFIFO;
_______________________________________________

--HERE IS THE FUNCTIONING SQL SYNTAX--

CREATE FUNCTION fnFIFO
(
@site char(5),
@stock char(10)
)
RETURNS
@Report TABLE
(
tran_ts DATETIME,
site_id CHAR(5),
stock_item_nbr CHAR(20),
unit_cost NUMERIC(15,5),
qty INT,
qty_on_hand INT,
RunningTotal INT,
TotalDollar NUMERIC(15,5)
)
AS
BEGIN
DECLARE curRunningTotal CURSOR LOCAL FAST_FORWARD FOR
SELECT IV_TRANSACTIONS.TRAN_TS, IV_TRANSACTIONS.SITE_ID, IV_TRANSACTIONS.STOCK_ITEM_NBR, COALESCE (SUM(IV_TRANSACTIONS.UNIT_COST), 0) AS unit_cost,
SUM(IV_TRANSACTIONS.QUANTITY - COALESCE
(IV_TRANSACTIONS.RECEIPT_QTY_ISSUED, 0)) AS qty,
COALESCE((SELECT SUM(QTY_ON_HAND) AS Expr1
FROM STOCK_ITEM_LOCATIONS
WHERE (SITE_ID = @site) AND
(STOCK_ITEM_NBR = @stock)
GROUP BY SITE_ID, STOCK_ITEM_NBR), 0) AS qty_on_hand
FROM IV_TRANSACTIONS
WHERE IV_TRANSACTIONS.SITE_ID = @site and IV_TRANSACTIONS.STOCK_ITEM_NBR = @stock
GROUP BY IV_TRANSACTIONS.TRAN_TS, IV_TRANSACTIONS.SITE_ID, IV_TRANSACTIONS.STOCK_ITEM_NBR
ORDER BY IV_TRANSACTIONS.TRAN_TS ASC, IV_TRANSACTIONS.SITE_ID, IV_TRANSACTIONS.STOCK_ITEM_NBR
;
OPEN curRunningTotal
DECLARE @tran_ts DATETIME
DECLARE @site_id CHAR(5)
DECLARE @stock_item_nbr CHAR(20)
DECLARE @unit_cost NUMERIC(15,5)
DECLARE @qty INT
DECLARE @qty_on_hand INT
DECLARE @RunningTotal INT
DECLARE @TotalDollar NUMERIC(15,5)
SET @RunningTotal = 0
SET @TotalDollar = 0
FETCH NEXT FROM curRunningTotal
INTO @tran_ts,
@site_id,
@stock_item_nbr,
@unit_cost,
@qty,
@qty_on_hand
WHILE @@FETCH_STATUS = 0 and @RunningTotal <= @qty_on_hand
BEGIN
SET @RunningTotal = @RunningTotal + @qty
IF @RunningTotal >= @qty_on_hand
SET @TotalDollar = @TotalDollar + ((@qty - (@RunningTotal - @qty_on_hand)) * @unit_cost)
ELSE
SET @TotalDollar = @TotalDollar + (@qty * @unit_cost)
INSERT @Report
VALUES(@tran_ts, @site_id, @stock_item_nbr, @unit_cost, @qty, @qty_on_hand, @RunningTotal, @TotalDollar)
FETCH NEXT FROM curRunningTotal
INTO @tran_ts,
@site_id,
@stock_item_nbr,
@unit_cost,
@qty,
@qty_on_hand
END
CLOSE curRunningTotal
DEALLOCATE curRunningTotal
RETURN
;
END
GO

 

[SantaMufasa]

Rose,

What was the Oracle instance's response to your Oracle version of the code? Did you receive errors? If so, were the errors:[ul][li]Syntax errors - Oracle took issue with the way you stated your code.[/li][li]Run-time errors - Oracle had no problem with your syntax, but it abnormally terminated during execution of your syntactically correct code.[/li][li]Logic Errors -- Your results did not match your expectations.[/li][/ul]Can you please post images of the Oracle's unwanted responses?

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[rose4567]

An empty result box. And the Function was not created. No error message in the bottom of the screen. I think the syntax is just all jacked up so TOAD doesn't have a clue what to do with it.

 

[Thargy]

rose,

I wonder if you could explain why you're creating a temporary table, and what you're trying to do with your function - this seems like quite a complex beastie for a newcomer.

If you are able to post a create table statement, some insert statements to populate said table with data, and state the desired output, we might be able to better understand and simplify the problem.

Regards

T

 

[SantaMufasa]

Amen to what Tharg says, Rose.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[rose4567]

DATE SITE STOCK UNIT QTY QTY Running Running
COST onhand count Total
2007-09-18 12:31 ADMN 100 2.18 10 73 10 21.80
2007-09-25 12:04 ADMN 100 2.18 12 73 22 47.96
2007-11-16 08:47 ADMN 100 2.18 1 73 23 50.14
2008-02-05 09:28 ADMN 100 2.18 10 73 33 71.94
2008-05-29 10:57 ADMN 100 2.18 11 73 44 95.92
2008-06-03 15:28 ADMN 100 2.78 20 73 64 151.52
2008-06-25 11:09 ADMN 100 2.78 10 73 74 154.30 <---


I am trying to write a loop statement. I'm creating a temporary table to store "running count" of qty and "running total" dollar.

It's for an inventory calculation called "FIFO" where you look at dates and calculate dollar value of items in date ascending order.

I multiply qty by unit_cost for each row, keeping an running total of qty and dollar as I go, until qty equals qty_on_hand (or until it just exceeds it). If the last row exceeds qty_on_hand I only need to calculate part of the qty to reach the qty_on_hand number. That's why I have the If/Then/Else portion written in. If qty is less than qty_on_hand I am multiplying the whole qty. If it just exceeds it, I multiply only to the part of the running total = qty_on_hand. So in this case, I only want to calculate up until my qty of 73 is met.

Thanks so much. I really appreciate you both taking the time to work through this with me.

In the sample above (this is output from my SQL function if I select the table as my return) 73 is the number I am totaling up to. So you can see in the last row the running total actually exceeds 73 to that's why I was doing an if/then/else so in the case it goes over I perform this part of the calculation:
@TotalDollar + ((@RunningTotal - @qty_on_hand) * @unit_cost)
So the last row in the sample above is really doing this:
(151.52 + ((74-73) * 2.78) = 154.30

Resulting in 154.30 as the final running dollar amount.

(and that said, this calculation down here is slightly modified because I found in testing my other calculation was incorrect. I was ending up multiplying the last line by 9 instead of 1).

 

[Thargy]

Rose,

Below, I've posted what I believe to be a correct create table statement and insert statements for the data you provided. Can you please run them and confirm that they're ok.

DROP TABLE ROSE_TEST CASCADE CONSTRAINTS PURGE;

CREATE TABLE ROSE_TEST
(
 ACTIVITY_DATE    DATE,
 SITE             VARCHAR2(4),
 STOCK            INTEGER,
 UNIT_PRICE       NUMBER(3,2),
 QUANTITY         INTEGER
);

INSERT INTO ROSE_TEST (ACTIVITY_DATE,SITE,STOCK,UNIT_PRICE,QUANTITY) 
VALUES (TO_DATE('2007-09-18 12:31','YYYY-MM-DD HH24:MI'),'ADMN',100,2.18,10);

INSERT INTO ROSE_TEST (ACTIVITY_DATE,SITE,STOCK,UNIT_PRICE,QUANTITY) 
VALUES (TO_DATE('2007-09-25 12:04','YYYY-MM-DD HH24:MI'),'ADMN',100,2.18,12);

INSERT INTO ROSE_TEST (ACTIVITY_DATE,SITE,STOCK,UNIT_PRICE,QUANTITY)
VALUES (TO_DATE('2007-11-16 08:47','YYYY-MM-DD HH24:MI'),'ADMN',100,2.18,1);

INSERT INTO ROSE_TEST (ACTIVITY_DATE,SITE,STOCK,UNIT_PRICE,QUANTITY)
VALUES (TO_DATE('2008-02-05 09:28','YYYY-MM-DD HH24:MI'),'ADMN',100,2.18,10);

INSERT INTO ROSE_TEST (ACTIVITY_DATE,SITE,STOCK,UNIT_PRICE,QUANTITY)
VALUES (TO_DATE('2008-05-29 10:57','YYYY-MM-DD HH24:MI'),'ADMN',100,2.18,11);

INSERT INTO ROSE_TEST (ACTIVITY_DATE,SITE,STOCK,UNIT_PRICE,QUANTITY)
VALUES (TO_DATE('2008-06-03 15:28','YYYY-MM-DD HH24:MI'),'ADMN',100,2.18,20);

INSERT INTO ROSE_TEST (ACTIVITY_DATE,SITE,STOCK,UNIT_PRICE,QUANTITY)
VALUES (TO_DATE('2008-06-25 11:09','YYYY-MM-DD HH24:MI'),'ADMN',100,2.18,10);

COMMIT;

SELECT * FROM ROSE_TEST yields:-

ACTIVITY_DATE       SITE STOCK UNIT_PRICE QUANTITY
18/09/2007 12:31:00 ADMN 100   2.18       10
25/09/2007 12:04:00 ADMN 100   2.18       12
16/11/2007 08:47:00 ADMN 100   2.18        1
05/02/2008 09:28:00 ADMN 100   2.18       10
29/05/2008 10:57:00 ADMN 100   2.18       11
03/06/2008 15:28:00 ADMN 100   2.18       20
25/06/2008 11:09:00 ADMN 100   2.18       10

This appears to be a faithful copy of the data you posted.

SELECT ACTIVITY_DATE,SITE, UNIT_PRICE, QUANTITY, QUANTITY*UNIT_PRICE DOLLAR_COST,
SUM(QUANTITY)            OVER (PARTITION BY SITE ORDER BY ACTIVITY_DATE) RUNNING_COUNT,
SUM(QUANTITY*UNIT_PRICE) OVER (PARTITION BY SITE ORDER BY ACTIVITY_DATE) RUNNING_TOTAL
FROM ROSE_TEST;

yields

ACTIVITY_DATE       SITE UNIT_PRICE QUANTITY DOLLAR_COST RUNNING_COUNT RUNNING_TOTAL
18/09/2007 12:31:00 ADMN 2.18       10       21.8        10             21.8
25/09/2007 12:04:00 ADMN 2.18       12       26.16       22             47.96
16/11/2007 08:47:00 ADMN 2.18        1       2.18        23             50.14
05/02/2008 09:28:00 ADMN 2.18       10       21.8        33             71.94
29/05/2008 10:57:00 ADMN 2.18       11       23.98       44             95.92
03/06/2008 15:28:00 ADMN 2.18       20       43.6        64            139.52
25/06/2008 11:09:00 ADMN 2.18       10       21.8        74            161.32

Is this what you wanted? I know I haven't dealt with the issue of limiting the point at which the calculation cuts off, but I want to verify that I'm getting the data required first. Note that this obviates the need for a temporary table - you can just select what you want.

And the answer is?








Regards

T

 

[rose4567]

I tested and yes, you are getting correct information. I tweaked a little for my particular result set, but this is great. So how do we deal with the cut off? I pasted in the modified query below since the cut off is based on the value stored in the subquery qty-on-hand. I guess if there's a way to handle the cut off here this could just be a view instead of a function?

This is really cool!

SELECT tran_ts, SITE_id, stock_item_nbr, UNIT_cost, COALESCE((SELECT SUM(QTY_ON_HAND) Expr1
FROM STOCK_ITEM_LOCATIONS
WHERE SITE_ID = iv_transactions.site_id AND STOCK_ITEM_NBR = iv_transactions.stock_item_nbr
GROUP BY SITE_ID, STOCK_ITEM_NBR), 0) qty_on_hand, QUANTITY, QUANTITY*UNIT_cost DOLLAR_COST,
SUM(QUANTITY) OVER (PARTITION BY SITE_id, stock_item_nbr ORDER BY tran_ts) RUNNING_COUNT,
SUM(QUANTITY*UNIT_cost) OVER (PARTITION BY SITE_id, stock_item_nbr ORDER BY tran_ts) RUNNING_TOTAL
FROM iv_transactions;

 

[Thargy]

rose,

Thanks for the confirmation. Absolutely yes, this could (and probably should) be a view. Creating temporary tables in Oracle is usually unnecessary, and (in my admittedly limited experience) usually done by those moving across from sql server.

I am a bit confused here. I can't see a subquery in what you posted.

What's wrong with

SELECT * FROM
(<your query goes here>)
WHERE quantity_on_hand <= RUNNING_COUNT

Can you clarify this please?

Regards

T

 

[Thargy]

FAO Santa,

can you carry on with this one please Dave?

It's getting really late here, and I'm guessing that Rose is on the west coast, as he/she seems to log on late-ish in my day.

I really do need some sleep, but don't want to leave the job unfinished.

Regards

T

 

[rose4567]

Running Count" is an invalid identifier.

I pulled qty_on_hand out of subquery and restructured.

Perhaps we could use the select statement as you've written but incorporate in a function so we can declare and access running count? Here's the part of my SQL statement that tells it to cut off:
WHILE @@FETCH_STATUS = 0 and @RunningTotal <= @qty_on_hand

if we structure as a function could we use similar syntax?

 

[SantaMufasa]

Rose,

I just finished some deadline work and checked this thread. I'm happy to carry on from Tharg's excellent work. (Thanks, Tharg, for doing the heavy lifting on the table CREATE, INSERTs and illustrated the beauty of Oracle's Analytical Functions. It makes it so much easier to create solutions when a table and rows exist!)

...@RunningTotal <= @qty_on_hand

My density is perhaps attributable to the hour, but which column represents qty_on_hand (quantity perhaps?) and when will RunningTotal ever be <= qty_on_hand?


Once that gets sorted out, I'm sure we can fulfill your request.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[Thargy]

Rose,

I'm slightly puzzled/concerned about your posting with regard to

WHILE @@FETCH_STATUS = 0 and @RunningTotal <= @qty_on_hand

As far as I tell from the information in your last post, you should create a view which provides the data needed.

I believe that you then do not require a function of any sort. You can just use a query to select from your just-created view to get what you want.

I believe that Santa has identified a logical inconsistency in your post, as I too can see no way by which a cut-off point can be determined.

Can you please clarify your intention, so we know how best to proceed? Also, I noticed (with pleasure) that your query has extended the partition criteria in the analytic part of the query. Therefore, could you post a create table statement for the tables you're using, some insert statements for the data and desired output based on your selection criteria. That way we can tweak the query and get the syntax correct as well.

Regards

T

 

[rose4567]

Here is the restructured query with qty_on_hand no longer a subquery. Perhaps easier to see this way.


SELECT iv_transactions.tran_ts, iv_transactions.SITE_id, iv_transactions.stock_item_nbr, iv_transactions.UNIT_cost, stock_item_locations.qty_on_hand,
QUANTITY, QUANTITY*UNIT_cost DOLLAR_COST,
SUM(QUANTITY) OVER (PARTITION BY iv_transactions.SITE_id, iv_transactions.stock_item_nbr ORDER BY tran_ts) RUNNING_COUNT,
SUM(QUANTITY*UNIT_cost) OVER (PARTITION BY iv_transactions.SITE_id, iv_transactions.stock_item_nbr ORDER BY tran_ts) RUNNING_TOTAL
FROM iv_transactions right outer join stock_item_locations on iv_transactions.site_id = stock_item_locations.site_id and
iv_transactions.stock_item_nbr = stock_item_locations.stock_item_nbr;


Here is the output sample from my earlier post. Qty_on_Hand is 73 in the sample.

DATE SITE STOCK UNIT QTY QTY Running Running
COST onhand count Total
2007-09-18 12:31 ADMN 100 2.18 10 73 10 21.80
2007-09-25 12:04 ADMN 100 2.18 12 73 22 47.96
2007-11-16 08:47 ADMN 100 2.18 1 73 23 50.14
2008-02-05 09:28 ADMN 100 2.18 10 73 33 71.94
2008-05-29 10:57 ADMN 100 2.18 11 73 44 95.92
2008-06-03 15:28 ADMN 100 2.78 20 73 64 151.52
2008-06-25 11:09 ADMN 100 2.78 10 73 74 154.30 <---

My apologies, I will try and better clarify. The FIFO calcuation multiplies QTY*UNIT_COST until it equals QTY_ON_HAND. That is the basic gist of it. Sometimes, though, we will only need part of a record, a portion of qty, to accomplish that goal.

So we pull records in ascending order and cut off where the RUNNING_COUNT either equals QTY_ON_HAND or in some cases, will just exceed it like the sample above.

If RUNNING_COUNT is less than or equal to qty_on_hand I am multiplying QTY*UNIT_COST.

We apply the logical stop if RUNNING_COUNT exceeds QTY_ON_HAND. In those cases, we must subtract QTY_ON_HAND from RUNNING_COUNT and multiple the difference by UNIT_COST.

So in the case of my sample above, the last row exceeds qty_on_hand of 73 when it reaches a RUNNING_TOTAL of 74. To calculate up to 73, the last row only needs to be multiplied by a quantity of 1. So again, I subtract RUNNING_TOTAL from QTY_ON_HAND to get my multipliable difference of 1. The calculation to do that looks like: (Running_Total - qty_on_hand) * unit_cost)

In the end I grab the MAX(RUNNING_TOTAL) calculated dollar amount. I need this record for each site_id, stock_item_nbr. So in the case above, I need $154.30. The end goal of the calculation is to grab that max dollar amount.

Since each row can have a difference unit cost I can't just multiply the qty_on_hand in an across the board way. This is why we total each row separately.

The SQL function works great in terms of cutting off the calcuation where it need to (that is the portion I pasted yesterday that I'm still struggling to incorporate into the statement above). The statement above gets us most of the way there, but as I mentioned, the suggested where clause
WHERE quantity_on_hand <= RUNNING_COUNT
produces an invalid identifier error. Is there a way to use a calculated field or assigned field name in a where clause like that?

 

[Thargy]

Rose,

I think I'm beginning to understand, but need to clear something up.

In my query I have a running total of
03/06/2008 15:28:00 ADMN 2.18 20 43.6 64 139.52
where you have
2008-06-03 15:28 ADMN 100 2.78 20 73 64 151.52

The discrepancy in running total is I believe due to me having an incorrect unit price of 2.18 instead of 2.78. Can you confirm this?

On the assumption that I am wrong I have rerun the query and now have a running total the same as yours on the sixth line. My output is now:-

ACTIVITY_DATE       SITE UNIT_PRICE QUANTITY DOLLAR_COST RUNNING_COUNT RUNNING_TOTAL
18/09/2007 12:31:00 ADMN 2.18       10       21.8        10             21.8
25/09/2007 12:04:00 ADMN 2.18       12       26.16       22             47.96
16/11/2007 08:47:00 ADMN 2.18        1        2.18       23             50.14
05/02/2008 09:28:00 ADMN 2.18       10       21.8        33             71.94
29/05/2008 10:57:00 ADMN 2.18       11       23.98       44             95.92
03/06/2008 15:28:00 ADMN 2.78       20       55.6        64            151.52
25/06/2008 11:09:00 ADMN 2.78       10       27.8        74            179.32

Your post states that you carry on multiplying until the running total equals the quantity on hand. Since row 6 has a running total of 64, and you want to stop at 73, why don't you have to multiply the unit price by nine, add it to the 151.52 and come up with the total. If you multiply just one more, then your running total must surely by 64+1, and not 73. What am I missing here?

Regards

T

 

[rose4567]

You are correct about the discrepency. It's the difference in 2.18 vs. 2.78.

Your corrected output with the last two lines showing unit cost as 2.78 are now correct in matching my sample.

You are also correct about multiplying by 9. I had the original code that way above, then when I was testing I confused myself. I'm sorry! So from my original post, the calculation to obtain the difference in the last line is:
((@qty - (@RunningTotal - @qty_on_hand)) * @unit_cost)
10 - (73 - 74) * 2.78

73-74 = -1 then subtracting 10 from -1 gives me the 9.

You aren't missing anything. I was tired and confused myself.

 

[Thargy]

Rose,

I've just spotted in your earlier post that multiplying by nine is definitely wrong. So, herewith a tweaked version of what I now have.

DROP TABLE ROSE_TEST CASCADE CONSTRAINTS PURGE;

CREATE TABLE ROSE_TEST
(
 ACTIVITY_DATE    DATE,
 SITE             VARCHAR2(4),
 STOCK            INTEGER,
 UNIT_PRICE       NUMBER(3,2),
 QUANTITY         INTEGER,
 QUANTITY_ON_HAND INTEGER
);

INSERT INTO ROSE_TEST (ACTIVITY_DATE,SITE,STOCK,UNIT_PRICE,QUANTITY,QUANTITY_ON_HAND)
VALUES (TO_DATE('2007-09-18 12:31','YYYY-MM-DD HH24:MI'),'ADMN',100,2.18,10,73);

INSERT INTO ROSE_TEST (ACTIVITY_DATE,SITE,STOCK,UNIT_PRICE,QUANTITY,QUANTITY_ON_HAND)
VALUES (TO_DATE('2007-09-25 12:04','YYYY-MM-DD HH24:MI'),'ADMN',100,2.18,12,73);

INSERT INTO ROSE_TEST (ACTIVITY_DATE,SITE,STOCK,UNIT_PRICE,QUANTITY,QUANTITY_ON_HAND)
VALUES (TO_DATE('2007-11-16 08:47','YYYY-MM-DD HH24:MI'),'ADMN',100,2.18,1,73);

INSERT INTO ROSE_TEST (ACTIVITY_DATE,SITE,STOCK,UNIT_PRICE,QUANTITY,QUANTITY_ON_HAND)
VALUES (TO_DATE('2008-02-05 09:28','YYYY-MM-DD HH24:MI'),'ADMN',100,2.18,10,73);

INSERT INTO ROSE_TEST (ACTIVITY_DATE,SITE,STOCK,UNIT_PRICE,QUANTITY,QUANTITY_ON_HAND)
VALUES (TO_DATE('2008-05-29 10:57','YYYY-MM-DD HH24:MI'),'ADMN',100,2.18,11,73);

INSERT INTO ROSE_TEST (ACTIVITY_DATE,SITE,STOCK,UNIT_PRICE,QUANTITY,QUANTITY_ON_HAND)
VALUES (TO_DATE('2008-06-03 15:28','YYYY-MM-DD HH24:MI'),'ADMN',100,2.78,20,73);

INSERT INTO ROSE_TEST (ACTIVITY_DATE,SITE,STOCK,UNIT_PRICE,QUANTITY,QUANTITY_ON_HAND)
VALUES (TO_DATE('2008-06-25 11:09','YYYY-MM-DD HH24:MI'),'ADMN',100,2.78,10,73);

COMMIT;

SELECT ACTIVITY_DATE,SITE, UNIT_PRICE, QUANTITY, QUANTITY_ON_HAND,QUANTITY*UNIT_PRICE DOLLAR_COST,
SUM(QUANTITY)            OVER (PARTITION BY SITE ORDER BY ACTIVITY_DATE) RUNNING_COUNT,
SUM(QUANTITY*UNIT_PRICE) OVER (PARTITION BY SITE ORDER BY ACTIVITY_DATE) RUNNING_TOTAL,
SUM(QUANTITY)            OVER (PARTITION BY SITE ORDER BY ACTIVITY_DATE) - QUANTITY_ON_HAND THRESHOLD
FROM ROSE_TEST;

which yields

ACTIVITY_DATE       SITE UNIT_PRICE QUANTITY DOLLAR_COST RUNNING_COUNT RUNNING_TOTAL THRESHOLD
18/09/2007 12:31:00 ADMN 2.18       10       21.8        10             21.8         -63
25/09/2007 12:04:00 ADMN 2.18       12       26.16       22             47.96        -51
16/11/2007 08:47:00 ADMN 2.18        1        2.18       23             50.14        -50
05/02/2008 09:28:00 ADMN 2.18       10       21.8        33             71.94        -40
29/05/2008 10:57:00 ADMN 2.18       11       23.98       44             95.92        -29
03/06/2008 15:28:00 ADMN 2.78       20       55.6        64            151.52        -9
25/06/2008 11:09:00 ADMN 2.78       10       27.8        74            179.32         1

more to follow.

Regards

T

 

[Thargy]

rose,

we cross-posted.
I now understand that multiplying by nine is the correct thing to do. Working.......

Regards

T

 

[Thargy]

Rose,

I have used the following to show working:-

WITH ROSE_DATA AS
(
    SELECT ACTIVITY_DATE,SITE, UNIT_PRICE, QUANTITY, QUANTITY_ON_HAND,QUANTITY*UNIT_PRICE DOLLAR_COST,
    SUM(QUANTITY)            OVER (PARTITION BY SITE ORDER BY ACTIVITY_DATE) RUNNING_COUNT,
    SUM(QUANTITY*UNIT_PRICE) OVER (PARTITION BY SITE ORDER BY ACTIVITY_DATE) RUNNING_TOTAL,
    SUM(QUANTITY)            OVER (PARTITION BY SITE ORDER BY ACTIVITY_DATE) - QUANTITY_ON_HAND THRESHOLD,
    QUANTITY - (SUM(QUANTITY) OVER (PARTITION BY SITE ORDER BY ACTIVITY_DATE) - QUANTITY_ON_HAND) MULTIPLICAND
    FROM ROSE_TEST
)
SELECT ACTIVITY_DATE,SITE, UNIT_PRICE, QUANTITY, QUANTITY_ON_HAND,QUANTITY*UNIT_PRICE DOLLAR_COST,
       RUNNING_COUNT, RUNNING_TOTAL, THRESHOLD, MULTIPLICAND,
       LAG(RUNNING_TOTAL,1,0) OVER (ORDER BY RUNNING_TOTAL) PREV_RUN_TOT,
       LAG(RUNNING_TOTAL,1,0) OVER (ORDER BY RUNNING_TOTAL) + (MULTIPLICAND * UNIT_PRICE) ROSE_TOTAL       
  FROM ROSE_DATA;

but for your required result use

WITH ROSE_DATA AS
(
    SELECT ACTIVITY_DATE,SITE, UNIT_PRICE, QUANTITY, QUANTITY_ON_HAND,QUANTITY*UNIT_PRICE DOLLAR_COST,
    SUM(QUANTITY)            OVER (PARTITION BY SITE ORDER BY ACTIVITY_DATE) RUNNING_COUNT,
    SUM(QUANTITY*UNIT_PRICE) OVER (PARTITION BY SITE ORDER BY ACTIVITY_DATE) RUNNING_TOTAL,
    SUM(QUANTITY)            OVER (PARTITION BY SITE ORDER BY ACTIVITY_DATE) - QUANTITY_ON_HAND THRESHOLD,
    QUANTITY - (SUM(QUANTITY) OVER (PARTITION BY SITE ORDER BY ACTIVITY_DATE) - QUANTITY_ON_HAND) MULTIPLICAND
    FROM ROSE_TEST
)
SELECT ACTIVITY_DATE,SITE, UNIT_PRICE, QUANTITY, QUANTITY_ON_HAND,
       QUANTITY*UNIT_PRICE DOLLAR_COST,RUNNING_COUNT, 
       (CASE 
           WHEN THRESHOLD < 0 THEN RUNNING_TOTAL 
           ELSE LAG(RUNNING_TOTAL,1,0) OVER (ORDER BY RUNNING_TOTAL) + (MULTIPLICAND * UNIT_PRICE)
         END ) RUNNING_TOTAL
  FROM ROSE_DATA;

which yields

ACTIVITY_DATE       SITE UNIT_PRICE QUANTITY QUANTITY_ON_HAND DOLLAR_COST RUNNING_COUNT RUNNING_TOTAL
18/09/2007 12:31:00 ADMN 2.18       10       73               21.8        10             21.8
25/09/2007 12:04:00 ADMN 2.18       12       73               26.16       22             47.96
16/11/2007 08:47:00 ADMN 2.18        1       73                2.18       23             50.14
05/02/2008 09:28:00 ADMN 2.18       10       73               21.8        33             71.94
29/05/2008 10:57:00 ADMN 2.18       11       73               23.98       44             95.92
03/06/2008 15:28:00 ADMN 2.78       20       73               55.6        64            151.52
25/06/2008 11:09:00 ADMN 2.78       10       73               27.8        74            176.54

Rose, one caveat here, I tested this on oracle 11 because that's all I have access to right now. Please check that this works on your version.

Regards

T

 

[rose4567]

Thanks, Tharg. I'm getting error

ORA-32031: illegal reference of a query name in WITH clause

but I am still testing. I am going to try and take the first select statement and make it a view then take the second statement and make that call to the first view.

I'm thinking that might do the trick.

I'll let you know what my testing finds, but won't be able to post back until tomorrow. Just wanted to say thank you for now and let you know I'm testing so you were not waiting for a reply tonight since I think you are in a different time zone.

Thanks again and I'll report back tomorrow!