SQL Multiple inner joins with max() for latest recorded entry

Question

Attempting to build SQL with INNER JOIN's. The INNER JOIN's work ok, now I need to add the MAX() function for limiting the rows to just most recent. Added this INNER JOIN client_diagnosis_record ON SELECT cr.PATID, cr.date_of_diagnosis, cr.most_recent_diagnosis... Received this SQL code error, need some help, I'm sure it a simple oversight but my eyes are getting dim from looking so long...

Syntax error: [SQLCODE: <-4>:

SQLCODE: <-4>:<A term expected, beginning with one of the following: identifier, constant, aggregate, %ALPHAUP, %EXACT, %MVR, %SQLSTRING, %

[%msg: < The SELECT list of the subquery


SELECT pd.patient_name,
       cr.PATID,
       cr.date_of_diagnosis,
       cr.EPISODE_NUMBER,
       ce.diagnosing_clinician_value, 
       ce.data_entry_user_name,
       most_recent_diagnosis
FROM client_diagnosis_record cr 
INNER JOIN patient_current_demographics pd ON cr.patid = pd.patid 
INNER JOIN client_diagnosis_entry ce ON ce.patid = pd.patid
                                    AND cr.ID = ce.DiagnosisRecord
INNER JOIN client_diagnosis_record ON (SELECT cr.PATID,
                                              cr.date_of_diagnosis,
                                              cr.most_recent_diagnosis
                                       FROM ( SELECT patid,
                                                     date_of_diagnosis,
                                                     MAX(ID) AS most_recent_diagnosis
                                              FROM client_diagnosis_record) cr
 INNER JOIN RADplus_users ru ON ru.staff_member_id = ce.diagnosing_clinician_code
 WHERE cr.PATID <> '1'
 AND ce.diagnosis_status_value ='Active'
 AND (ru.user_description LIKE '%SOA' OR ru.user_description LIKE '%OA')
 GROUP BY cr.PATID

can you help with the correct code format, point the details out so I can fix it — manager_matt, Aug 29 '19 at 18:38
Is the incorrect format pertain to the? INNER JOIN client_diagnosis_record ON ( SELECT cr.PATID, cr.date_of_diagnosis, cr.most_recent_diagnosis FROM ( SELECT patid, date_of_diagnosis, MAX(ID) AS most_recent_diagnosis ) — manager_matt, Aug 29 '19 at 18:41
Dont you see the FROM client_diagnosis_record ) at the end of your code block? — JM123, Aug 29 '19 at 18:41
INNER JOIN client_diagnosis_record ON ( SELECT cr.PATID, cr.date_of_diagnosis, cr.most_recent_diagnosis FROM ( SELECT patid, date_of_diagnosis, MAX(ID) AS most_recent_diagnosis FROM client_diagnosis_record ) cr ) — manager_matt, Aug 29 '19 at 18:45
Please in code questions give a [mre]--cut & paste & runnable code; example input with desired & actual output (including verbatim error messages); tags & versions; clear specification & explanation. That includes the least code you can give that is code that you show is OK extended by code that you show is not OK. (Debugging fundamental.) PS You have a syntax error. Read the grammar & manual. Show that constituent subexpressions are OK. Make it clear that your question is about *that error* & ask re your overall goal later in a new post. — philipxy, Aug 29 '19 at 19:49

Ankit Bajpai · Accepted Answer · 2019-08-29T19:54:18.587

I tried to re-format you query and it seems your query syntax is not correct. You may try below query -

SELECT pd.patient_name,
       cr.PATID,
       cr.date_of_diagnosis,
       cr.EPISODE_NUMBER,
       ce.diagnosing_clinician_value, 
       ce.data_entry_user_name,
       most_recent_diagnosis
FROM client_diagnosis_record cr
INNER JOIN (SELECT patid,
                   date_of_diagnosis,
                   MAX(ID) AS most_recent_diagnosis
            FROM client_diagnosis_record
            GROUP BY patid,
                     date_of_diagnosis) cr2 ON cr.PATID = cr2.PATID
                                            AND cr.date_of_diagnosis = cr2.date_of_diagnosis
                                            AND cr.ID = cr2.most_recent_diagnosis
INNER JOIN patient_current_demographics pd ON cr.patid = pd.patid 
INNER JOIN client_diagnosis_entry ce ON ce.patid = pd.patid
                                    AND cr.ID = ce.DiagnosisRecord
INNER JOIN RADplus_users ru ON ru.staff_member_id = ce.diagnosing_clinician_code
WHERE cr.PATID <> '1'
AND ce.diagnosis_status_value ='Active'
AND (ru.user_description LIKE '%SOA' OR ru.user_description LIKE '%OA')
GROUP BY cr.PATID

thanks, that really helped, I was out sick for the past 4 days — manager_matt, Sep 11 '19 at 03:55

SQL Multiple inner joins with max() for latest recorded entry

1 Answers1