locked
Why my function is not working with a datetime argument? RRS feed

  • Question

  • I have 2 tables payroll_period and position. I have a Table-valued Function udf_GetEmployeeCurrPermPositions which accepts a datetime argument.

    The function is working ok if I provide it a hard-coded argument:

    select * from dbo.udf_GetEmployeeCurrPermPositions('2008-02-17 23:59:59')

    But if the argument is a value from a column of another table like this:

    select * from dbo.udf_GetEmployeeCurrPermPositions(select end_date from payroll_period where payroll_period_id = 240)
    

    it is giving me this error message: 

    Msg 156, Level 15, State 1, Line 1
    Incorrect syntax near the keyword 'select'.
    Msg 102, Level 15, State 1, Line 1
    Incorrect syntax near ')'.

    Why it is not working? 

    These are the SQLs of table and function:

    create table payroll_period (
    	payroll_period_id bigint not null primary key,
    	start_date datetime not null,
    	end_date datetime not null )	
    GO
    
    create table position (
    	position_id bigint not null primary key,
    	employee_id bigint not null,
    	pos_start_date datetime not null,
    	pos_end_date datetime null,
    	location_code int not null,
    	department_code int not null,
    	job_code int not null,
    	base_rate decimal(10, 2) null )
    GO
    	
    create function [dbo].[udf_GetEmployeeCurrPermPositions]
    (	
    	@a_as_of_date		datetime
    )
    RETURNS TABLE 
    AS
    RETURN 
    (
    	select 
    		employee_id,
    		MAX(pos_start_date) 'pos_start_date'
    	from 
    		position with (nolock) 
    	where 
    		(pos_end_date is null) and 
    		(pos_start_date <= @a_as_of_date)
    	group by 
    		employee_id 
    )
    GO
    


    Tuesday, September 11, 2012 6:28 PM

Answers

  • You could use a variable to hold the scalar value from the query, and pass the variable as the parameter. Try also surrounding the query with parentheses or using the APPLY operator.

    select * from dbo.udf_GetEmployeeCurrPermPositions( (select end_date from payroll_period where payroll_period_id = 240) );

    select B.*
    from payroll_period as P cross apply dbo.udf_GetEmployeeCurrPermPositions(P.end_date) as B
    where payroll_period_id = 240;


    AMB

    Some guidelines for posting questions...


    • Edited by HunchbackMVP Tuesday, September 11, 2012 6:38 PM
    • Proposed as answer by Naomi N Tuesday, September 11, 2012 7:07 PM
    • Marked as answer by srhcan1 Tuesday, September 11, 2012 7:36 PM
    Tuesday, September 11, 2012 6:32 PM
  • You can not call table-valued function with the syntax used to call scalar function. As Hunchback showed, you may want to use CROSS APPLY operator, e.g.

    select T.*, F.* from Payroll_Period T CROSS APPLY dbo.udf_GetEmployeeCurrPermPositions (T.end_date) F

    where T.Payroll_Period_id = 240


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    • Marked as answer by Kent Waldrop Tuesday, October 2, 2012 4:51 PM
    Tuesday, September 11, 2012 7:10 PM
  • Yet another way:

    select
      xa1.*
    from payroll_Period
    cross apply dbo.udf_getEmployeeCurrPermPositions(end_Date) as xa1
    where payroll_Period_id = 240


    Note: This assumes that you mean "payroll_Period_Id" = 240 -- is that the case?

    Also, give this a look as a general method of employing table functions; the CROSS APPLY operator provides a powerful tool that is not available in other RDBMS for working with table functions.

    EDIT:

    Something to note is that the performance of this particular function might be greatly improved by the addition of indexes to your tables.  But of course, that is a bit of a different question.

    EDIT II:

    ( Oh brother, I just saw Alejandro's solution.  Yes, I like Alejandro's solution. )

    :)

    • Edited by Kent Waldrop Tuesday, September 11, 2012 6:46 PM
    • Marked as answer by Kent Waldrop Tuesday, October 2, 2012 4:49 PM
    Tuesday, September 11, 2012 6:40 PM
  • Hmm strange, try prefixing the database name to the function. IE. My database is called John so it would be John.Dbo.udf_GetEmployeeCurrPermPositions.

    In theory it's good practice to name database, schema and table name all in one, at least the way I've been taught anyway.

    It is a table-valued function, so it doesn't make much sense referencing it in the SELECT clause.


    AMB

    Some guidelines for posting questions...

    • Marked as answer by Kent Waldrop Tuesday, October 2, 2012 4:52 PM
    Tuesday, September 11, 2012 7:05 PM
  • select dbo.udf_GetEmployeeCurrPermPositions(end_date) 
    from payroll_period
    where payroll_period_id = 240

    Simple enough :) You use a Table-Valued Function as if it were a Column Name in this case, and pass it a column in brackets.

    What you did there is a syntax error, syntax errors almost always mean that you've broken a basic SQL Standard. Read up on windowed queries a bit more :) 

    Thanks.


    When I run your query I got this error message:

    Msg 4121, Level 16, State 1, Line 1
    Cannot find either column "dbo" or the user-defined function or aggregate "dbo.udf_GetEmployeeCurrPermPositions", or the name is ambiguous.

    The problem with these queries is that the table function is referenced as if it were a scalar function and this doesn't work for a table function.  Give a look at Hunchback's first post.  It gives good examples of the tecniques for this, but especially give a look a the second method using the CROSS APPLY operator.

    Also note the "select * from " syntax.  A something to remember is that scalar function will work like a cell or a single row-value for a single column of a table.  A table function, on the other hand, works more like a view and you might want to think of a table function as a view that accepts parameters.

    • Marked as answer by Kent Waldrop Tuesday, October 2, 2012 4:52 PM
    Tuesday, September 11, 2012 7:06 PM

