SQL Statement Help - Syntax Error

[Hebbers]

Hello All

I know this is huge, but I'm wondering if anyone can immediately see where the syntax error might be? Any help is greatly appreciated.

The error says:
Syntax error (missing operator) in query expression 'dbo_AVATAR_patient_current_demographics.patid = dbo_AVATAR_admission_practitioner.patid LEFT OUTER JOIN dbo_AVATAR_history_attending_Practitioner ON dbo_AVATAR_history_attending_practitioner.patid = dbo_AVATAR_admission_practitioner.pati'.

SELECT dbo_AVATAR_patient_current_demographics.patid,
REPLACE(dbo_AVATAR_patient_current_demographics.patient_name, ',' , '_') AS patient_name,
dbo_AVATAR_patient_current_demographics.date_of_birth, dbo_AVATAR_patient_current_demographics.patient_ssn,
ISNULL( dbo_AVATAR_patient_current_demographics.policy_number_1,dbo_AVATAR_billing_guar_subs_data.subs_policy) AS p1_ID,
ISNULL(REPLACE(dbo_AVATAR_admission_practitoner.admitting_practitioner_value, ',' , '_'), 'None Listed') AS admitting_practitioner_value , dbo_AVATAR_admission_practitoner.EPISODE_NUMBER , dbo_AVATAR_admission_practitoner.preadmit_admission_date,
ISNULL(REPLACE( dbo_AVATAR_history_attending_practitioner.attending_clinician_value, ',' , '_'), 'None Listed') AS attending_clinician_value , dbo_AVATAR_history_attending_practitioner.pract_assignment_date, dbo_AVATAR_history_attending_practitioner.option_desc,
-- d.assessment_type_value,
dbo_AVATAR_Audit_client_diagnosis_codes.diagnosis_code,
dbo_AVATAR_Audit_client_diagnosis_codes.data_entry_date ,
dbo_AVATAR_ss_history_diagnosis.axis_I_diag_code_1,
dbo_AVATAR_ss_history_diagnosis.axis_I_diag_value_1,
dbo_AVATAR_Audit_client_diagnosis_entry.icd_code,
dbo_AVATAR_client_diagnosis_entry_1.ranking_value,
dbo_AVATAR_client_diagnosis_codes.diagnosis_code AS Dx_code,
dbo_AVATAR_client_diagnosis_codes.diagnosis_value,
dbo_AVATAR_client_diagnosis_entry_1.data_entry_date as date_of_diagnosis,
dbo_AVATAR_client_diagnosis_entry_1.remarks

FROM dbo_AVATAR_Audit_client_diagnosis_codes, AVATAR_patient_current_demographics
LEFT OUTER JOIN dbo_AVATAR_admission_practitioner
ON dbo_AVATAR_patient_current_demographics.patid = dbo_AVATAR_admission_practitoner.patid
LEFT OUTER JOIN dbo_AVATAR_history_attending_practitioner
ON dbo_AVATAR_history_attending_practitioner.patid = dbo_AVATAR_admission_practitoner.patid
AND dbo_AVATAR_history_attending_practitioner.EPISODE_NUMBER = dbo_AVATAR_admission_practitoner.EPISODE_NUMBER
LEFT OUTER JOIN dbo_AVATAR_billing_guar_subs_data
ON dbo_AVATAR_patient_current_demographics.patid = dbo_AVATAR_billing_guar_subs_data.patid
LEFT OUTER JOIN dbo_AVATAR_Audit_client_diagnosis_codes
ON dbo_AVATAR_Audit_client_diagnosis_codes.patid = dbo_AVATAR_patient_current_demographics.patid
AND dbo_AVATAR_Audit_client_diagnosis_codes.EPISODE_NUMBER = dbo_AVATAR_admission_practitoner.EPISODE_NUMBER
AND codeset_code LIKE 'ICD10'
LEFT OUTER JOIN dbo_AVATAR_ss_history_diagnosis
ON dbo_AVATAR_ss_history_diagnosis.patid = dbo_AVATAR_patient_current_demographics.patid
AND dbo_AVATAR_ss_history_diagnosis.diagnosis_type_value = 'Admission'
and dbo_AVATAR_ss_history_diagnosis.date_of_diagnosis =
(
select max(x.date_of_diagnosis)
from dbo_AVATAR_ss_history_diagnosis
where dbo_AVATAR_ss_history_diagnosispatid = dbo_AVATAR_ss_history_diagnosis.patid
and dbo_AVATAR_ss_history_diagnosisdiagnosis_type_value = 'Admission'
)
LEFT OUTER JOIN dbo_AVATAR_Audit_client_diagnosis_entry
ON dbo_AVATAR_Audit_client_diagnosis_entry.patid = dbo_AVATAR_patient_current_demographics.patid
AND dbo_AVATAR_Audit_client_diagnosis_entry.diagnosis_status_code = 1 -- active
and dbo_AVATAR_Audit_client_diagnosis_entry.data_entry_date =
(
select max(x.data_entry_date)
from dbo_AVATAR_Audit_client_diagnosis_entry
where dbo_AVATAR_ss_history_diagnosispatid = dbo_AVATAR_Audit_client_diagnosis_entry.patid
and dbo_AVATAR_ss_history_diagnosisdiagnosis_status_code = 1 -- active
)
LEFT OUTER JOIN dbo_AVATAR_client_diagnosis_codes
ON dbo_AVATAR_client_diagnosis_codes.patid = dbo_AVATAR_patient_current_demographics.patid
AND dbo_AVATAR_client_diagnosis_codes.code_set_code like 'ICD10'

