September 8, 2010 at 11:24 am
Hi,
I am having a really strange problem when using CROSS JOIN and a table-valued FUNCTION.
If I pass parameters to the function as static values, it works just fine. If I pass a field name from the query, I get;
Msg 102, Level 15, State 1, Line 148
Incorrect syntax near '.'. ('.' being the tablealias.fieldname)
weird.
can someone point out what I am doing wrong ??
here is the query;
this works;
==============
select
e.eecEEID,
e.NameLast,
e.NameFirstMiddle,
jobs.ejhJobCode,
jobs.ejfJobEffDate
from v_EmpPersComp e
cross apply
dbo.fn_BI_gnsa_EmpHJob('7G2SBX0000K0', 2, 'EN') as jobs
where e.eecCoID='FGVMB' and jobs.ejhReason <> 'Z'
order by e.NameLast, e.NameFirstMiddle, e.eecEEID
this does not work;
==============
cross apply
dbo.fn_BI_gnsa_EmpHJob(e.eedEEID, 2, 'EN') as jobs
I am not sure what it does not like about the alias ?
changing it from a cross join to a select/left outer join appears to have same result (the error).
any help pointing me in the direction of my ignorance greatly appreciated ~!
September 8, 2010 at 1:25 pm
it's hard to answer from just looking at the text, but it looks to me like your field in your query should be e.eecEEID, not e.eedEEID.
I'd have to see more though to help you.
September 8, 2010 at 1:48 pm
Is this a level 90 database ?
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
September 8, 2010 at 2:40 pm
Level 90;
nope.
I'm guessing based on some searching I did and your question, that that be why !
Darn it. I am unable to change the compatibility level on this db....
September 8, 2010 at 2:58 pm
bbaley (9/8/2010)
Darn it. I am unable to change the compatibility level on this db....
Why not?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 8, 2010 at 3:03 pm
I should have elaborated...
the database is provided by and supported by a vendor. for various reasons, they have not yet fixed everything in it that requires it to [still] be in level 80.
a constant bummer...
September 8, 2010 at 3:15 pm
If this is an ad-hoc query, here's a trick...
use master
go
select
e.eecEEID,
e.NameLast,
e.NameFirstMiddle,
jobs.ejhJobCode,
jobs.ejfJobEffDate
from <database name>.dbo.v_EmpPersComp e
cross apply <database name>.dbo.fn_BI_gnsa_EmpHJob(e.eedEEID, 2, 'EN') as jobs
where e.eecCoID='FGVMB' and jobs.ejhReason <> 'Z'
order by e.NameLast, e.NameFirstMiddle, e.eecEEID
Be sure to replace the <database name> with the real name of the DB.
If this is supposed to be a stored proc, this trick isn't really practical.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 9, 2010 at 2:16 pm
I don't think SQL 80 will accept any type of "CROSS APPLY", so that will be limited.
I guess if you really wanted you could create the proc in master and reference the other db and have it work??
Scott Pletcher, SQL Server MVP 2008-2010
September 9, 2010 at 2:33 pm
- Can you try out the trick Gail proposed ?
The trick is to execute the query whilest being connected to master db.
(off course you shouldn't use that in a real prod app, but upgrade your dblevel)
- if that query returns a result, it proves your db needs to be at level 90.
Keep in mind, if you upgraded the instance from sql2000 to SQL2005 - in place upgrade- you may still need to alter the dblevel of you systems databases to 90 as well !!
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
September 9, 2010 at 4:06 pm
I did try this, and it had no effect / same error
September 9, 2010 at 4:07 pm
scott.pletcher (9/9/2010)
As I mentioned, it oddly enough allows the CROSS JOIN just fine as long as the parameter passed to the function is not an alias (a static string, etc)
September 9, 2010 at 11:15 pm
bbaley (9/9/2010)
I did try this, and it had no effect / same error
SELECT @@VERSION
bbaley (9/9/2010)
As I mentioned, it oddly enough allows the CROSS JOIN just fine as long as the parameter passed to the function is not an alias (a static string, etc)
Yes it will. That's been valid SQL for years, ever since UDFs were first introduced. What CROSS APPLY allows (and what was not permitted before SQL 2005) is the passing of a column from one table as a parameter to a UDF. That requires SQL 2005+ and the DB in compat mode 90 or higher.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply