jspc
asked on
SQL Function NOT ROUND
Hello,
I have created a FUNCTION to return a Value in SQL.
The function is working ok, however the value returned is rounded and I don't want it rounded. It needs to be 2 decimals.
The attached file example $36.00 should be $36.33.
Below the the Function Script.
CREATE FUNCTION [dbo].[GetLastSalesAmountP roducts]
(@ProductID char(17))
RETURNS
INT
AS
BEGIN
-- Declare the return variable here
DECLARE @LastAmountProducts INT
SELECT @LastAmountProducts = MAX(transheaders.localgros s)
FROM TRANSHEADERS
INNER JOIN TRANSDETAILS ON TRANSHEADERS.TransactionID =
TRANSDETAILS.TransactionID
AND TRANSHEADERS.CurrentVersio nNo = TRANSDETAILS.CurrentVersio nNo
where TRANSHEADERS.transactionty pe in ('CI','CX') and
TRANSDETAILS.ItemAcType = 'P' and TRANSDETAILS.ItemAcID = @ProductID
-- Return the result of the function
RETURN @LastAmountProducts
END
GO
Untitled.png
I have created a FUNCTION to return a Value in SQL.
The function is working ok, however the value returned is rounded and I don't want it rounded. It needs to be 2 decimals.
The attached file example $36.00 should be $36.33.
Below the the Function Script.
CREATE FUNCTION [dbo].[GetLastSalesAmountP
(@ProductID char(17))
RETURNS
INT
AS
BEGIN
-- Declare the return variable here
DECLARE @LastAmountProducts INT
SELECT @LastAmountProducts = MAX(transheaders.localgros
FROM TRANSHEADERS
INNER JOIN TRANSDETAILS ON TRANSHEADERS.TransactionID
TRANSDETAILS.TransactionID
AND TRANSHEADERS.CurrentVersio
where TRANSHEADERS.transactionty
TRANSDETAILS.ItemAcType = 'P' and TRANSDETAILS.ItemAcID = @ProductID
-- Return the result of the function
RETURN @LastAmountProducts
END
GO
Untitled.png
ASKER
Tried both and still rounding ...
and change:
RETURNS
INT
accordingly
RETURNS
INT
accordingly
to:
or
RETURNS
Decimal(16,2)
or
RETURNS
Money
ASKER
Now get this error
Msg 102, Level 15, State 1, Procedure GetLastSalesAmountProducts , Line 19
Incorrect syntax near 'Decimal'.
Msg 102, Level 15, State 1, Procedure GetLastSalesAmountProducts
Incorrect syntax near 'Decimal'.
can you post your latest scripts?
ASKER
CREATE FUNCTION [dbo].[GetLastSalesAmountP roducts]
(@ProductID char(17))
RETURNS
INT
AS
BEGIN
-- Declare the return variable here
DECLARE @LastAmountProducts Money
SELECT @LastAmountProducts = MAX(transheaders.localgros s)
FROM TRANSHEADERS
INNER JOIN TRANSDETAILS ON TRANSHEADERS.TransactionID =
TRANSDETAILS.TransactionID
AND TRANSHEADERS.CurrentVersio nNo = TRANSDETAILS.CurrentVersio nNo
where TRANSHEADERS.transactionty pe in ('CI','CX') and
TRANSDETAILS.ItemAcType = 'P' and TRANSDETAILS.ItemAcID = @ProductID
-- Return the result of the function
RETURN @LastAmountProducts Money
END
GO
(@ProductID char(17))
RETURNS
INT
AS
BEGIN
-- Declare the return variable here
DECLARE @LastAmountProducts Money
SELECT @LastAmountProducts = MAX(transheaders.localgros
FROM TRANSHEADERS
INNER JOIN TRANSDETAILS ON TRANSHEADERS.TransactionID
TRANSDETAILS.TransactionID
AND TRANSHEADERS.CurrentVersio
where TRANSHEADERS.transactionty
TRANSDETAILS.ItemAcType = 'P' and TRANSDETAILS.ItemAcID = @ProductID
-- Return the result of the function
RETURN @LastAmountProducts Money
END
GO
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
RETURN @LastAmountProducts Money
is wrong, and it should be coded as:
RETURN @LastAmountProducts
Open in new window
or:
Open in new window
instead.