CODEWITHSUNDEEP
sqlsql-server
C Bos
C Bos

Reputation: 21

Msg 156, Level 15, State 1, Line 2493 Incorrect syntax near the keyword 'ON'

I know that this question may be asked quite often but I have been trying to find the issue with this left join. I have searched around and tried a few different things but I am still having trouble identifying what is wrong with it.

left join (
    SELECT
        MAX(e.date) as MaxDate
        ,e.patientID
    FROM
        labdata ld
        inner join enc e on e.encounterID = ld.EncounterId
            and e.deleteFlag = 0
            and ld.deleteFlag = 0
            and ld.futureflag = 0
            and ld.cancelled = 0
            and ld.received = 1
        inner join @patients t on t.patientID = e.patientID
        inner join structsocialhistory SSH on SSH.encounterId = e.encounterID
        INNER JOIN (structdatadetail SDD ON SSH.catid = SDD.catid      
                AND     SSH.itemid = SDD.itemid 
                AND   SSH.detailid = SDD.id)
        WHERE SSH.catid  = 10619
        AND SSH.itemid = 318681 
        AND SSH.detailID = 49 AND SSH.ValueID =82
        OR SSH.detailID = 51 AND SSH.ValueID IN(88,89,145)
        AND SSH.detailID IN(52,53,98,99,100,101,106,107,108,109) AND CONVERT(VARCHAR(MAX), SSH.Value) = 'Yes'

    GROUP BY
        e.patientID
) SmokeCounselMaxDate on SmokeCounselMaxDate.patientid = u.uid

Upvotes: 0

Views: 103

Answers (2)

Kedar Limaye
Kedar Limaye

Reputation: 1041

Check this I removed the brackets at table structdatadetail

left join ( SELECT MAX(e.date) as MaxDate ,e.patientID FROM labdata ld inner join enc e on e.encounterID = ld.EncounterId and e.deleteFlag = 0 and ld.deleteFlag = 0 and ld.futureflag = 0 and ld.cancelled = 0 and ld.received = 1 inner join @patients t on t.patientID = e.patientID inner join structsocialhistory SSH on SSH.encounterId = e.encounterID INNER JOIN structdatadetail SDD ON SSH.catid = SDD.catid
AND SSH.itemid = SDD.itemid AND SSH.detailid = SDD.id WHERE SSH.catid = 10619 AND SSH.itemid = 318681 AND SSH.detailID = 49 AND SSH.ValueID =82 OR SSH.detailID = 51 AND SSH.ValueID IN(88,89,145) AND SSH.detailID IN(52,53,98,99,100,101,106,107,108,109) AND CONVERT(VARCHAR(MAX), SSH.Value) = 'Yes'

GROUP BY
    e.patientID

) SmokeCounselMaxDate on SmokeCounselMaxDate.patientid = u.uid

Upvotes: -1

Caius Jard
Caius Jard

Reputation: 74605

Remove the brackets from this bit:

INNER JOIN (structdatadetail SDD ON SSH.catid = SDD.catid      
            AND     SSH.itemid = SDD.itemid 
            AND   SSH.detailid = SDD.id)

Upvotes: 3

Related Questions