LEFT OUTER JOIN dbo_AVATAR_client_diagnosis_entry
ON dbo_AVATAR_client_diagnosis_entry_1.patid = dbo_AVATAR_patient_current_demographics.patid
AND dbo_AVATAR_client_diagnosis_entry_1.data_entry_date =
(
select max(x.data_entry_date)
from dbo_AVATAR_client_diagnosis_entry
where dbo_AVATAR_ss_history_diagnosispatid = dbo_AVATAR_client_diagnosis_entry_1.patid
)

 

[Ranman256]

It may be too complex.
Try making smaller queries. Not SQL.
Q1 to get som data,
Then Q2 uses Q1 to get more data.

 

[isladogs]

Agree with ranman about the need to break this down and check each step
However, I would stick with SQL for anything this complex

When posting lengthy code like this please use the code tags (# button in toolbar above your post) to
a) improve readability
b) avoid an issue where forum software adds a space every 50 characters

I've done this below and removed a lot of added spaces.
Please can you check it for accuracy.

SELECT dbo_AVATAR_patient_current_demographics.patid, 
REPLACE(dbo_AVATAR_patient_current_demographics.pa tient_name, ',' , '_') AS patient_name, 
dbo_AVATAR_patient_current_demographics.date_of_birth, dbo_AVATAR_patient_current_demographics.patient_ssn,
ISNULL( dbo_AVATAR_patient_current_demographics.policy_number_1,
dbo_AVATAR_billing_guar_subs_data.subs_policy) AS p1_ID, 
ISNULL(REPLACE(dbo_AVATAR_admission_practitoner.admitting_practitioner_value, ',' , '_'), 'None Listed') AS admitting_practitioner_value , dbo_AVATAR_admission_practitoner.EPISODE_NUMBER	, dbo_AVATAR_admission_practitoner.preadmit_admission_date, 
ISNULL(REPLACE( dbo_AVATAR_history_attending_practitioner.attending_clinician_value, ',' , '_'), 'None Listed') AS attending_clinician_value , dbo_AVATAR_history_attending_practitioner.pract_as signment_date, dbo_AVATAR_history_attending_practitioner.option_desc, 
-- d.assessment_type_value,
dbo_AVATAR_Audit_client_diagnosis_codes.diagnosis_code,
dbo_AVATAR_Audit_client_diagnosis_codes.data_entry_date ,
dbo_AVATAR_ss_history_diagnosis.axis_I_diag_code_1 , 
dbo_AVATAR_ss_history_diagnosis.axis_I_diag_value_1,
dbo_AVATAR_Audit_client_diagnosis_entry.icd_code, 
dbo_AVATAR_client_diagnosis_entry_1.ranking_value, 
dbo_AVATAR_client_diagnosis_codes.diagnosis_code AS Dx_code,
dbo_AVATAR_client_diagnosis_codes.diagnosis_value, 
dbo_AVATAR_client_diagnosis_entry_1.data_entry_date as date_of_diagnosis,
dbo_AVATAR_client_diagnosis_entry_1.remarks
FROM dbo_AVATAR_Audit_client_diagnosis_codes, AVATAR_patient_current_demographics 
LEFT OUTER JOIN dbo_AVATAR_admission_practitioner 
ON dbo_AVATAR_patient_current_demographics.patid = dbo_AVATAR_admission_practitoner.patid 
LEFT OUTER JOIN dbo_AVATAR_history_attending_practitioner 
ON dbo_AVATAR_history_attending_practitioner.patid = dbo_AVATAR_admission_practitoner.patid 
AND dbo_AVATAR_history_attending_practitioner.EPISODE_ NUMBER = dbo_AVATAR_admission_practitoner.EPISODE_NUMBER
[COLOR="Red"]LEFT OUTER JOIN dbo_AVATAR_billing_guar_subs_data 
ON dbo_AVATAR_patient_current_demographics.patid = dbo_AVATAR_billing_guar_subs_data.patid 
LEFT OUTER JOIN dbo_AVATAR_Audit_client_diagnosis_codes 
ON dbo_AVATAR_Audit_client_diagnosis_codes.patid = dbo_AVATAR_patient_current_demographics.patid [/COLOR]
AND dbo_AVATAR_Audit_client_diagnosis_codes.EPISODE_NUMBER = dbo_AVATAR_admission_practitoner.EPISODE_NUMBER
AND codeset_code LIKE 'ICD10'
LEFT OUTER JOIN dbo_AVATAR_ss_history_diagnosis 
ON dbo_AVATAR_ss_history_diagnosis.patid = dbo_AVATAR_patient_current_demographics.patid 
AND dbo_AVATAR_ss_history_diagnosis.diagnosis_type_value = 'Admission'
and dbo_AVATAR_ss_history_diagnosis.date_of_diagnosis = 
(
select max(x.date_of_diagnosis)
from dbo_AVATAR_ss_history_diagnosis 
where dbo_AVATAR_ss_history_diagnosispatid = dbo_AVATAR_ss_history_diagnosis.patid
and [COLOR="blue"]dbo_AVATAR_ss_history_diagnosisdiagnosis_type_value[/COLOR] = 'Admission'
)
LEFT OUTER JOIN dbo_AVATAR_Audit_client_diagnosis_entry 
ON dbo_AVATAR_Audit_client_diagnosis_entry.patid = dbo_AVATAR_patient_current_demographics.patid
AND dbo_AVATAR_Audit_client_diagnosis_entry.diagnosis_ status_code = [COLOR="Green"]1 -- active[/COLOR]
and dbo_AVATAR_Audit_client_diagnosis_entry.data_entry_date = 
(
select max(x.data_entry_date)
from dbo_AVATAR_Audit_client_diagnosis_entry 
where dbo_AVATAR_ss_history_diagnosispatid = dbo_AVATAR_Audit_client_diagnosis_entry.patid
and [COLOR="Blue"]dbo_AVATAR_ss_history_diagnosisdiagnosis_status_code[/COLOR] = [COLOR="green"]1 -- active[/COLOR]
)
LEFT OUTER JOIN dbo_AVATAR_client_diagnosis_codes 
ON dbo_AVATAR_client_diagnosis_codes.patid = dbo_AVATAR_patient_current_demographics.patid 
AND dbo_AVATAR_client_diagnosis_codes.code_set_code like 'ICD10'
LEFT OUTER JOIN dbo_AVATAR_client_diagnosis_entry 
ON dbo_AVATAR_client_diagnosis_entry_1.patid = dbo_AVATAR_patient_current_demographics.patid
AND dbo_AVATAR_client_diagnosis_entry_1.data_entry_date = 
(
select max(x.data_entry_date)
from dbo_AVATAR_client_diagnosis_entry 
where dbo_AVATAR_ss_history_diagnosispatid = dbo_AVATAR_client_diagnosis_entry_1.patid
)

Some things I've noticed
1. You should be able to omit dbo_ in Access
2. Is there a space between the two LEFT JOIN sections highlighted in RED
3. The sections in BLUE also look wrong - missing . between the 2 lots of diagnosis ?
4. '1 -- active' in GREEN is incorrect in an Access SQL statement. Perhaps you mean

=1 'active

where active is an explanatory comment on the value 1

There may be more issues

 

[Galaxiom]

Some things I've noticed
1. You should be able to omit dbo_ in Access

dbo_ is part of the name the ODBC connector brings in from the server. The underscore replaces the dot that normally separates the schema from the tablename on the server.

2. Is there a space between the two LEFT JOIN sections highlighted in RED

Be aware that spaces are inserted by the vBulletin to prevent long unbroken strings of text in the body of the post from pushing the right margin off the screen. It is another reason why using code tags is important. Spaces are not inserted inside a code box.

The critical problem is the joins. Access must have all its query joins nested. Build a query with multiple joins in the designer and have a look at the SQL to see what I mean. It is a lot easier to write complex queries in TSQL on the server.

 

[The_Doc_Man]

Although it is a pain in the toches to set up, you might do better to do as Ranman suggested, though I will be more specific.

With that many JOINS, you might well do better to layer your queries. Galaxiom sort of mentions this when he talks about nesting the JOINs. Specifically, break that big query into smaller parts that are self-sufficient. Then bring together those parts by making a query of a query. When using the query design window, you can drag a table OR a query into the open area above the grid. You can then draw relationship diagrams that only apply to that query.

A query only needs an input recordset, which you can get from another query just fine. You can also define "spot" relationships that would help Access implement the JOINs for you.

If you can make the JOINS so that the most restrictive JOIN (i.e. the one with the fewest returned records) is innermost, the speed of the resultant operation won't be that bad, because nested queries have to evaluate from the inside out.

Better still, breaking up the query into parts lets you debug smaller parts, which means less head-scratching as you try to determine what just went wrong.

 

[isladogs]

dbo_ is part of the name the ODBC connector brings in from the server. The underscore replaces the dot that normally separates the schema from the tablename on the server.

True. As I use DSN-less connection strings, I never have to deal with the dbo_ part. Aliases could be used here to shorten the SQL

 

[Galaxiom]

True. As I use DSN-less connection strings, I never have to deal with the dbo_ part.

Sometimes I drop the dbo_ by simply renaming the linked table in the Access interface. Especially if I originally developed the database using an Access table.

Access really doesn't care what you call a table locally.

Aliases could be used here to shorten the SQL

Table aliases are a great idea in any SQL query. Aliases make it trivial to edit a query after changing a tablename.

 

[Galaxiom]

I started out writing SQL subqueries in Access by creating the chain of queries and troubleshooting them, then pasting the text of subqueries into the higher level queries.

I have read that Access processes chained queries at least as well as the combined mega-query because it navigates the whole job while constructing the query plan. I can't say with any authority, as I have never tested it.

Writing SQL in Access isn't a very attractive proposition after working in MSSQL Studio with Intellisense, not having to nest joins and the awesome power of TSQL structures. Then there that horrid behaviour in Access where it easily loses the formatting of the SQL.

I tend towards everything being done in Views and Stored Procedures which I feed out to Excel and Access where their already highly processed output can be easily digested for presentation.

 

[Minty]

I agree with Galaxiom, I find writing complex queries in SSMS as views and stored procedures a great deal easier in TSQL.

The fact that you can join queries and subqueries in different more powerful ways is also really handy. The fact that the sub query appears as a derived table makes it really easy to see what is happening.

 

[Galaxiom]

The fact that the sub query appears as a derived table makes it really easy to see what is happening.

I have completely stopped using the query designer in MSSQLSMS. Now I write the query using the "Select the top 1000 records" on the context menu of a table to get started. The Intellisense is that good.

Lists of columns from a table can be dragged into the SQL editor directly from the Columns branch of the tree. The editor supports vertical selection and multiline typing using <ALT>. MSSQLSMS is one of Microsoft's finest user interfaces.

BTW I keep finding more capabilities every time I go exploring. Yesterday I linked to our Domain's Active Directory database so I can now very easily audit our domain logins against our payroll system. Linked Servers is a very powerful capability. We have even used it with ODBC to link a Universe database running on IBM AIX.

 

[Minty]

Actually - I agree about the Intellisense, it's only because I switch from a RDP 2008 SQL server (which isn't as good), and my local 2016 version that I still use the View Designer at all to create a query...

