Pass-Thru Query not working

[KS31]

I'm trying to do a passthru query using two tables to just pull patient
diagnosis code from one table and the diagnosis description from another.
When I run as a regular query I need to bring the diagnosis table in 4 times
in order to get the description for all 4 diagnosis codes. When trying to
run this as a pass-thru doing the same thing, I'm getting an error - Line 2:
Incorrect syntax near '.' (#170). I
think this has something to do with the way I'm referencing the duplicate
diagnosis tables. Can someone help? Here's my sql:

SELECT dbo.Patient.SourceSystemCode, dbo.Patient.PatientCode,
dbo.Patient.OriginalServiceDate, dbo.Patient.Diagnosis1,
dbo.Diagnosis.DiagnosisDescription, dbo.Patient.Diagnosis2,
dbo.Diagnosis_2.DiagnosisDescription, dbo.Patient.Diagnosis3,
dbo.Diagnosis_3.DiagnosisDescription, dbo.Patient.Diagnosis4,
dbo.Diagnosis_1.DiagnosisDescription
FROM (((dbo.Patient INNER JOIN dbo.Diagnosis ON (dbo.Patient.Diagnosis1 =
dbo.Diagnosis.DiagnosisCode) AND (dbo.Patient.SourceSystemCode =
dbo.Diagnosis.Sourcesystemcode)) INNER JOIN dbo.Diagnosis AS dbo.Diagnosis_2
ON (dbo.Patient.Diagnosis2 = dbo.Diagnosis_2.DiagnosisCode) AND
(dbo.Patient.SourceSystemCode = dbo.Diagnosis_2.Sourcesystemcode)) INNER JOIN
dbo.Diagnosis AS dbo.Diagnosis_3 ON (dbo.Patient.SourceSystemCode =
dbo.Diagnosis_3.Sourcesystemcode) AND (dbo.Patient.Diagnosis3 =
dbo.Diagnosis_3.DiagnosisCode)) INNER JOIN dbo.Diagnosis AS dbo.Diagnosis_1
ON (dbo.Patient.SourceSystemCode = dbo.Diagnosis_1.Sourcesystemcode) AND
(dbo.Patient.Diagnosis4 = dbo.Diagnosis_1.DiagnosisCode);

 

[Sylvain Lafontaine]

Maybe because you have repeated the dbo. part for your aliases; so instead
of:

dbo.Diagnosis AS dbo.Diagnosis_2

you should try with:

dbo.Diagnosis AS Diagnosis_2

Also, using something simpler and smaller like D2 instead of Diagnosis_2
would make your code more readable. By experience, using the underline _
character make also the code harder to read: I always find harder to read
code written by someone else when there is a lot of _ than where there none
or few. Also, using the exact same name for the diagnosis columns will also
make it easier to relate them the table Diagnosis (ie., it's easier to read
and remember « dbo.Patient.DiagnosisCode1 = dbo.Diagnosis.DiagnosisCode »
than « dbo.Patient.Diagnosis1 = dbo.Diagnosis.DiagnosisCode » and it's even
easier to read « P.DiagnosisCode1 = D.DiagnosisCode » or « P.DiagnosisCode1
= D1.DiagnosisCode » .

 

[KS31]

I changed the name of my Alias to not have the dbo. and also renamed so there
is no _ and it works now.

Thanks so much for the help!