asked on UDF not working - passing a date parameter
I created a UDF named: [dbo].[RG_DailyShippingSta
When I call the function like:
SELECT * FROM [dbo].[RG_DailyShippingSta
I get: Line 1: Incorrect syntax near '('.
Then I replace GETDATE() with the values Ialso used when testing the function with hard-coded values:
SELECT * FROM [RGMFG].[dbo].[RG_DailyShi
But I get zero results.
I have included a copy of this UDF from when I was using hard coded values. When running it that way, I do get results.
I am also including a copy of it, not using hard coded values.
Would someone be willing to take a look at this and see if you can locate where I am making the mistake?
Thank you very much
udf-with-hard-coded-values.txt
udf-with-out-values.txt
When I call the function like:
SELECT * FROM [dbo].[RG_DailyShippingSta
I get: Line 1: Incorrect syntax near '('.
Then I replace GETDATE() with the values Ialso used when testing the function with hard-coded values:
SELECT * FROM [RGMFG].[dbo].[RG_DailyShi
But I get zero results.
I have included a copy of this UDF from when I was using hard coded values. When running it that way, I do get results.
I am also including a copy of it, not using hard coded values.
Would someone be willing to take a look at this and see if you can locate where I am making the mistake?
Thank you very much
udf-with-hard-coded-values.txt
udf-with-out-values.txt
Microsoft SQL ServerSQL
Last Comment
metropia
SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
I will post the functions tomorrow. I apologize, I need the help still but don't have the code at this moment.
Thanks.
Thanks.
ASKER
here are the functions:
Thanks for your help.
ALTER FUNCTION [dbo].[RG_GetFiscalYearName] (@lookupDate AS datetime)
RETURNS VARCHAR(15)
AS
begin
DECLARE @intDate BIGINT, @yearName VARCHAR(15)
SELECT @intDate = DAY(@lookupDate) + (MONTH(@lookupDate)) * 100 + (YEAR(@lookupDate) * 10000)
SELECT @yearName = MIN(t.FiscYearName)
FROM
dbo.DIM_Time_PC t
WHERE
t.id = @intDate
RETURN @yearName
END
ALTER FUNCTION [dbo].[RG_GetFiscalMonthStart] (@lookupDate AS datetime)
RETURNS datetime
AS
begin
DECLARE @intDate BIGINT, @monthStart DATETIME
SELECT @intDate = DAY(@lookupDate) + (MONTH(@lookupDate)) * 100 + (YEAR(@lookupDate) * 10000)
SELECT @monthStart = MIN(t2.formatDate)
FROM
dbo.DIM_Time_PC t INNER JOIN
dbo.DIM_Time_PC t2 ON t.FiscYearKey = t2.FiscYearKey AND t.MonthKeyFisc = t2.MonthKeyFisc
WHERE
t.id = @intDate
RETURN @monthStart
END
ALTER FUNCTION [dbo].[RG_GetFiscalMonthKeyFiscal] (@lookupDate AS datetime)
RETURNS VARCHAR(15)
AS
begin
DECLARE @intDate BIGINT, @monthStart VARCHAR(15)
SELECT @intDate = DAY(@lookupDate) + (MONTH(@lookupDate)) * 100 + (YEAR(@lookupDate) * 10000)
SELECT @monthStart = MIN(t2.MonthKeyFisc)
FROM
dbo.DIM_Time_PC t INNER JOIN
dbo.DIM_Time_PC t2 ON t.FiscYearKey = t2.FiscYearKey AND t.MonthKeyFisc = t2.MonthKeyFisc
WHERE
t.id = @intDate
RETURN @monthStart
END
ALTER FUNCTION [dbo].[minusOneDay]
(
@dt DATETIME
)
RETURNS DATETIME
AS
BEGIN
SET @dt = DATEADD(DAY, -1, DATEDIFF(dd, 0, @dt))
RETURN @dt
END
Thanks for your help.
where is RG_DailyShippingStatusRepo
ASKER
That is the UDF located in the same database as the other UDFs.
I attached to this question.
I attached to this question.
SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
amongst the several errors I have found so far, there is one that I am not sure how to fix.
Error:
Syntax error converting the varchar value '*' to a column of data type int.
it comes from this operation within a CASE statement:
Is there any way to work around this issue?
Thanks a lot.
Error:
Syntax error converting the varchar value '*' to a column of data type int.
it comes from this operation within a CASE statement:
SUM((CASE
WHEN col.PRODUCT_CODE IN ('8500', '9000') THEN 0
ELSE
CASE
WHEN RG_SALESFORECASTBYPRODUCT_WEBSTER.FiscalYear = @fiscalYearNumber
AND RG_SALESFORECASTBYPRODUCT_WEBSTER.FiscalMonth = @fiscalMonthNumber
THEN ROUND((shl.SHIPPED_QTY * shl.UNIT_PRICE), 4)
ELSE 0
END
END)) AS 'TotalActualShip$$'
Is there any way to work around this issue?
Thanks a lot.
Syntax error converting the varchar value '*' to a column of data type int.
One of your columns has bad data (it contains a * which cannot be converted). Identify which one it is and what you want to happen in the query.
One of your columns has bad data (it contains a * which cannot be converted). Identify which one it is and what you want to happen in the query.
ASKER
It was the fiscal year parameter
I decalred it as a varchar without no specific length.
I changed it to an int data type and that fixed that problem.
One thing I noticed is that, when I try my function in SQL Server 2000 in the following manner:
select * from [dbo].[Test] (getdate())
I keep getting the error: Line 1: Incorrect syntax near '('.
Although I have tested each query that make up the function, separately. They all work, just not when they are run as a function.
So I tried it on SQL Server 2008, and the function runs without throwing that error, same code, same everything. just different version of SQL Server.
Can GETDATE() be used a an argument when calling the function?
Please advice.
Thank you much.
I decalred it as a varchar without no specific length.
I changed it to an int data type and that fixed that problem.
One thing I noticed is that, when I try my function in SQL Server 2000 in the following manner:
select * from [dbo].[Test] (getdate())
I keep getting the error: Line 1: Incorrect syntax near '('.
Although I have tested each query that make up the function, separately. They all work, just not when they are run as a function.
So I tried it on SQL Server 2008, and the function runs without throwing that error, same code, same everything. just different version of SQL Server.
Can GETDATE() be used a an argument when calling the function?
Please advice.
Thank you much.
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
I am somehow relief that you were able to duplicate the error.
Tomorrow I will test as you show in your post.
One issue I will face assuming that your recommendation works, is that the whole idea of mine to put all the code into a UDF, was to run it from crystal reports.
Having problems with the date parameter, do you think I will be able to run it from CR?
Thanks.
Tomorrow I will test as you show in your post.
One issue I will face assuming that your recommendation works, is that the whole idea of mine to put all the code into a UDF, was to run it from crystal reports.
Having problems with the date parameter, do you think I will be able to run it from CR?
Thanks.
SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
Sort of. Ended up creating a view that contains the current date
SELECT GETDATE() AS CurrentDate
and then declaring a new datetime variable in the function that is set to the value from the date view.
no need to pass any date parameter anymore.
SELECT GETDATE() AS CurrentDate
and then declaring a new datetime variable in the function that is set to the value from the date view.
no need to pass any date parameter anymore.
ASKER
Thank you everyone for your help.
Not having your tables, I tested your Table-Valued UDF with minimal code and I had no problems. But if I were to guess I suspect the problem lies in the following code:
SELECT @startFiscalMonth = dbo.RG_GetFiscalMonthStart
@endFiscalMonth = dbo.RG_GetFiscalMonthEnd(@
@fiscalYearNumber = dbo.RG_GetFiscalYearName(@
@fiscalMonthNumber = dbo.RG_GetFiscalMonthKeyFi
@currentDateMinusOne = dbo.minusOneDay(@dt)