June 14, 2006 at 10:12 pm
The query runs fine if no joins are used, but when the aliased column is used as a join "ON" column the message "Invalid column name 'xyz'" is reported. The syntax parses OK.
Thanks.
June 14, 2006 at 10:45 pm
The alias doesn't apply at that point in the SQL parsing/execution. Whatever expression you're aliasing in the SELECT needs to be replicated in the FROM/JOIN section.
If it's a large, complex CASE ... WHEN type evaluation, you might consider wrapping it in a derived table so that the expression does not need to be repeated.
June 14, 2006 at 11:02 pm
Thanks for the response, however I don't have a choice about how to form the sql since it is being generated by a middle-ware process. I was wondering if I needed to set some preference in SQL2000 to allow it to work properly (I know I'm clutching at straws!). The SQL is as follows:
SELECT p.pkPersonnelID AS pkPerson1_0_, p.PersonnelFirstName AS Personne2_0_, p.PersonnelLastName AS Personne3_0_,
p.PersonnelOtherNames AS Personne4_0_, p.PersonnelPreferredName AS Personne5_0_, p.PersonnelUserName AS Personne6_0_,
p.PersonnelIsDisplayed AS Personne7_0_, p.fkEmploymentTypeID AS fkEmploy8_0_
FROM dbo.PersonnelTeams LEFT OUTER JOIN
dbo.PersonnelPositions LEFT OUTER JOIN
dbo.Personnels p ON dbo.PersonnelPositions.fkPersonnelID = pkPerson1_0_ ON dbo.PersonnelTeams.fkPersonnelID = pkPerson1_0_
WHERE (dbo.PersonnelTeams.fkTeamID = 2) AND (dbo.PersonnelPositions.fkPositionTypeID = 2)
June 14, 2006 at 11:52 pm
The middle-ware process is generating some strange SQL. The first two tables (PersonnelTeams and PersonnelPositions) are being joined in the spot where the second and third tables are supposed to be joined, but Personnels is not being joined to either of them. If you tell us what table Personnels is joined to and on what column(s), we should be able to give you code that will execute.
Edit: I see that Personnels is being joined, but on that alias of its column name, which won't work. What middleware is this, and are there settings that control how the SQL looks? It's definitely generating some invalid SQL in that FROM portion.
June 15, 2006 at 1:11 am
try replacing this code in your from clause to where claues
FROM dbo.PersonnelTeams
LEFT OUTER JOIN dbo.PersonnelPositions
ON dbo.PersonnelPositions.fkPersonnelID = pkPerson1_0_
LEFT OUTER JOIN dbo.Personnels p
ON dbo.PersonnelTeams.fkPersonnelID = pkPerson1_0_
------------
Prakash Sawant
http://psawant.blogspot.com
June 15, 2006 at 8:42 am
>>however I don't have a choice about how to form the sql since it is being generated by a middle-ware process.
In that case you have no other choice than to contact the middleware vendor and report a bug. If middleware is generating SQL with invalid syntax, and you have no control over it, not much else you can do, is there ?
June 15, 2006 at 11:58 pm
Thanks for the replies,
I fixed the problem by writing some native SQL and by bypassing the generator altogether.
Cheers.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply