1

I have a Inline table valued Function that i want to pass a column from a select but will use a table alias.

For Example:

select top 1000 a.*, b.* from item a
LEFT JOIN itemList b on a.item_id = b.item_id
where a.item_id in (SELECT * FROM dbo.fn_GIVFUC('1234567', a.item_id))

Results in : Incorrect syntax near 'a'.

Thanks

J Cooper
  • 4,828
  • 3
  • 36
  • 39
Jdunn5
  • 108
  • 2
  • 10

1 Answers1

5

You'd have to use CROSS APPLY like this

select top 1000  
   a.*, b.* 
from 
  item a
  CROSS APPLY
  dbo.fn_GIVFUC('1234567', a.item_id) c ON a.item_id = c.item_id
  LEFT JOIN 
  itemList b on a.item_id = b.item_id

This means you may get duplicates though, so this may work. I can't test

select top 1000  
   a.*, b.* 
from 
  item a
  LEFT JOIN 
  itemList b on a.item_id = b.item_id
WHERE
  EXISTS (
       SELECT *
       FROM dbo.fn_GIVFUC('1234567', a.item_id)
       -- may need this WHERE a.item_id = c.item_id
      )
gbn
  • 422,506
  • 82
  • 585
  • 676
  • Thanks for the quick reply but i still get the same error : "Incorrect syntax near 'a'." – Jdunn5 Feb 20 '12 at 16:38
  • It really seems that sql does not like me passing the a.column name to the function. It is a INLINE Table Function that returns a table and I am expecting multiple results, that is why my initial sql used in – Jdunn5 Feb 20 '12 at 16:43
  • 1
    In which case, check the DB compatibility mode. It need to be 90 (or higher for SQL Server 2008+) to allow this. It won't work in "80" – gbn Feb 20 '12 at 16:54
  • This is currently being run on sql 2005 server, so would that setting be the same? – Jdunn5 Feb 20 '12 at 17:05
  • 1
    Not necessarily, no. Did you check it? See http://stackoverflow.com/questions/9318684/incorrect-syntax-near-the-keyword-current-timestamp-but-only-on-one-database/9318720#9318720 – Aaron Bertrand Feb 20 '12 at 17:24
  • Thank you, you were correct with the compatibility. I forgot the db im working on use to be a 2000 db and compatibility was never modified. Ill have to do some testing and make sure other functionality does not break. But setting compatibility to 90 fixed the glitch. =) – Jdunn5 Feb 20 '12 at 17:41