'field name' is not a recognized OPTIMIZER LOCK HINTS option

I get 'field name' is not a recognized OPTIMIZER LOCK HINTS option
when running the following query:
select
(SELECT
listdate FROM DateList2(ejDateBeg, epDateBeg, eeDateBeg,
euDateMod, HRTimeStamp) AS ChangeDate
FROM tablea Emp INNER JOIN Tableb HR
ON Emp.col1= HR.col1)
The function DateList2 is defined as so:
create FUNCTION [dbo].[DateList]
(
@.date1 smalldatetime,
@.date2 smalldatetime,
@.date3 smalldatetime,
@.date4 smalldatetime,
@.date5 smalldatetime
)
RETURNS TABLE
AS
RETURN
(
-- Add the SELECT statement with parameter references here
SELECT @.date1 as ListDate UNION
SELECT @.date2 as ListDate UNION
SELECT @.date3 as ListDate UNION
SELECT @.date4 as ListDate UNION
SELECT @.date5 as ListDate
)
The intent is to grab the highest date form a set of 5 dates that come from
either tablea or tableb and pass it as ChangeDate.
Any assistance would be appreciated.
Thanks,
DaveHi Dave
My guess is that the parser thinks DateList2 is a table, not a function, so
the parens following the name are thought to be a hint.
User defined functions must be qualified with their owner name to help the
parser distinguish them from tables. For example, if the owner of the
function is dbo, you can do this:
select
( SELECT
listdate FROM dbo.DateList2(ejDateBeg, epDateBeg, eeDateBeg,
euDateMod, HRTimeStamp) AS ChangeDate
...
HTH
Kalen Delaney, SQL Server MVP
"Dave Sundell" <DaveSundell@.discussions.microsoft.com> wrote in message
news:FE9D8229-391D-4823-8079-D7CDD8155A5C@.microsoft.com...
>I get 'field name' is not a recognized OPTIMIZER LOCK HINTS option
> when running the following query:
> select
> (SELECT
> listdate FROM DateList2(ejDateBeg, epDateBeg, eeDateBeg,
> euDateMod, HRTimeStamp) AS ChangeDate
> FROM tablea Emp INNER JOIN Tableb HR
> ON Emp.col1= HR.col1)
> The function DateList2 is defined as so:
> create FUNCTION [dbo].[DateList]
> (
> @.date1 smalldatetime,
> @.date2 smalldatetime,
> @.date3 smalldatetime,
> @.date4 smalldatetime,
> @.date5 smalldatetime
> )
> RETURNS TABLE
> AS
> RETURN
> (
> -- Add the SELECT statement with parameter references here
> SELECT @.date1 as ListDate UNION
> SELECT @.date2 as ListDate UNION
> SELECT @.date3 as ListDate UNION
> SELECT @.date4 as ListDate UNION
> SELECT @.date5 as ListDate
> )
> The intent is to grab the highest date form a set of 5 dates that come
> from
> either tablea or tableb and pass it as ChangeDate.
> Any assistance would be appreciated.
> Thanks,
> Dave|||karen,
Thanks for responding. I did put dbo. and it still didn't work. It is like
the compiler is getting lost.
Dave
"Kalen Delaney" wrote:

> Hi Dave
> My guess is that the parser thinks DateList2 is a table, not a function, s
o
> the parens following the name are thought to be a hint.
> User defined functions must be qualified with their owner name to help th
e
> parser distinguish them from tables. For example, if the owner of the
> function is dbo, you can do this:
> select
> ( SELECT
> listdate FROM dbo.DateList2(ejDateBeg, epDateBeg, eeDateBeg,
> euDateMod, HRTimeStamp) AS ChangeDate
> ....
>
> --
> HTH
> Kalen Delaney, SQL Server MVP
>
> "Dave Sundell" <DaveSundell@.discussions.microsoft.com> wrote in message
> news:FE9D8229-391D-4823-8079-D7CDD8155A5C@.microsoft.com...
>
>|||One more thing. I even hardcoded dates, '1/1/2006' or passed null and it wor
ks.
"Dave Sundell" wrote:
> karen,
> Thanks for responding. I did put dbo. and it still didn't work. It is like
> the compiler is getting lost.
> Dave
> "Kalen Delaney" wrote:
>|||Dave,
In SQL Server 2000, you cannot pass a column to a table-valued
function. SQL Server 2000 can't handle a correlated table source,
but SQL Server 2005 can, if you use the APPLY operator.
It doesn't look to me as though what you are doing will work
anyway, since dbo.DateList may contain as many as 5 rows, and
if it does contain more than one, the sub-SELECT will raise an
error.
Try something like this if you are using 2000.
select
thisCol, thatCol,
(
select top 1 listdate from (
(select ejDateBeg as listdate union all
select epDateBeg union all select eeDateBeg
union all select euDateMod union all select HRTimeStamp
) as T
order by listdate desc
) as ChangeDate
from tablea as Emp ...
If this doesn't work (and I vaguely recall some quirks in
SQL Server 2000 when things are nested this deeply), please
post the CREATE TABLE statements and sample data sufficient
to reproduce the scenario (http://www.aspfaq.com/etiquette.asp?id=5006)
Steve Kass
Drew University
Dave Sundell wrote:

>I get 'field name' is not a recognized OPTIMIZER LOCK HINTS option
>when running the following query:
>select
>(SELECT
> listdate FROM DateList2(ejDateBeg, epDateBeg, eeDateBeg,
>euDateMod, HRTimeStamp) AS ChangeDate
>FROM tablea Emp INNER JOIN Tableb HR
> ON Emp.col1= HR.col1)
>The function DateList2 is defined as so:
>create FUNCTION [dbo].[DateList]
>(
> @.date1 smalldatetime,
> @.date2 smalldatetime,
> @.date3 smalldatetime,
> @.date4 smalldatetime,
> @.date5 smalldatetime
> )
>RETURNS TABLE
>AS
>RETURN
>(
> -- Add the SELECT statement with parameter references here
> SELECT @.date1 as ListDate UNION
> SELECT @.date2 as ListDate UNION
> SELECT @.date3 as ListDate UNION
> SELECT @.date4 as ListDate UNION
> SELECT @.date5 as ListDate
> )
>The intent is to grab the highest date form a set of 5 dates that come from
>either tablea or tableb and pass it as ChangeDate.
>Any assistance would be appreciated.
>Thanks,
>Dave
>|||You cannot pass columns as params for a Table valued UDF.
For fixing it, since you need only one value from that function, you can use
a scalar valued function.
Your query (as you have provided) will however work is SQL Server 2005 as
long as the UDF returns only one row.
Maybe you would want to go through this discussion :)
http://groups.google.co.in/group/mi...4b44dbec88e80d6
Hope this helps.
--
-Omnibuzz (The SQL GC)
http://omnibuzz-sql.blogspot.com/|||Thanks to all (Karen, Steve, OmniBuzz) that responded. You were all a big he
lp.
Dave
"Omnibuzz" wrote:

> You cannot pass columns as params for a Table valued UDF.
> For fixing it, since you need only one value from that function, you can u
se
> a scalar valued function.
> Your query (as you have provided) will however work is SQL Server 2005 as
> long as the UDF returns only one row.
> Maybe you would want to go through this discussion :)
> http://groups.google.co.in/group/mi...4b44dbec88e80d6
> Hope this helps.
> --
> -Omnibuzz (The SQL GC)
> http://omnibuzz-sql.blogspot.com/
>