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]

I think you are in a different time zone.

Tharg is in "Jolly Ol' England" (Metro London to be roughly precise), so he is six hours ahead of you (seven hours ahead of me in Metro Salt Lake City). That's why Tharg gets sleepy along about now since it's midnight for him presently.


(But since I'm an hour behind you, I'm happy to help, especially if it's urgent.)

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.”

 

[SantaMufasa]

...and if you post code and any accompanying errors, Rose, it makes it much easier for us to do "instant debugging." <grin>

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]

Good Morning to you Rose and Santa, I trust you are both sleeping well whilst us busy British folks are up and about .

Rose, I was concerned that you might get different behaviour on oracle 9, which is why I mentioned that I'm using 11. Your notion of making a view and then selecting from it seems excellent.

Please do let me know how you get on.

Regards

T

 

[rose4567]

I restructured for this version, but I'm having trouble using the "WITH" clause. I get the following error:

ORA-00905: missing keyword

Here's the code. In this version of Oracle you have to list the fields you want created. The "With temp_tble AS" line it where it hits "WITH" and throws the missing keyword error. What keyword am I missing here?


CREATE OR REPLACE VIEW V_FIFO
(tran_ts, site_id, stock_item_nbr, unit_cost, quantity, quantity_on_hand, dollar_cost, running_count, running_total)

WITH rose_data AS (
SELECT iv_transactions.tran_ts, iv_transactions.SITE_id, iv_transactions.stock_item_nbr,
iv_transactions.UNIT_cost, iv_transactions.QUANTITY, stock_item_locations.QUANTITY_ON_HAND,
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_PRICE) OVER (PARTITION BY iv_transactions.SITE_id, iv_transactions.stock_item_nbr ORDER BY tran_ts) RUNNING_TOTAL,
SUM(QUANTITY) OVER (PARTITION BY iv_transactions.SITE_id, iv_transactions.stock_item_nbr ORDER BY tran_ts) - QUANTITY_ON_HAND THRESHOLD,
QUANTITY - (SUM(QUANTITY) OVER (PARTITION BY iv_transactions.SITE_id, iv_transactions.stock_item_nbr ORDER BY tran_ts) - QUANTITY_ON_HAND) MULTIPLICAND
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
)
SELECT iv_transactions.tran_ts, iv_transactions.SITE_id, iv_transactions.stock_item_nbr, iv_transactions.UNIT_cost,
iv_transactions.QUANTITY, QUANTITY_ON_HAND,
QUANTITY*UNIT_cost 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;

 

[Thargy]

Rose,

CREATE OR REPLACE VIEW V_ROSE_TEST
(ACTIVITY_DATE,SITE, UNIT_PRICE, QUANTITY, QUANTITY_ON_HAND,DOLLAR_COST,RUNNING_COUNT, RUNNING_TOTAL)
AS
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;

works correctly for me. You appear to be missing an "AS" immediately after the list of fields and before the with clause. Let me know if this works.

Regards

T

 

[SantaMufasa]

Yes, Rose, the code that Tharg posted works for me, as well:

CREATE OR REPLACE VIEW V_ROSE_TEST
(ACTIVITY_DATE,SITE, UNIT_PRICE, QUANTITY, QUANTITY_ON_HAND,DOLLAR_COST,RUNNING_COUNT, RUNNING_TOTAL)
AS
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;

View created.

select * from v_rose_test;

ACTIVITY_ SITE UNIT_PRICE   QUANTITY QUANTITY_ON_HAND DOLLAR_COST RUNNING_COUNT RUNNING_TOTAL
--------- ---- ---------- ---------- ---------------- ----------- ------------- -------------
18-SEP-07 ADMN       2.18         10               73        21.8            10          21.8
25-SEP-07 ADMN       2.18         12               73       26.16            22         47.96
16-NOV-07 ADMN       2.18          1               73        2.18            23         50.14
05-FEB-08 ADMN       2.18         10               73        21.8            33         71.94
29-MAY-08 ADMN       2.18         11               73       23.98            44         95.92
03-JUN-08 ADMN       2.78         20               73        55.6            64        151.52
25-JUN-08 ADMN       2.78         10               73        27.8            74        176.54

7 rows selected.

BTW, could you please confirm what version of Oracle you are running?

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]

This is running against Oracle 10g database, but I am trying to create the view through TOAD (no laughing). Perhaps that's my problem?

Adding "AS" took care of that error, but now it's throwing the following error and this doesn't make sense to me why it would be complaining about QUANTITY_ON_HAND.

ORA-00904: "QUANTITY_ON_HAND": invalid identifier

It complained about the below copied portion of code.

QUANTITY - (SUM(QUANTITY) OVER (PARTITION BY SITE ORDER BY ACTIVITY_DATE) - QUANTITY_ON_HAND) MULTIPLICAND

 

