CODEWITHSUNDEEP
sqlsql-server
sesodesa
sesodesa

Reputation: 1603

Syntax error near JOIN in SQL Server database

The following SQL query is giving below error when run against a Microsoft SQL Server database from C#:

Incorrect syntax near the keyword JOIN

Certainly, the query contains the columns mentioned in the SELECT portion.

SELECT
  Patient.FirstName,
  Patient.LastName,
  Patient.PatientId,
  Patient.PatientSer,
  CAST(Course.StartDateTime AS DateTime),
  Course.ClinicalStatus,
  CAST(Course.CompletedDateTime AS DateTime),
  CAST(ScheduledActivity.CreationDate AS DateTime),
  CAST(ScheduledActivity.ActualStartDate AS DateTime),
  CAST(ScheduledActivity.ActualEndDate AS DateTime),
  ScheduledActivity.ActivityNote,
  Activity.ActivityCode,
  Activity.ActivityType,
  Activity.ObjectStatus,
  CAST(Activity.HstryDateTime AS DateTime),
  Activity.HstryTaskName
FROM
  Patient
  INNER JOIN Course
    ON (Patient.PatientSer=Course.PatientSer)
  INNER JOIN ScheduledActivity
    ON (Patient.PatientSer=ScheduledActivity.PatientSer)
  INNER JOIN ActivityInstance
    ON (ScheduledActivity.ActivityInstanceSer=ActivityInstance.ActivityInstanceSer)
  INNER JOIN Activity
    ON (Activity.ActivitySer=ActivityInstance.ActivitySer)
WHERE
  Patient.PatientId=@patientId;

A very similar query is successfully parsed without any issues. I just can't for the life of me see, why this isn't working. For now, I would like someone to just make sure that there actually is a syntax error in this query as the system claims, because I sure can't spot it.

There might also be an issue with the C# script that a "colleague" is using to run the query against the database, but that is a whole different issue.

Upvotes: 0

Views: 691

Answers (2)

DRapp
DRapp

Reputation: 48139

I agree with tgolisch, getting table ALIAS name references both shortens the query from readability standpoint, but also in cases where you need the same table joined multiple times in the same query. Such as an employee ID and supervisor ID both point to a person table to get the name. Each ID represents a person. Having aliases can help.

Now, back to your original post. Does it actually work AS-WRITTEN under SSMS? when the patient ID is provided? If not, fix it there first. If the query DOES work, then its somewhere in how the query is prepared within your C# code. If so, post the entire code of C# (without actual login credential stuff), but the whole code to be assessed.

In its easiest test case, I would go in C# and try doing the query one step at a time such as getting EVERY column so you are not worrying about "CAST()" issues that may fail. ALSO, put a specific patient ID vs parameter just to test and make sure all the JOINs work. If you are failing on the join to begin with, it doesn't matter WHAT the other fields are. If the parameter is bad, that too has been removed from the equation of problem. Only other alias I would add is for the "ScheduledActivity" table reference to ex: "SA" or "SchA", and "ActivityInstance" to "AI" or "ActI"

        yourSqlCommand.CommandText =
@"SELECT 
          *
       FROM
          Patient P
             INNER JOIN Course C
                ON P.PatientSer = C.PatientSer
             INNER JOIN ScheduledActivity SA
                ON P.PatientSer = SA.PatientSer
                INNER JOIN ActivityInstance AI
                   ON SA.ActivityInstanceSer = AI.ActivityInstanceSer
                   INNER JOIN Activity A
                      ON AI.ActivitySer = A.ActivitySer
        WHERE
          P.PatientId=@parmForPatientID
";

Mino

Update to show how I personally write my SQL queries in C# using the leading "@" which allows one string to span multiple lines. I get the entire clarity of the SQL in 1 statement without guessing at where the concatenation on multiple lines comes in. I even changed the parameter to show the SQL "@"-based parameter name. I also try to prefix with "@parm" to prevent any ambiguity of coming from some table, or parameter... no guessing.

Reformatted query to show indentation of where tables rely on previous... IMO, easier to follow how A->B->C are related, also updated the aliases as noted in top post.

Upvotes: 1

tgolisch
tgolisch

Reputation: 6744

SQL Server has several reserved words. It is really difficult to memorize them all, so it is a good idea to use delimiters around any table name which might (maybe) have the same name as a reserved word. Example: [delimited]

Try this:

SELECT
  P.FirstName,
  P.LastName,
  P.PatientId,
  P.PatientSer,
  CAST(C.StartDateTime AS DateTime) StartDateTime,
  C.ClinicalStatus,
  CAST(C.CompletedDateTime AS DateTime) CompletedDateTime,
  CAST(ScheduledActivity.CreationDate AS DateTime) CreationDate,
  CAST(ScheduledActivity.ActualStartDate AS DateTime) ActualStartDate,
  CAST(ScheduledActivity.ActualEndDate AS DateTime) ActualEndDate,
  ScheduledActivity.ActivityNote,
  A.ActivityCode,
  A.ActivityType,
  A.ObjectStatus,
  CAST(A.HstryDateTime AS DateTime) HstryDateTime,
  A.HstryTaskName
FROM
  [Patient] P
  INNER JOIN [Course] C
    ON (P.PatientSer=C.PatientSer)
  INNER JOIN ScheduledActivity
    ON (P.PatientSer=ScheduledActivity.PatientSer)
  INNER JOIN ActivityInstance
    ON (ScheduledActivity.ActivityInstanceSer=ActivityInstance.ActivityInstanceSer)
  INNER JOIN [Activity] A
    ON (A.ActivitySer=ActivityInstance.ActivitySer)
WHERE
  P.PatientId=@patientId;

Upvotes: 0

Related Questions