All replies

  • You could use a variable to hold the scalar value from the query, and pass the variable as the parameter. Try also surrounding the query with parentheses or using the APPLY operator.

    select * from dbo.udf_GetEmployeeCurrPermPositions( (select end_date from payroll_period where payroll_period_id = 240) );

    select B.*
    from payroll_period as P cross apply dbo.udf_GetEmployeeCurrPermPositions(P.end_date) as B
    where payroll_period_id = 240;


    AMB

    Some guidelines for posting questions...


    • Edited by HunchbackMVP Tuesday, September 11, 2012 6:38 PM
    • Proposed as answer by Naomi N Tuesday, September 11, 2012 7:07 PM
    • Marked as answer by srhcan1 Tuesday, September 11, 2012 7:36 PM
    Tuesday, September 11, 2012 6:32 PM
  • try :

    select dbo.udf_GetEmployeeCurrPermPositions(end_Date)
     from dbo.payroll_period 
     where payroll_period_id = 240

    or

    Declare @enddate datetime = (select end_date from payroll_period where payroll_period_id = 240)
     
     Select * from dbo.udf_GetEmployeeCurrPermPositions(@enddate)

    • Edited by JR1811 Tuesday, September 11, 2012 6:38 PM
    Tuesday, September 11, 2012 6:34 PM
  • select dbo.udf_GetEmployeeCurrPermPositions(end_date) 
    from payroll_period
    where payroll_period_id = 240

    Simple enough :) You use a Table-Valued Function as if it were a Column Name in this case, and pass it a column in brackets.

    What you did there is a syntax error, syntax errors almost always mean that you've broken a basic SQL Standard. Read up on windowed queries a bit more :) 

    Thanks.


    Tuesday, September 11, 2012 6:39 PM
  • Yet another way:

    select
      xa1.*
    from payroll_Period
    cross apply dbo.udf_getEmployeeCurrPermPositions(end_Date) as xa1
    where payroll_Period_id = 240


    Note: This assumes that you mean "payroll_Period_Id" = 240 -- is that the case?

    Also, give this a look as a general method of employing table functions; the CROSS APPLY operator provides a powerful tool that is not available in other RDBMS for working with table functions.

    EDIT:

    Something to note is that the performance of this particular function might be greatly improved by the addition of indexes to your tables.  But of course, that is a bit of a different question.

    EDIT II:

    ( Oh brother, I just saw Alejandro's solution.  Yes, I like Alejandro's solution. )

    :)

    • Edited by Kent Waldrop Tuesday, September 11, 2012 6:46 PM
    • Marked as answer by Kent Waldrop Tuesday, October 2, 2012 4:49 PM
    Tuesday, September 11, 2012 6:40 PM
  • Sorry, Kent. I added that suggestion later, so that could be the reason you didn't see it.


    AMB

    Some guidelines for posting questions...

    Tuesday, September 11, 2012 6:52 PM
  • select dbo.udf_GetEmployeeCurrPermPositions(end_date) 
    from payroll_period
    where payroll_period_id = 240

    Simple enough :) You use a Table-Valued Function as if it were a Column Name in this case, and pass it a column in brackets.

    What you did there is a syntax error, syntax errors almost always mean that you've broken a basic SQL Standard. Read up on windowed queries a bit more :) 

    Thanks.


    When I run your query I got this error message:

    Msg 4121, Level 16, State 1, Line 1
    Cannot find either column "dbo" or the user-defined function or aggregate "dbo.udf_GetEmployeeCurrPermPositions", or the name is ambiguous.

    Tuesday, September 11, 2012 6:53 PM
  • try :

    select dbo.udf_GetEmployeeCurrPermPositions(end_Date)
     from dbo.payroll_period 
     where payroll_period_id = 240

    or

    Declare @enddate datetime = (select end_date from payroll_period where payroll_period_id = 240)
     
     Select * from dbo.udf_GetEmployeeCurrPermPositions(@enddate)

    The 1st query is giving me an error message:

    Msg 4121, Level 16, State 1, Line 1
    Cannot find either column "dbo" or the user-defined function or aggregate "dbo.udf_GetEmployeeCurrPermPositions", or the name is ambiguous.

    I cannot use a variable to hold the value to be used for the query to get my results. Its not an option. 

    Tuesday, September 11, 2012 6:56 PM
  • Sorry, Kent. I added that suggestion later, so that could be the reason you didn't see it.


    AMB

    Some guidelines for posting questions...

    No worries, my friend.  We have been working together a long time and it is and always has been enjoyable and educational.  It is just another case where you made me laugh or smile.

    :)

    Tuesday, September 11, 2012 7:02 PM
  • Hmm strange, try prefixing the database name to the function. IE. My database is called John so it would be John.Dbo.udf_GetEmployeeCurrPermPositions.

    In theory it's good practice to name database, schema and table name all in one, at least the way I've been taught anyway.

    Tuesday, September 11, 2012 7:03 PM
  • Hmm strange, try prefixing the database name to the function. IE. My database is called John so it would be John.Dbo.udf_GetEmployeeCurrPermPositions.

    In theory it's good practice to name database, schema and table name all in one, at least the way I've been taught anyway.

    It is a table-valued function, so it doesn't make much sense referencing it in the SELECT clause.


    AMB

    Some guidelines for posting questions...

    • Marked as answer by Kent Waldrop Tuesday, October 2, 2012 4:52 PM
    Tuesday, September 11, 2012 7:05 PM
  • select dbo.udf_GetEmployeeCurrPermPositions(end_date) 
    from payroll_period
    where payroll_period_id = 240

    Simple enough :) You use a Table-Valued Function as if it were a Column Name in this case, and pass it a column in brackets.

    What you did there is a syntax error, syntax errors almost always mean that you've broken a basic SQL Standard. Read up on windowed queries a bit more :) 

    Thanks.


    When I run your query I got this error message:

    Msg 4121, Level 16, State 1, Line 1
    Cannot find either column "dbo" or the user-defined function or aggregate "dbo.udf_GetEmployeeCurrPermPositions", or the name is ambiguous.

    The problem with these queries is that the table function is referenced as if it were a scalar function and this doesn't work for a table function.  Give a look at Hunchback's first post.  It gives good examples of the tecniques for this, but especially give a look a the second method using the CROSS APPLY operator.

    Also note the "select * from " syntax.  A something to remember is that scalar function will work like a cell or a single row-value for a single column of a table.  A table function, on the other hand, works more like a view and you might want to think of a table function as a view that accepts parameters.

    • Marked as answer by Kent Waldrop Tuesday, October 2, 2012 4:52 PM
    Tuesday, September 11, 2012 7:06 PM
  • Hmm strange, try prefixing the database name to the function. IE. My database is called John so it would be John.Dbo.udf_GetEmployeeCurrPermPositions.

    In theory it's good practice to name database, schema and table name all in one, at least the way I've been taught anyway.

    I did it:

    select work_test.dbo.udf_GetEmployeeCurrPermPositions(end_date) from payroll_period where payroll_period_id = 240

    And it did not work. Got this error message:

    Msg 4121, Level 16, State 1, Line 1
    Cannot find either column "work_test" or the user-defined function or aggregate "work_test.dbo.udf_GetEmployeeCurrPermPositions", or the name is ambiguous.

    Tuesday, September 11, 2012 7:06 PM
  • You can not call table-valued function with the syntax used to call scalar function. As Hunchback showed, you may want to use CROSS APPLY operator, e.g.

    select T.*, F.* from Payroll_Period T CROSS APPLY dbo.udf_GetEmployeeCurrPermPositions (T.end_date) F

    where T.Payroll_Period_id = 240


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    • Marked as answer by Kent Waldrop Tuesday, October 2, 2012 4:51 PM
    Tuesday, September 11, 2012 7:10 PM
  • Take a look at this blog post 

    SQL Advent 2011 Day 16: CROSS APPLY and OUTER APPLY

    which hopefully explains this topic in a simple terms.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    • Marked as answer by Kent Waldrop Tuesday, October 2, 2012 4:51 PM
    • Unmarked as answer by Kent Waldrop Tuesday, October 2, 2012 4:51 PM
    Tuesday, September 11, 2012 7:17 PM