I've just used Linked servers for the first time to combine query results from one system based in the UK to the remote system I manage in the US, via a created on demand VPN tunnel, all run from a SQL server agent job and a couple of stored procedures. Really handy.

 

[Galaxiom]

I've just used Linked servers for the first time to combine query results from one system based in the UK to the remote system I manage in the US,

My first linked server was from 2012 to another SQL database stuck in 2008 due to being so ancient the compatibility modes in 2012 didn't go back far enough.

The most complex part to work out was passing kerberos authentication between the servers while limiting delegation to the SQL process.

 

[Hebbers]

Thank you. I'm going to see if your suggestions help make a difference in the code. It definitely was much easier to read once you put it in the code tags and color coded. I will let you know if some of these suggestions work.

Agree with ranman about the need to break this down and check each step
However, I would stick with SQL for anything this complex

When posting lengthy code like this please use the code tags (# button in toolbar above your post) to
a) improve readability
b) avoid an issue where forum software adds a space every 50 characters

I've done this below and removed a lot of added spaces.
Please can you check it for accuracy.

SELECT dbo_AVATAR_patient_current_demographics.patid, 
REPLACE(dbo_AVATAR_patient_current_demographics.pa tient_name, ',' , '_') AS patient_name, 
dbo_AVATAR_patient_current_demographics.date_of_birth, dbo_AVATAR_patient_current_demographics.patient_ssn,
ISNULL( dbo_AVATAR_patient_current_demographics.policy_number_1,
dbo_AVATAR_billing_guar_subs_data.subs_policy) AS p1_ID, 
ISNULL(REPLACE(dbo_AVATAR_admission_practitoner.admitting_practitioner_value, ',' , '_'), 'None Listed') AS admitting_practitioner_value , dbo_AVATAR_admission_practitoner.EPISODE_NUMBER	, dbo_AVATAR_admission_practitoner.preadmit_admission_date, 
ISNULL(REPLACE( dbo_AVATAR_history_attending_practitioner.attending_clinician_value, ',' , '_'), 'None Listed') AS attending_clinician_value , dbo_AVATAR_history_attending_practitioner.pract_as signment_date, dbo_AVATAR_history_attending_practitioner.option_desc, 
-- d.assessment_type_value,
dbo_AVATAR_Audit_client_diagnosis_codes.diagnosis_code,
dbo_AVATAR_Audit_client_diagnosis_codes.data_entry_date ,
dbo_AVATAR_ss_history_diagnosis.axis_I_diag_code_1 , 
dbo_AVATAR_ss_history_diagnosis.axis_I_diag_value_1,
dbo_AVATAR_Audit_client_diagnosis_entry.icd_code, 
dbo_AVATAR_client_diagnosis_entry_1.ranking_value, 
dbo_AVATAR_client_diagnosis_codes.diagnosis_code AS Dx_code,
dbo_AVATAR_client_diagnosis_codes.diagnosis_value, 
dbo_AVATAR_client_diagnosis_entry_1.data_entry_date as date_of_diagnosis,
dbo_AVATAR_client_diagnosis_entry_1.remarks
FROM dbo_AVATAR_Audit_client_diagnosis_codes, AVATAR_patient_current_demographics 
LEFT OUTER JOIN dbo_AVATAR_admission_practitioner 
ON dbo_AVATAR_patient_current_demographics.patid = dbo_AVATAR_admission_practitoner.patid 
LEFT OUTER JOIN dbo_AVATAR_history_attending_practitioner 
ON dbo_AVATAR_history_attending_practitioner.patid = dbo_AVATAR_admission_practitoner.patid 
AND dbo_AVATAR_history_attending_practitioner.EPISODE_ NUMBER = dbo_AVATAR_admission_practitoner.EPISODE_NUMBER
[COLOR="Red"]LEFT OUTER JOIN dbo_AVATAR_billing_guar_subs_data 
ON dbo_AVATAR_patient_current_demographics.patid = dbo_AVATAR_billing_guar_subs_data.patid 
LEFT OUTER JOIN dbo_AVATAR_Audit_client_diagnosis_codes 
ON dbo_AVATAR_Audit_client_diagnosis_codes.patid = dbo_AVATAR_patient_current_demographics.patid [/COLOR]
AND dbo_AVATAR_Audit_client_diagnosis_codes.EPISODE_NUMBER = dbo_AVATAR_admission_practitoner.EPISODE_NUMBER
AND codeset_code LIKE 'ICD10'
LEFT OUTER JOIN dbo_AVATAR_ss_history_diagnosis 
ON dbo_AVATAR_ss_history_diagnosis.patid = dbo_AVATAR_patient_current_demographics.patid 
AND dbo_AVATAR_ss_history_diagnosis.diagnosis_type_value = 'Admission'
and dbo_AVATAR_ss_history_diagnosis.date_of_diagnosis = 
(
select max(x.date_of_diagnosis)
from dbo_AVATAR_ss_history_diagnosis 
where dbo_AVATAR_ss_history_diagnosispatid = dbo_AVATAR_ss_history_diagnosis.patid
and [COLOR="blue"]dbo_AVATAR_ss_history_diagnosisdiagnosis_type_value[/COLOR] = 'Admission'
)
LEFT OUTER JOIN dbo_AVATAR_Audit_client_diagnosis_entry 
ON dbo_AVATAR_Audit_client_diagnosis_entry.patid = dbo_AVATAR_patient_current_demographics.patid
AND dbo_AVATAR_Audit_client_diagnosis_entry.diagnosis_ status_code = [COLOR="Green"]1 -- active[/COLOR]
and dbo_AVATAR_Audit_client_diagnosis_entry.data_entry_date = 
(
select max(x.data_entry_date)
from dbo_AVATAR_Audit_client_diagnosis_entry 
where dbo_AVATAR_ss_history_diagnosispatid = dbo_AVATAR_Audit_client_diagnosis_entry.patid
and [COLOR="Blue"]dbo_AVATAR_ss_history_diagnosisdiagnosis_status_code[/COLOR] = [COLOR="green"]1 -- active[/COLOR]
)
LEFT OUTER JOIN dbo_AVATAR_client_diagnosis_codes 
ON dbo_AVATAR_client_diagnosis_codes.patid = dbo_AVATAR_patient_current_demographics.patid 
AND dbo_AVATAR_client_diagnosis_codes.code_set_code like 'ICD10'
LEFT OUTER JOIN dbo_AVATAR_client_diagnosis_entry 
ON dbo_AVATAR_client_diagnosis_entry_1.patid = dbo_AVATAR_patient_current_demographics.patid
AND dbo_AVATAR_client_diagnosis_entry_1.data_entry_date = 
(
select max(x.data_entry_date)
from dbo_AVATAR_client_diagnosis_entry 
where dbo_AVATAR_ss_history_diagnosispatid = dbo_AVATAR_client_diagnosis_entry_1.patid
)

