using multi-statement table-valued

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

using multi-statement table-valued

Author

Topic

mayerl
Yak Posting Veteran

95 Posts

Posted - 2010-10-19 : 13:49:39

Afternoon,

I have a multi-statement table-valued function I created. Now I want to use is with a field name like this:


....
 join dbo.fn_FindPromisedDate(Job.Part_Number)
	on job.Part_Number = dbo.fn_FindPromisedDate(Job.Part_Number)

I keep getting the error:


Msg 102, Level 15, State 1, Line 74
Incorrect syntax near '.'.

Am I correct in guessing I can't use this kind of function in a query?

Thanks

Laura

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-10-19 : 13:55:59

you cant pass values into function from left part of query in a join. for that you need to use apply instead.

see scenario 4 below

http://visakhm.blogspot.com/2010/01/multipurpose-apply-operator.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

mayerl
Yak Posting Veteran

95 Posts

Posted - 2010-10-20 : 08:35:29

I read you post and looked in the database journal about how to use it. However I am still getting the same error:

Incorrect syntax near '.'

This is what I have:


select s.*,obyS.checkdate from 
(select * from vw_WCDispatch_Pass1)  s
	cross apply 
(select * from fn_FindPromisedDate(s.material)) obyS
where s.part_number = '10580002 REV A'

Can someone tell me what is incorrect?

Thanks

Laura

mayerl
Yak Posting Veteran

95 Posts

Posted - 2010-10-20 : 08:39:42

Nevermind I think I got it. The database has to be set to 90 mine is only at 80.

Thanks

Laura

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-10-20 : 12:37:47

yep. APPLY works only on compatibility level 90 and above

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources