|
Home > Archive > microsoft.public.sqlserver.server > November 2002 > UDF as expression in select statement
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
UDF as expression in select statement
|
|
| Koliazine Dmitry 2002-11-28, 7:23 am |
| Hi All
I have following statement:
select
col1,
col2
( select my_column from dbo.fnMyFunction( MyTable.col1 ) ) as col3
from
MyTable
Where dbo.fnMyFunction is inline table-valued function returning only one
row.
When running this statement SQL Server shows error "Incorrect syntax near
'.'"
If I use ( select my_column from dbo.fnMyFunction( col1 ) ) as col3 it shows
"'col1' is not a recognized OPTIMIZER LOCK HINTS option"
Is it possible to use inline table-valued function as expressions in select
statement?
If so then what is wrong in my script?
Thanks for any help
| |
| Uri Dimant 2002-11-28, 8:23 am |
| Koliazine
Look at this one,i hope it will help you
CREATE FUNCTION dbo.cust_orders
(@customerid AS nvarchar(5))
RETURNS varchar(1000)
AS
BEGIN
DECLARE @custorders AS varchar(1000)
SET @custorders = ''
SELECT @custorders = @custorders + CAST(orderid AS varchar(10)) + ';'
FROM Orders
WHERE customerid = @customerid
RETURN @custorders
END
GO
SELECT
customerid,
dbo.cust_orders(customerid) AS cust_orders
FROM Customers
Koliazine Dmitry <Kolyazine@yandex.ru> wrote in message
news:OnUfY5tlCHA.1928@tkmsftngp07...
> Hi All
>
> I have following statement:
> select
> col1,
> col2
> ( select my_column from dbo.fnMyFunction( MyTable.col1 ) ) as col3
> from
> MyTable
>
> Where dbo.fnMyFunction is inline table-valued function returning only one
> row.
> When running this statement SQL Server shows error "Incorrect syntax near
> '.'"
> If I use ( select my_column from dbo.fnMyFunction( col1 ) ) as col3 it
shows
> "'col1' is not a recognized OPTIMIZER LOCK HINTS option"
>
> Is it possible to use inline table-valued function as expressions in
select
> statement?
> If so then what is wrong in my script?
>
> Thanks for any help
>
>
>
| |
| Koliazine Dmitry 2002-11-28, 8:23 am |
| Yes, when using user defined scalar functions everything is ok.
But if I use inline table-valued one then error occur :-(
"Uri Dimant" <urid@iscar.co.il> wrote in message
news:esExFGulCHA.1568@tkmsftngp07...
> Koliazine
> Look at this one,i hope it will help you
>
>
>
> CREATE FUNCTION dbo.cust_orders
> (@customerid AS nvarchar(5))
> RETURNS varchar(1000)
> AS
> BEGIN
>
> DECLARE @custorders AS varchar(1000)
> SET @custorders = ''
>
> SELECT @custorders = @custorders + CAST(orderid AS varchar(10)) + ';'
> FROM Orders
> WHERE customerid = @customerid
>
> RETURN @custorders
>
> END
> GO
>
> SELECT
> customerid,
> dbo.cust_orders(customerid) AS cust_orders
> FROM Customers
>
>
>
>
>
>
> Koliazine Dmitry <Kolyazine@yandex.ru> wrote in message
> news:OnUfY5tlCHA.1928@tkmsftngp07...
> > Hi All
> >
> > I have following statement:
> > select
> > col1,
> > col2
> > ( select my_column from dbo.fnMyFunction( MyTable.col1 ) ) as col3
> > from
> > MyTable
> >
> > Where dbo.fnMyFunction is inline table-valued function returning only
one
> > row.
> > When running this statement SQL Server shows error "Incorrect syntax
near
> > '.'"
> > If I use ( select my_column from dbo.fnMyFunction( col1 ) ) as col3 it
> shows
> > "'col1' is not a recognized OPTIMIZER LOCK HINTS option"
> >
> > Is it possible to use inline table-valued function as expressions in
> select
> > statement?
> > If so then what is wrong in my script?
> >
> > Thanks for any help
> >
> >
> >
>
>
| |
| Tibor Karaszi 2002-11-28, 8:23 am |
| You're missing a comma between col2 and the third column.
Also, check your database compatibility level (just in case).
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=...ublic.sqlserver
"Koliazine Dmitry" <Kolyazine@yandex.ru> wrote in message news:OnUfY5tlCHA.1928@tkmsftngp07...
> Hi All
>
> I have following statement:
> select
> col1,
> col2
> ( select my_column from dbo.fnMyFunction( MyTable.col1 ) ) as col3
> from
> MyTable
>
> Where dbo.fnMyFunction is inline table-valued function returning only one
> row.
> When running this statement SQL Server shows error "Incorrect syntax near
> '.'"
> If I use ( select my_column from dbo.fnMyFunction( col1 ) ) as col3 it shows
> "'col1' is not a recognized OPTIMIZER LOCK HINTS option"
>
> Is it possible to use inline table-valued function as expressions in select
> statement?
> If so then what is wrong in my script?
>
> Thanks for any help
>
>
>
| |
| Tibor Karaszi 2002-11-28, 8:23 am |
| Can you post a repro (with required tables, udf etc so we can try)?
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=...ublic.sqlserver
"Koliazine Dmitry" <Kolyazine@yandex.ru> wrote in message news:O6J9iIulCHA.2592@tkmsftngp02...
> Yes, when using user defined scalar functions everything is ok.
> But if I use inline table-valued one then error occur :-(
>
> "Uri Dimant" <urid@iscar.co.il> wrote in message
> news:esExFGulCHA.1568@tkmsftngp07...
> > Koliazine
> > Look at this one,i hope it will help you
> >
> >
> >
> > CREATE FUNCTION dbo.cust_orders
> > (@customerid AS nvarchar(5))
> > RETURNS varchar(1000)
> > AS
> > BEGIN
> >
> > DECLARE @custorders AS varchar(1000)
> > SET @custorders = ''
> >
> > SELECT @custorders = @custorders + CAST(orderid AS varchar(10)) + ';'
> > FROM Orders
> > WHERE customerid = @customerid
> >
> > RETURN @custorders
> >
> > END
> > GO
> >
> > SELECT
> > customerid,
> > dbo.cust_orders(customerid) AS cust_orders
> > FROM Customers
> >
> >
> >
> >
> >
> >
> > Koliazine Dmitry <Kolyazine@yandex.ru> wrote in message
> > news:OnUfY5tlCHA.1928@tkmsftngp07...
> > > Hi All
> > >
> > > I have following statement:
> > > select
> > > col1,
> > > col2
> > > ( select my_column from dbo.fnMyFunction( MyTable.col1 ) ) as col3
> > > from
> > > MyTable
> > >
> > > Where dbo.fnMyFunction is inline table-valued function returning only
> one
> > > row.
> > > When running this statement SQL Server shows error "Incorrect syntax
> near
> > > '.'"
> > > If I use ( select my_column from dbo.fnMyFunction( col1 ) ) as col3 it
> > shows
> > > "'col1' is not a recognized OPTIMIZER LOCK HINTS option"
> > >
> > > Is it possible to use inline table-valued function as expressions in
> > select
> > > statement?
> > > If so then what is wrong in my script?
> > >
> > > Thanks for any help
> > >
> > >
> > >
> >
> >
>
>
| |
| Koliazine Dmitry 2002-11-28, 8:23 am |
| I've added comma but it did not help
My compatibility level is 80
"Tibor Karaszi" <tibor.please_reply_to_public_forum.karaszi@cornerstone.se>
wrote in message news:uTIetKulCHA.2144@tkmsftngp02...
> You're missing a comma between col2 and the third column.
>
> Also, check your database compatibility level (just in case).
>
> --
> Tibor Karaszi, SQL Server MVP
> Archive at:
http://groups.google.com/groups?oi=...ublic.sqlserver
>
>
> "Koliazine Dmitry" <Kolyazine@yandex.ru> wrote in message
news:OnUfY5tlCHA.1928@tkmsftngp07...
> > Hi All
> >
> > I have following statement:
> > select
> > col1,
> > col2
> > ( select my_column from dbo.fnMyFunction( MyTable.col1 ) ) as col3
> > from
> > MyTable
> >
> > Where dbo.fnMyFunction is inline table-valued function returning only
one
> > row.
> > When running this statement SQL Server shows error "Incorrect syntax
near
> > '.'"
> > If I use ( select my_column from dbo.fnMyFunction( col1 ) ) as col3 it
shows
> > "'col1' is not a recognized OPTIMIZER LOCK HINTS option"
> >
> > Is it possible to use inline table-valued function as expressions in
select
> > statement?
> > If so then what is wrong in my script?
> >
> > Thanks for any help
> >
> >
> >
>
>
| |
| Uri Dimant 2002-11-28, 8:23 am |
| Dmitry
My compatibility level is 70
CREATE FUNCTION fn_CustomerNamesInRegion
( @RegionParameter nvarchar(30) )
RETURNS table
AS
RETURN (
SELECT CustomerID, CompanyName
FROM Northwind.dbo.Customers
WHERE Region = @RegionParameter
)
GO
-- Example of calling the function for a specific region
SELECT *
FROM fn_CustomerNamesInRegion(N'WA'
)
GO
Inline user-defined functions follow these rules:
The RETURNS clause contains only the keyword table. You do not have to
define the format of a return variable because it is set by the format of
the result set of the SELECT statement in the RETURN clause.
Koliazine Dmitry <Kolyazine@yandex.ru> wrote in message
news:ecE95RulCHA.1244@tkmsftngp02...
> I've added comma but it did not help
> My compatibility level is 80
>
> "Tibor Karaszi"
<tibor.please_reply_to_public_forum.karaszi@cornerstone.se>
> wrote in message news:uTIetKulCHA.2144@tkmsftngp02...
> > You're missing a comma between col2 and the third column.
> >
> > Also, check your database compatibility level (just in case).
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > Archive at:
>
http://groups.google.com/groups?oi=...ublic.sqlserver
> >
> >
> > "Koliazine Dmitry" <Kolyazine@yandex.ru> wrote in message
> news:OnUfY5tlCHA.1928@tkmsftngp07...
> > > Hi All
> > >
> > > I have following statement:
> > > select
> > > col1,
> > > col2
> > > ( select my_column from dbo.fnMyFunction( MyTable.col1 ) ) as col3
> > > from
> > > MyTable
> > >
> > > Where dbo.fnMyFunction is inline table-valued function returning only
> one
> > > row.
> > > When running this statement SQL Server shows error "Incorrect syntax
> near
> > > '.'"
> > > If I use ( select my_column from dbo.fnMyFunction( col1 ) ) as col3 it
> shows
> > > "'col1' is not a recognized OPTIMIZER LOCK HINTS option"
> > >
> > > Is it possible to use inline table-valued function as expressions in
> select
> > > statement?
> > > If so then what is wrong in my script?
> > >
> > > Thanks for any help
> > >
> > >
> > >
> >
> >
>
>
| |
| Koliazine Dmitry 2002-11-28, 8:23 am |
| Here is sample script:
create table Test(
ID int identity not null,
OpDate datetime not null,
Amount money not null,
CrncID int not null
)
create table Rates(
RateDate datetime,
Rate numeric(28,8) not null,
CrncID int not null
)
go
create function fnGetRate( @OnDate datetime, @CrncID int )
returns table
as
return( select Rate from Rates where CrncID = @CrncID and RateDate = (
select Max( RateDate ) from Rates where CrncID = @CrncID and RateDate <=
@OnDate ) )
go
select
Amount,
CrncID,
( select Rate from dbo.fnGetRate( OpDate, CrncID ) ) as Rate
from Test
"Tibor Karaszi" <tibor.please_reply_to_public_forum.karaszi@cornerstone.se>
wrote in message news:OA1S$RulCHA.1488@tkmsftngp04...
> Can you post a repro (with required tables, udf etc so we can try)?
>
> --
> Tibor Karaszi, SQL Server MVP
> Archive at:
http://groups.google.com/groups?oi=...ublic.sqlserver
>
>
> "Koliazine Dmitry" <Kolyazine@yandex.ru> wrote in message
news:O6J9iIulCHA.2592@tkmsftngp02...
> > Yes, when using user defined scalar functions everything is ok.
> > But if I use inline table-valued one then error occur :-(
> >
> > "Uri Dimant" <urid@iscar.co.il> wrote in message
> > news:esExFGulCHA.1568@tkmsftngp07...
> > > Koliazine
> > > Look at this one,i hope it will help you
> > >
> > >
> > >
> > > CREATE FUNCTION dbo.cust_orders
> > > (@customerid AS nvarchar(5))
> > > RETURNS varchar(1000)
> > > AS
> > > BEGIN
> > >
> > > DECLARE @custorders AS varchar(1000)
> > > SET @custorders = ''
> > >
> > > SELECT @custorders = @custorders + CAST(orderid AS varchar(10)) +
';'[c
olor=darkred]
> > > FROM Orders
> > > WHERE customerid = @customerid
> > >
> > > RETURN @custorders
> > >
> > > END
> > > GO
> > >
> > > SELECT
> > > customerid,
> > > dbo.cust_orders(customerid) AS cust_orders
> > > FROM Customers
> > >
> > >
> > >
> > >
> > >
> > >
> > > Koliazine Dmitry <Kolyazine@yandex.ru> wrote in message
> > > news:OnUfY5tlCHA.1928@tkmsftngp07...
> > > > Hi All
> > > >
> > > > I have following statement:
> > > > select
> > > > col1,
> > > > col2
> > > > ( select my_column from dbo.fnMyFunction( MyTable.col1 ) ) as[/color]
col3[
color=darkred]
> > > > from
> > > > MyTable
> > > >
> > > > Where dbo.fnMyFunction is inline table-valued function returning[/color]
only
> > one
> > > > row.
> > > > When running this statement SQL Server shows error "Incorrect syntax
> > near
> > > > '.'"
> > > > If I use ( select my_column from dbo.fnMyFunction( col1 ) ) as col3
it[co
lor=darkred]
> > > shows
> > > > "'col1' is not a recognized OPTIMIZER LOCK HINTS option"
> > > >
> > > > Is it possible to use inline table-valued function as expressions in
> > > select
> > > > statement?
> > > > If so then what is wrong in my script?
> > > >
> > > > Thanks for any help
> > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
>[/color]
| |
| Tibor Karaszi 2002-11-28, 9:24 am |
| I get the same error. My guess that this is because you can't "pass in" column names from an
outer table into a table valued function when using it in a scalar subselect in the column list.
I gotta go, but perhaps Books Online confirms this? Can you re-write the function as a scalar
function?
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=...ublic.sqlserver
"Koliazine Dmitry" <Kolyazine@yandex.ru> wrote in message news:eibT6bulCHA.2116@tkmsftngp07...
> Here is sample script:
>
> create table Test(
> ID int identity not null,
> OpDate datetime not null,
> Amount money not null,
> CrncID int not null
> )
> create table Rates(
> RateDate datetime,
> Rate numeric(28,8) not null,
> CrncID int not null
> )
> go
>
> create function fnGetRate( @OnDate datetime, @CrncID int )
> returns table
> as
> return( select Rate from Rates where CrncID = @CrncID and RateDate = (
> select Max( RateDate ) from Rates where CrncID = @CrncID and RateDate <=
> @OnDate ) )
> go
>
> select
> Amount,
> CrncID,
> ( select Rate from dbo.fnGetRate( OpDate, CrncID ) ) as Rate
> from Test
>
>
> "Tibor Karaszi" <tibor.please_reply_to_public_forum.karaszi@cornerstone.se>
> wrote in message news:OA1S$RulCHA.1488@tkmsftngp04...
> > Can you post a repro (with required tables, udf etc so we can try)?
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > Archive at:
> http://groups.google.com/groups?oi=...ublic.sqlserver
> >
> >
> > "Koliazine Dmitry" <Kolyazine@yandex.ru> wrote in message
> news:O6J9iIulCHA.2592@tkmsftngp02...
> > > Yes, when using user defined scalar functions everything is ok.
> > > But if I use inline table-valued one then error occur :-(
> > >
> > > "Uri Dimant" <urid@iscar.co.il> wrote in message
> > > news:esExFGulCHA.1568@tkmsftngp07...
> > > > Koliazine
> > > > Look at this one,i hope it will help you
> > > >
> > > >
> > > >
> > > > CREATE FUNCTION dbo.cust_orders
> > > > (@customerid AS nvarchar(5))
> > > > RETURNS varchar(1000)
> > > > AS
> > > > BEGIN
> > > >
> > > > DECLARE @custorders AS varchar(1000)
> > > > SET @custorders = ''
> > > >
> > > > SELECT @custorders = @custorders + CAST(orderid AS varchar(10)) +
> ';'
> > > > FROM Orders
> > > > WHERE customerid = @customerid
> > > >
> > > > RETURN @custorders
> > > >
> > > > END
> > > > GO
> > > >
> > > > SELECT
> > > > customerid,
> > > > dbo.cust_orders(customerid) AS cust_orders
> > > > FROM Customers
> > > >
> > > >
> > > >
> > > >
> > > >
> > > >
> > > > Koliazine Dmitry <Kolyazine@yandex.ru> wrote in message
> > > > news:OnUfY5tlCHA.1928@tkmsftngp07...
> > > > > Hi All
> > > > >
> > > > > I have following statement:
> > > > > select
> > > > > col1,
> > > > > col2
> > > > > ( select my_column from dbo.fnMyFunction( MyTable.col1 ) ) as
> col3
> > > > > from
> > > > > MyTable
> > > > >
> > > > > Where dbo.fnMyFunction is inline table-valued function returning
> only
> > > one
> > > > > row.
> > > > > When running this statement SQL Server shows error "Incorrect syntax
> > > near
> > > > > '.'"
> > > > > If I use ( select my_column from dbo.fnMyFunction( col1 ) ) as col3
> it
> > > > shows
> > > > > "'col1' is not a recognized OPTIMIZER LOCK HINTS option"
> > > > >
> > > > > Is it possible to use inline table-valued function as expressions in
> > > > select
> > > > > statement?
> > > > > If so then what is wrong in my script?
> > > > >
> > > > > Thanks for any help
> > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
>
| |
| Koliazine Dmitry 2002-11-28, 10:23 am |
| Yes, I've already tried to do so and it works ok. But I wanted to use this
ability to reduce my script sizes. I could use nested subquery returning the
same result and in this case select statement works fast but it is necessary
to repeat long code in all cases where it is needed. When using scalar
function then speed of select statement is decreased 
"Tibor Karaszi" <tibor.please_reply_to_public_forum.karaszi@cornerstone.se>
wrote in message news:ub$DOEvlCHA.1924@tkmsftngp04...
> I get the same error. My guess that this is because you can't "pass in"
column names from an
> outer table into a table valued function when using it in a scalar
subselect in the column list.
> I gotta go, but perhaps Books Online confirms this? Can you re-write the
function as a scalar
> function?
>
> --
> Tibor Karaszi, SQL Server MVP
> Archive at:
http://groups.google.com/groups?oi=...ublic.sqlserver
>
>
> "Koliazine Dmitry" <Kolyazine@yandex.ru> wrote in message
news:eibT6bulCHA.2116@tkmsftngp07...
> > Here is sample script:
> >
> > create table Test(
> > ID int identity not null,
> > OpDate datetime not null,
> > Amount money not null,
> > CrncID int not null
> > )
> > create table Rates(
> > RateDate datetime,
> > Rate numeric(28,8) not null,
> > CrncID int not null
> > )
> > go
> >
> > create function fnGetRate( @OnDate datetime, @CrncID int )
> > returns table
> > as
> > return( select Rate from Rates where CrncID = @CrncID and RateDate = (
> > select Max( RateDate ) from Rates where CrncID = @CrncID and RateDate <=
> > @OnDate ) )
> > go
> >
> > select
> > Amount,
> > CrncID,
> > ( select Rate from dbo.fnGetRate( OpDate, CrncID ) ) as Rate
> > from Test
> >
> >
> > "Tibor Karaszi"
<tibor.please_reply_to_public_forum.karaszi@cornerstone.se>
> > wrote in message news:OA1S$RulCHA.1488@tkmsftngp04...
> > > Can you post a repro (with required tables, udf etc so we can try)?
> > >
> > > --
> > > Tibor Karaszi, SQL Server MVP
> > > Archive at:
> >
[url]http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver[/ url][
color=darkred]
> > >
> > >
> > > "Koliazine Dmitry" <Kolyazine@yandex.ru> wrote in message
> > news:O6J9iIulCHA.2592@tkmsftngp02...
> > > > Yes, when using user defined scalar functions everything is ok.
> > > > But if I use inline table-valued one then error occur :-(
> > > >
> > > > "Uri Dimant" <urid@iscar.co.il> wrote in message
> > > > news:esExFGulCHA.1568@tkmsftngp07...
> > > > > Koliazine
> > > > > Look at this one,i hope it will help you
> > > > >
> > > > >
> > > > >
> > > > > CREATE FUNCTION dbo.cust_orders
> > > > > (@customerid AS nvarchar(5))
> > > > > RETURNS varchar(1000)
> > > > > AS
> > > > > BEGIN
> > > > >
> > > > > DECLARE @custorders AS varchar(1000)
> > > > > SET @custorders = ''
> > > > >
> > > > > SELECT @custorders = @custorders + CAST(orderid AS varchar(10))[/color]
+
> > ';'
> > > > > FROM Orders
> > > > > WHERE customerid = @customerid
> > > > >
> > > > > RETURN @custorders
> > > > >
> > > > > END
> > > > > GO
> > > > >
> > > > > SELECT
> > > > > customerid,
> > > > > dbo.cust_orders(customerid) AS cust_orders
> > > > > FROM Customers
> > > > >
> > > > >
> > > > >
> > > > >
> > > > >
> > > > >
> > > > > Koliazine Dmitry <Kolyazine@yandex.ru> wrote in message
> > > > > news:OnUfY5tlCHA.1928@tkmsftngp07...
> > > > > > Hi All
> > > > > >
> > > > > > I have following statement:
> > > > > > select
> > > > > > col1,
> > > > > > col2
> > > > > > ( select my_column from dbo.fnMyFunction( MyTable.col1 ) )
as
> > col3
> > > > > > from
> > > > > > MyTable
> > > > > >
> > > > > > Where dbo.fnMyFunction is inline table-valued function returning
> > only
> > > > one
> > > > > > row.
> > > > > > When running this statement SQL Server shows error "Incorrect
syntax
> > > > near
> > > > > > '.'"
> > > > > > If I use ( select my_column from dbo.fnMyFunction( col1 ) ) as
col3
> > it
> > > > > shows
> > > > > > "'col1' is not a recognized OPTIMIZER LOCK HINTS option"
> > > > > >
> > > > > > Is it possible to use inline table-valued function as
expressions in[co
lor=darkred]
> > > > > select
> > > > > > statement?
> > > > > > If so then what is wrong in my script?
> > > > > >
> > > > > > Thanks for any help
> > > > > >
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
>[/color]
| |
| Koliazine Dmitry 2002-11-28, 10:23 am |
| Yes, It works for me as well
But I need to use my function as expression in column list in the select
statement.
By the way, I've already sent my test script to this post and you can see
and check it
"Uri Dimant" <urid@iscar.co.il> wrote in message
news:OCAElZulCHA.2800@tkmsftngp04...
> Dmitry
> My compatibility level is 70
> CREATE FUNCTION fn_CustomerNamesInRegion
> ( @RegionParameter nvarchar(30) )
> RETURNS table
> AS
> RETURN (
> SELECT CustomerID, CompanyName
> FROM Northwind.dbo.Customers
> WHERE Region = @RegionParameter
> )
> GO
> -- Example of calling the function for a specific region
> SELECT *
> FROM fn_CustomerNamesInRegion(N'WA'
)
> GO
>
> Inline user-defined functions follow these rules:
>
> The RETURNS clause contains only the keyword table. You do not have to
> define the format of a return variable because it is set by the format of
> the result set of the SELECT statement in the RETURN clause.
>
>
> Koliazine Dmitry <Kolyazine@yandex.ru> wrote in message
> news:ecE95RulCHA.1244@tkmsftngp02...
> > I've added comma but it did not help
> > My compatibility level is 80
> >
> > "Tibor Karaszi"
> <tibor.please_reply_to_public_forum.karaszi@cornerstone.se>
> > wrote in message news:uTIetKulCHA.2144@tkmsftngp02...
> > > You're missing a comma between col2 and the third column.
> > >
> > > Also, check your database compatibility level (just in case).
> > >
> > > --
> > > Tibor Karaszi, SQL Server MVP
> > > Archive at:
> >
>
[url]http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver[/ url][
color=darkred]
> > >
> > >
> > > "Koliazine Dmitry" <Kolyazine@yandex.ru> wrote in message
> > news:OnUfY5tlCHA.1928@tkmsftngp07...
> > > > Hi All
> > > >
> > > > I have following statement:
> > > > select
> > > > col1,
> > > > col2
> > > > ( select my_column from dbo.fnMyFunction( MyTable.col1 ) ) as[/color]
col3[
color=darkred]
> > > > from
> > > > MyTable
> > > >
> > > > Where dbo.fnMyFunction is inline table-valued function returning[/color]
only
> > one
> > > > row.
> > > > When running this statement SQL Server shows error "Incorrect syntax
> > near
> > > > '.'"
> > > > If I use ( select my_column from dbo.fnMyFunction( col1 ) ) as col3
it
> > shows
> > > > "'col1' is not a recognized OPTIMIZER LOCK HINTS option"
> > > >
> > > > Is it possible to use inline table-valued function as expressions in
> > select
> > > > statement?
> > > > If so then what is wrong in my script?
> > > >
> > > > Thanks for any help
> > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
>
|
|
|
|
|