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
Hi 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