I can't use GETDATE() in an In-Line function becaue it generates an error saying that I can't.

How do I get the date in it?

__________________
Mitch

The short answer is that you can't.

The GETDATE() function is "non-deterministic", and by definition, such functions are not allowed in the body of user defined functions.

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com

One possible solution though , is to use a datetime parameter to the function, and send in getdate() at exec-time. This naturally depends on what the function is meant to do...

Gert

Gert,

That actually is a good idea, and I think it will work in my case.

Thanks.

OK, but I keep getting the error:
"syntax error converting character string to smalldatetime data type"

I'm using Access 2002 and MS SQL2000.

Here is the code that I am using, I have tried several versions of the date parameter

ALTER FUNCTION dbo.FN_Shipping_QtyShippedByDate_WOIDPerShipper
(@FullItemID nvarchar(30),
@mGETDATE nvarchar(100)) /* I also tried it as date and smalldate */
RETURNS TABLE
AS
RETURN ( SELECT TOP 100 dbo.vShipping_ShpQty_PerPkInd.MultQty, dbo.vShipping_ShpQty_PerPkInd.ShpItem, dbo.vShipping_ShpQty_PerPkInd.ShpWOID,
                      dbo.vShipping_ShpQty_PerPkInd.ShpWOIDShipper, dbo.T_SetupSheetHistoryShipments.ShpmntDate,
                      dbo.T_SetupSheetHistoryShipments.ShpmntID
FROM dbo.vShipping_ShpQty_PerPkInd INNER JOIN
                      dbo.T_SetupSheetHistoryShipments ON dbo.vShipping_ShpQty_PerPkInd.ShpmntID = dbo.T_SetupSheetHistoryShipments.ShpmntID
GROUP BY dbo.vShipping_ShpQty_PerPkInd.MultQty, dbo.vShipping_ShpQty_PerPkInd.ShpItem, dbo.vShipping_ShpQty_PerPkInd.ShpWOID,
                      dbo.vShipping_ShpQty_PerPkInd.ShpWOIDShipper, dbo.T_SetupSheetHistoryShipments.ShpmntDate,
                      dbo.T_SetupSheetHistoryShipments.ShpmntID
HAVING (dbo.vShipping_ShpQty_PerPkInd.ShpItem IS NOT NULL) AND (dbo.vShipping_ShpQty_PerPkInd.ShpItem = @FullItemID) AND
                      (dbo.vShipping_ShpQty_PerPkInd.ShpWOID IS NOT NULL) AND (dbo.T_SetupSheetHistoryShipments.ShpmntDate < ''' + @mGETDATE + ''')
ORDER BY dbo.T_SetupSheetHistoryShipments.ShpmntDate )

I don't know why it did not work the first time I tired it, but here is what worked, which is wht I origionaly had:

ALTER FUNCTION dbo.FN_Shipping_QtyShippedByDate_WOIDPerShipper
(@FullItemID nvarchar(30),
@GETDATE smalldatetime)
RETURNS TABLE
AS
RETURN ( SELECT TOP 100 dbo.vShipping_ShpQty_PerPkInd.MultQty, dbo.vShipping_ShpQty_PerPkInd.ShpItem, dbo.vShipping_ShpQty_PerPkInd.ShpWOID,
                      dbo.vShipping_ShpQty_PerPkInd.ShpWOIDShipper, dbo.T_SetupSheetHistoryShipments.ShpmntDate,
                      dbo.T_SetupSheetHistoryShipments.ShpmntID
FROM dbo.vShipping_ShpQty_PerPkInd INNER JOIN
                      dbo.T_SetupSheetHistoryShipments ON dbo.vShipping_ShpQty_PerPkInd.ShpmntID = dbo.T_SetupSheetHistoryShipments.ShpmntID
GROUP BY dbo.vShipping_ShpQty_PerPkInd.MultQty, dbo.vShipping_ShpQty_PerPkInd.ShpItem, dbo.vShipping_ShpQty_PerPkInd.ShpWOID,
                      dbo.vShipping_ShpQty_PerPkInd.ShpWOIDShipper, dbo.T_SetupSheetHistoryShipments.ShpmntDate,
                      dbo.T_SetupSheetHistoryShipments.ShpmntID
HAVING (dbo.vShipping_ShpQty_PerPkInd.ShpItem IS NOT NULL) AND (dbo.vShipping_ShpQty_PerPkInd.ShpItem = @FullItemID) AND
                      (dbo.vShipping_ShpQty_PerPkInd.ShpWOID IS NOT NULL) AND (dbo.T_SetupSheetHistoryShipments.ShpmntDate < @GETDATE)
ORDER BY dbo.T_SetupSheetHistoryShipments.ShpmntDate )

You didn't ask, but I'll offer an opinion anyway. :D

It looks like you would be better off performance-wise moving the selection expressions from the HAVING clause to a WHERE clause.

Both HAVING and WHERE are similar, but the WHERE clause selects rows before the grouping operation, and the HAVING clause selects rows (groups actually) after the grouping operation. Since generally speaking a grouping operation is a resource intensive operation, if you can reduce the number of rows going in, the query will have less rows to group which should mean it will run faster.

Some other questions come to mind, like why you are grouping at all, why you are doing an ORDER by on the results returned by the function and indeed, why you are writing a function at all, when it looks like what you are doing is constructing a VIEW...

Of course, without knowing what you are trying to accomplish, this is all supposition on my part...;)

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com

Thanks for the thought. ;)

I am converting someone elses Access 97 queries into MS SQL 2000.

The Funcition in in there because I needed to duplicate the nested or sub (not sure what the difference is) qurey that uses parameters. The only way I could do that was with a Function. This one actually goes into another one.

I thank you for the performance suggestions, as I am "teaching myself" MS SQL all the help is appreciated. :)

Access isn't SQL.

You may in fact be better off constructing a view and then JOINing that view to your main query and allowing the optimizer to do the row selection for you from within the JOIN, rather than trying to use a function.

The reason that SQL Server doesn't allow non-deterministic functions within a user-defined function body is that the function may get called at different times within the query execution (you really can't control this) depending on the optimizer's choice of a query plan. Thus two different plans which might otherwise return the same results could in fact return different results if a non-deterministic function is allowed. So it isn't.

The thing to remember is that the function will get called on all the rows being processed - you just can't tell when or even necessarily on which rows in what order.

Your function is doing a rather involved query, and it will do this on every row for which it is invoked, which could be on every row involved in the main query. Or not, depending on the optimizer.

You may not be happy with the performance.

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com

Oh boy do I know that SQL is not Access ;)

The trouble that I have with using views, is that I can not use parameters. The only way that I have found (thus far) to duplicate what Access allows me to do with sub/nested/embeded queires (whatever they are called) is with an In-Line Function.

In "my thinking" I guess I could have my sub/nested/embeded queires create temp tables, then my "main" query could join to them (isn't that was queries do behind the scenes anyway?). I just don't know if that is a "good/fast" way to do it, nor do I know how to code, in T-SQL, for that.

I have actually learned a lot, being self taught, even if my teacher isn't all that good. ;)