August 2, 2010 at 9:56 am
I need to return the last inventory receipt date to a select statement. I created a function to do so as follows:
ALTER FUNCTION [dbo].[zfn_get_last_inv_Receipt]
(
@last_receipt_date as smalldatetime,
@item_id as int,
@whs_code as varchar(8)
)
RETURNS smalldatetime
AS
BEGIN
SELECT @last_receipt_date = MAX(txn_date)
FROM inv_tran
WHERE reason_code = 'RECEIPT' AND item_id = @item_id
AND whs_code = @whs_code
RETURN @last_receipt_date
END
When I iinsert the function in the TSQl statement:
SELECT ite_id, whs_code,
dbo.zfn_get_last_inv_Receipt(dbo.inv_tran.item_id, dbo.inv_tran.whs_code, 0) AS Lst Recpt
FROM inv_tran
WHERE reason_code = 'RECEIPT' AND item_id = '108372' AND whs_code = '628'
GROUP BY item, whs_code
I get the following error thrown:
Msg 8115, Level16, State 2, Line 1
Arothmetic overflow error converting expression to data type smalldatetime
Any suggestions
August 2, 2010 at 10:09 am
can u check the params to the function....
i think first argument to the function is not smalldatetime......
August 2, 2010 at 10:24 am
The data type for the txn_date data type in the table is smalldate.
I am not sure what you mean regarding the first argument
August 2, 2010 at 11:48 am
Change the function to:
ALTER FUNCTION [dbo].[zfn_get_last_inv_Receipt]
(
@item_id as int,
@whs_code as varchar(8)
)
RETURNS smalldatetime
AS
BEGIN
DECLARE @last_receipt_date smalldatetime
then change how you're calling it by taking out the ", 0"
Edit: What you are doing is passing the item_id as the date, the whs_code as the item_id, and 0 as the whs_code.
That Those changes above would should get you running. But, you might want to look into making this an inline table-valued function, or even a view:
ALTER FUNCTION dbo.zfn_get_last_inv_Receipt ()
RETURNS TABLE
WITH SCHEMABINDING -- not sure if this is in the correct place
AS
SELECT item_id, whs_code, last_receipt_date = MAX(txn_date)
FROM inv_tran
WHERE reason_code = 'RECEIPT'
GROUP BY item_id, whs_code
GO
Then you can call it by:
SELECT i.item_id, i.whs_code,
uf.last_receipt_date AS Lst Recpt
FROM inv_tran i
JOIN dbo.zfn_get_last_inv_Receipt uf
ON uf.item_id = i.item_id
AND uf.whs_code = i.whs_code
WHERE i.reason_code = 'RECEIPT' AND i.item_id = '108372' AND i.whs_code = '628'
GROUP BY i.item_id, i.whs_code
Edit: since there is no sample data, I couldn't test it. But if you run this against the original version, and check out the actual execution plan and io/time statistics, you should see a marked increase in performance.
Edit2: if you would need to get the max(txn_date), grouped by the item_id and whs_code, but for other reason codes, then you could either make that a parameter in the function to use in the where clause, or remove the where clause completely and add that column to the column list being selected/grouped by, and then join to it.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply