Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > SQL Server > SQL Server 2000 > SQL Server 2000
Password Reminder
Register
Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
SQL Server 2000 General discussion of Microsoft SQL Server -- for topics that don't fit in one of the more specific SQL Server forums. version 2000 only. There's a new forum for SQL Server 2005.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server 2000 section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old March 12th, 2004, 05:45 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: South Bend, Indiana, USA.
Posts: 149
Thanks: 0
Thanked 0 Times in 0 Posts
Default Using GETDATE in an In-Line function

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
Reply With Quote
  #2 (permalink)  
Old March 12th, 2004, 05:50 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: Hudson, MA, USA.
Posts: 839
Thanks: 0
Thanked 1 Time in 1 Post
Default

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
Reply With Quote
  #3 (permalink)  
Old March 13th, 2004, 05:33 AM
Authorized User
 
Join Date: Jun 2003
Location: , , Norway.
Posts: 95
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
Reply With Quote
  #4 (permalink)  
Old March 14th, 2004, 09:23 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: South Bend, Indiana, USA.
Posts: 149
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Gert,

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

Thanks.
Reply With Quote
  #5 (permalink)  
Old March 15th, 2004, 10:35 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: South Bend, Indiana, USA.
Posts: 149
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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 )
Reply With Quote
  #6 (permalink)  
Old March 15th, 2004, 11:33 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: South Bend, Indiana, USA.
Posts: 149
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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 )
Reply With Quote
  #7 (permalink)  
Old March 15th, 2004, 12:43 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: Hudson, MA, USA.
Posts: 839
Thanks: 0
Thanked 1 Time in 1 Post
Default

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
Reply With Quote
  #8 (permalink)  
Old March 15th, 2004, 12:59 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: South Bend, Indiana, USA.
Posts: 149
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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. :)
Reply With Quote
  #9 (permalink)  
Old March 15th, 2004, 02:59 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: Hudson, MA, USA.
Posts: 839
Thanks: 0
Thanked 1 Time in 1 Post
Default

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
Reply With Quote
  #10 (permalink)  
Old March 15th, 2004, 03:44 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: South Bend, Indiana, USA.
Posts: 149
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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. ;)
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
replace function for line breaks crazeydazey XSLT 6 September 1st, 2008 03:53 AM
mail() function and From line allgoodnamesaregone Beginning PHP 1 January 19th, 2006 09:04 AM
GETDATE? drachx SQL Server 2000 2 February 17th, 2005 08:05 AM
retreive function/Line from macro or function? MikoMax J2EE 0 April 1st, 2004 04:42 AM
GETDATE() dose not......... arshad mahmood SQL Server 2000 2 August 19th, 2003 07:57 AM



All times are GMT -4. The time now is 02:01 PM.


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.