Transact SQL Subquery calling a function incorrect syntax

Question

I get an incorrect syntax near '.' and can't seem to identify why in the following code:

select 
o.object_id,
(select top 1 Zone from dbo.getzone(o.object_id)) as Zone from object as o

getzone is a table-valued Function that works perfectly when I reference it directly, or if I put a specific object_id in, but everytime I try to make it dynamic, I get the syntax error.

What am I missing?

Your alias is wrong. The final part of the statement should say 'from object o' — Bob Tway
– Bob Tway, Commented Apr 17, 2015 at 16:17
You are correct, the aliasing for object was incorrect. However, changing that doesn't help the actual syntax error that was firing on this part of the code: dbo.getzone(o.object_id) — Paul
– Paul, Commented Apr 17, 2015 at 17:17
I would try what @Necreax said, but you might also try putting brackets around your object table and object_id column. Both are reserved words, which it may not like. Example [object] instead of just object. — Adam Scharp
– Adam Scharp, Commented Apr 17, 2015 at 18:16

Necreaux · Accepted Answer · 2015-04-17 17:37:38Z

1

You can't do that. You need to have a scalar version that returns only one result. It can be just a wrapper script if you want. Something like this:

CREATE FUNCTION [dbo].[getSingleZone](@object_id varchar(20))
RETURNS varchar(20)
AS
BEGIN
DECLARE @Zone varchar(20)
select @Zone = max(Zone) from dbo.getzone(@object_id)
return @Zone 
END

select 
o.object_id,
dbo.getSingleZone(o.object_id) as Zone from object o

I don't know your data types, so I guessed.

answered Apr 17, 2015 at 17:37

Necreaux

9,8248 gold badges29 silver badges43 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Stephan · Accepted Answer · 2015-04-17 16:48:47Z

1

Fix your alias

select  o.object_id, 
        (select top 1 Zone from dbo.getzone(o.object_id)) as Zone 
from object AS o

answered Apr 17, 2015 at 16:48

Stephan

6,0581 gold badge19 silver badges26 bronze badges

1 Comment

Paul Over a year ago

Thanks for catching that. The alias doesn't seem to be the issue though, as I still get a syntax error call on the Function call.

HABO · Accepted Answer · 2015-04-17 20:06:54Z

Perhaps I'm missing the problem, but this seems to work. Using the name of a built-in function (OBJECT_ID) as a column name might not be helping.

SQL fiddle example or code below.

-- TVF without parameter.
create function dbo.GetZone()
  returns table as
  return
    select Id, Letter
      from
      ( values ( 1, 'Aleph' ), ( 2, 'Beth' ), ( 3, 'Gimmel' ) ) as Letters( Id, Letter );
go

-- TVF with parameter;
create function dbo.GetZone2( @Id as Int )
  returns table as
  return
    select Id, Letter
      from dbo.GetZone() where Id = @Id;
go

select * from dbo.GetZone();
select * from dbo.GetZone2( 2 );

-- Sample table and data.
declare @Objects as table ( Id Int Identity, Letter VarChar(16) );
insert into @Objects values ( 'Alpha' ), ( 'Beta' ), ( 'Gamma' );
select * from @Objects;

-- Correlated subquery.
select O.Id, O.Letter as [Greek],
  ( select top 1 Letter from dbo.GetZone( ) where Id = O.Id ) as [Hebrew]
  from @Objects as O;
select O.Id, O.Letter as [Greek],
  ( select top 1 Letter from dbo.GetZone2( O.Id ) ) as [Hebrew]
  from @Objects as O;

-- Houseclean.
drop function dbo.GetZone;
drop function dbo.GetZone2;

Collectives™ on Stack Overflow

Transact SQL Subquery calling a function incorrect syntax

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related