[rose4567]

Here's the whole body of code. Keep in mind for me that quantity_on_hand is coming from another table so I need to perform an outer join. I don't see why that would have any effect on the query. I should be able to perform a select statement and join two tables. I also tried cutting the first select statement and tried running that by iteself. It throws the same error about quantity_on_hand being an invalid identifier.



CREATE OR REPLACE VIEW V_FIFO
(tran_ts, site_id, stock_item_nbr, unit_cost, quantity, QUANTITY_ON_HAND, dollar_cost, running_count, running_total)
AS WITH temp_table AS (
SELECT iv_transactions.tran_ts, iv_transactions.SITE_id, iv_transactions.stock_item_nbr,
iv_transactions.UNIT_cost, iv_transactions.QUANTITY, STOCK_ITEM_LOCATIONS.QUANTITY_ON_HAND,
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_PRICE) OVER (PARTITION BY iv_transactions.SITE_id, iv_transactions.stock_item_nbr ORDER BY tran_ts) RUNNING_TOTAL,
SUM(QUANTITY) OVER (PARTITION BY iv_transactions.SITE_id, iv_transactions.stock_item_nbr ORDER BY tran_ts) - STOCK_ITEM_LOCATIONS.QUANTITY_ON_HAND THRESHOLD,
QUANTITY - (SUM(QUANTITY) OVER (PARTITION BY iv_transactions.SITE_id, iv_transactions.stock_item_nbr ORDER BY tran_ts) - STOCK_ITEM_LOCATIONS.QUANTITY_ON_HAND) MULTIPLICAND
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
)
SELECT tran_ts, SITE_id, stock_item_nbr, UNIT_cost,
QUANTITY, QUANTITY_ON_HAND,
QUANTITY*UNIT_cost 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 temp_table;

 

[rose4567]

OK sorry, still testing. I think that this version of Oracle or TOAD requires different JOIN syntax.

 

[Thargy]

Rose,
I am suspicious of the right outer join in your sql. That's definitely a hold-over from sql server.

Just run the query on its own (without attempting to make a view). When the query's straigt, then go for the view, ok?

TOAD is just reporting the troubles Oracle is having with your query, it's almost certainly not TOAD that's causing the problem.

Regards

T

 

[rose4567]

IT WORKS! Thank you SO much Tharg!! My version of Oracle did not like the outer join statement. I needed to join in the WHERE clause. I'm going to paste the end result in case anyone else is running into version issues and references this thread.

I want to say thank you again - a thousand times over. You guys are the best! THANK YOU THANK YOU THANK YOU!!!!

_______________________
CREATE OR REPLACE VIEW V_FIFO ( TRAN_TS,
SITE_ID, STOCK_ITEM_NBR, UNIT_COST, QUANTITY,
QTY_ON_HAND, DOLLAR_COST, RUNNING_COUNT, RUNNING_TOTAL
) AS WITH TEMP_TABLE AS (
SELECT IV_TRANSACTIONS.TRAN_TS, IV_TRANSACTIONS.SITE_ID, IV_TRANSACTIONS.STOCK_ITEM_NBR,
IV_TRANSACTIONS.UNIT_COST, IV_TRANSACTIONS.QUANTITY, STOCK_ITEM_LOCATIONS.QTY_ON_HAND,
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,
SUM(QUANTITY) OVER (PARTITION BY IV_TRANSACTIONS.SITE_ID, IV_TRANSACTIONS.STOCK_ITEM_NBR ORDER BY TRAN_TS) - STOCK_ITEM_LOCATIONS.QTY_ON_HAND THRESHOLD,
QUANTITY - (SUM(QUANTITY) OVER (PARTITION BY IV_TRANSACTIONS.SITE_ID, IV_TRANSACTIONS.STOCK_ITEM_NBR ORDER BY TRAN_TS) - STOCK_ITEM_LOCATIONS.QTY_ON_HAND) MULTIPLICAND
FROM STOCK_ITEM_LOCATIONS, IV_TRANSACTIONS
WHERE STOCK_ITEM_LOCATIONS.SITE_ID = IV_TRANSACTIONS.SITE_ID(+) and
STOCK_ITEM_LOCATIONS.STOCK_ITEM_NBR = IV_TRANSACTIONS.STOCK_ITEM_NBR(+)
)
SELECT TRAN_TS, SITE_ID, STOCK_ITEM_NBR, UNIT_COST,
QUANTITY, QTY_ON_HAND,
QUANTITY*UNIT_COST DOLLAR_COST,RUNNING_COUNT,
(CASE
WHEN THRESHOLD < 0 THEN RUNNING_TOTAL
ELSE LAG(RUNNING_TOTAL,1,0) OVER (ORDER BY RUNNING_TOTAL) + (MULTIPLICAND * UNIT_COST)
END ) RUNNING_TOTAL
FROM TEMP_TABLE

 

