SQL Prompt Not Recognizing Alias
stamey
Posts: 12
I have this query, already written out:
Select
Left(S.FirstName, 20), Left(S.LastName, 20),
Left(Replace(P.SS, '-', ''), 9), Left(S.Address, 35), Left(S.Address2, 35), Left(S.City, 35),
Left(S.State, 35), Left(S.Zip, 10),
Left(Replace(S.Phone, '-', ''), 14), Left(S.Email, 64),
Left(J.Type, 30),
'USA', 'RMSImportProcess',
S.Submit_ID
From RMS.dbo.Submits S
Inner Join RMS.dbo.Placement P On S.Submit_ID = P.Submit_ID
Inner Join RMS.dbo.Jobs J On P.Job_ID = J.Job_ID
Inner Join RMS.dbo.CostCenterNum C On J.CostCenterNumID = C.CostCenterNumID
INNER JOIN #RecruitSSNs RS ON Replace(P.SS, '-', '') = RS.SSN
I go to add, on a new line, after the Select, "P.SS". When I type "P.", I get two different behaviors, both wrong. Immediately after hitting Return and Tab, I type "P." and I get a list of fields from various tables because SQLPrompt has not figured out the existing alias. If I back up, deleting the "P.", trying to reproduce the error I find that when I type "P." again SQLPrompt grabs the first field it can find that starts with a P, from any table, and I end up with "PaAmount.". This is not a field in the table I am working with. Neither of these behaviors is correct.
As should be obvious, and I don't know if it has anything to do with the behavior, the DB the "P" table is in is a different one than the DB I am working in whle writing this query. The table is on the same DB server and uses a fully quailified name though.
I don't really have any ideas for fixing this problem at this time. I can say that I'd rather not have help if it is wrong, but I don't know what would necessarily be better, if the alias evaluation cannot be fixed.
Thanks,
Chris
Select
Left(S.FirstName, 20), Left(S.LastName, 20),
Left(Replace(P.SS, '-', ''), 9), Left(S.Address, 35), Left(S.Address2, 35), Left(S.City, 35),
Left(S.State, 35), Left(S.Zip, 10),
Left(Replace(S.Phone, '-', ''), 14), Left(S.Email, 64),
Left(J.Type, 30),
'USA', 'RMSImportProcess',
S.Submit_ID
From RMS.dbo.Submits S
Inner Join RMS.dbo.Placement P On S.Submit_ID = P.Submit_ID
Inner Join RMS.dbo.Jobs J On P.Job_ID = J.Job_ID
Inner Join RMS.dbo.CostCenterNum C On J.CostCenterNumID = C.CostCenterNumID
INNER JOIN #RecruitSSNs RS ON Replace(P.SS, '-', '') = RS.SSN
I go to add, on a new line, after the Select, "P.SS". When I type "P.", I get two different behaviors, both wrong. Immediately after hitting Return and Tab, I type "P." and I get a list of fields from various tables because SQLPrompt has not figured out the existing alias. If I back up, deleting the "P.", trying to reproduce the error I find that when I type "P." again SQLPrompt grabs the first field it can find that starts with a P, from any table, and I end up with "PaAmount.". This is not a field in the table I am working with. Neither of these behaviors is correct.
As should be obvious, and I don't know if it has anything to do with the behavior, the DB the "P" table is in is a different one than the DB I am working in whle writing this query. The table is on the same DB server and uses a fully quailified name though.
I don't really have any ideas for fixing this problem at this time. I can say that I'd rather not have help if it is wrong, but I don't know what would necessarily be better, if the alias evaluation cannot be fixed.
Thanks,
Chris
Comments
Sorry you're having difficulties. You have however correctly identified the problem which is that SQL Prompt 3 does not support cross-database queries. It was one of those things that was dropped fairly late on in the project due to time constraints, however it's quite likely to reappear again soon, and the more people ask about it the more likely it is to appear. Obviously that doesn't help you much right now so I apologise for the hassle this is causing you.
Thanks,
Bart
Principal Consultant
bartread.com Ltd