Some things I've noticed
1. You should be able to omit dbo_ in Access
2. Is there a space between the two LEFT JOIN sections highlighted in RED
3. The sections in BLUE also look wrong - missing . between the 2 lots of diagnosis ?
4. '1 -- active' in GREEN is incorrect in an Access SQL statement. Perhaps you mean where active is an explanatory comment on the value 1

There may be more issues

 

[kevlray]

I am not sure why, but I have noticed a number of places where there is a space in the middle of a column name (i.e.,
dbo_AVATAR_patient_current_demographics.pa tient_name). That will cause all kind of issues.

 

[isladogs]

@hebbers
You're welcome. Good luck

@kevlray
The spaces are added by the forum software when code tags aren't used.
See my comment in post 3 item b)

 

[kevlray]

Did not see where the forum adds a space every 50 characters. Explains my previous post.


But in the FROM clause. You have the dbo_AVATAR_Audit_client_diagnosis_codes then a comma then AVATAR_patient_current_demographics. Then later on you have dbo_AVATAR_Audit_client_diagnosis_codes again, but joined with dbo_AVATAR_patient_current_demographics. So it appears that the first AVATAR_patient_current_demographics should be removed from the query. But you are joining the dbo_AVATAR_admission_practitioner to the AVATAR_patient_current_demographics, so something is amiss here.

 

[kevlray]

I missed the comment about the spaces added.


But further examination. It appears that the dbo_AVATAR_Audit_client_diagnosis_codes right after the FROM cause should not be there. It gets joined later on in the query.