[Thargy]

Rose,

thanks very much from my side of the pond.

As a parting shot, I would encourage you to stop writing PL/SQL (i.e. functions and procedures) and start writing lots more SQL. As you have seen for yourself, temporary tables, processing data in cursors and loads of variables can be replaced with a few well-crafted lines of SQL.

Also, straight SQL almost invariably out-performs code. By just creating a view, you don't clutter up your schema with unnecessary tables - temporary or otherwise.

Do let me know how this FIFO thing pans out. Please post again if it goes into production, so we can bask in the glory of it all

Regards

T

 

[SantaMufasa]

And, Rose, Be sure to reward Tharg's fine efforts with a well-deserved

by clicking the link: "

Thank thargtheslayer for this valuable post".

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]

Hey guys,

So I am testing and for some reason the DOLLAR_COST and RUNNING_TOTAL fields loose their formatting when selected from the second "temp table." I am not sure if I need to define the fields in my create view statement or if I need a format mast in the second select statement. Here's the code and I'm also trying to upload a picture to show the difference but basically when I run the first select statement separately the DOLLAR_COST and RUNNING_TOTAL fields keep their decimal places. But when I look at the view they are no longer decimals. These fields are now whole numbers. What is the best way to get these two fields formatted properly?

CREATE OR REPLACE VIEW V_FIFO ( TRAN_TS, 
SITE_ID, STOCK_ITEM_NBR, UNIT_COST, QUANTITY, 
QTY_ON_HAND, DOLLAR_COST, RUNNING_COUNT, THRESHOLD, 
RUNNING_TOTAL ) AS WITH TEMP_TABLE AS (  
    SELECT IV_TRANSACTIONS.TRAN_TS, IV_TRANSACTIONS.SITE_ID, IV_TRANSACTIONS.STOCK_ITEM_NBR,   
	IV_TRANSACTIONS.UNIT_COST, IV_TRANSACTIONS.QUANTITY, STOCK_ITEM_LOCATIONS.QTY_ON_HAND,  
	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,  
    SUM(QUANTITY)             OVER (PARTITION BY IV_TRANSACTIONS.SITE_ID, IV_TRANSACTIONS.STOCK_ITEM_NBR ORDER BY TRAN_TS) - STOCK_ITEM_LOCATIONS.QTY_ON_HAND THRESHOLD,  
    QUANTITY - (SUM(QUANTITY) OVER (PARTITION BY IV_TRANSACTIONS.SITE_ID, IV_TRANSACTIONS.STOCK_ITEM_NBR ORDER BY TRAN_TS) - STOCK_ITEM_LOCATIONS.QTY_ON_HAND) MULTIPLICAND  
    FROM STOCK_ITEM_LOCATIONS, IV_TRANSACTIONS  
	WHERE STOCK_ITEM_LOCATIONS.SITE_ID = IV_TRANSACTIONS.SITE_ID(+) and  
STOCK_ITEM_LOCATIONS.STOCK_ITEM_NBR = IV_TRANSACTIONS.STOCK_ITEM_NBR(+) 
ORDER BY STOCK_ITEM_LOCATIONS.SITE_ID, STOCK_ITEM_LOCATIONS.STOCK_ITEM_NBR, IV_TRANSACTIONS.TRAN_TS  
)  
SELECT TRAN_TS, SITE_ID, STOCK_ITEM_NBR, UNIT_COST,   
QUANTITY, QTY_ON_HAND,  
       QUANTITY*UNIT_COST DOLLAR_COST,RUNNING_COUNT,THRESHOLD, 
       (CASE   
           WHEN THRESHOLD < 0 THEN RUNNING_TOTAL   
           ELSE LAG(RUNNING_TOTAL,1,0) OVER (ORDER BY RUNNING_TOTAL) + (MULTIPLICAND * UNIT_COST)  
         END ) RUNNING_TOTAL  
		  
  FROM TEMP_TABLE  
  ORDER BY SITE_ID, STOCK_ITEM_NBR, TRAN_TS

 

[SantaMufasa]

Rose,

Oracle never stores leading or trailing zeroes in a NUMBER expression. If you want to see a specific number of formatted decimal places, you should use Oracle's TO_CHAR() function:

select 22, to_char(22,'00.99') formatted from dual;

        22 FORMAT
---------- ------
        22  22.00

Let us know if you have additional questions.

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]

Interesting. I guess I figured since the actual dollar_cost is 44.09 that's what would display. I didn't think it would round to straight 44.

I had tried TO_NUMBER with formatting but it still displayed just the whole number.

TO_CHAR in your sample above, however, works. Not sure why I could not format to number, but I'll take the to_char.

Thank you so much! The result set looks much better now.