Command missing expression error

[Former Member]

Below is the command that I'm trying to modify and I'm currently getting an error.  The lines in bold is what I added to the command.

SELECT DISTINCT OR_LOG.LOG_ID,OR_LOG.LOC_ID,PATIENT.PAT_NAME,V_MRN.MRN,PATIENT.PAT_ID

,TO_CHAR(OR_LOG.SURGERY_DATE,'MM/DD/YYYY') AS SURGERY_DATE,ROOM.PROV_NAME AS ROOM_NAME

,DIABETES.CODE,DIABETES.PARENT_NAME,DIABETES.DX_NAME

  FROM EPICADM.OR_LOG OR_LOG INNER JOIN EPICADM.PATIENT PATIENT ON OR_LOG.PAT_ID=PATIENT.PAT_ID

  INNER JOIN EPICADM.PAT_OR_ADM_LINK ADM_LINK ON OR_LOG.LOG_ID=ADM_LINK.OR_CASELOG_ID

  INNER JOIN EPICADM.PAT_ENC_HSP ENC_HSP ON ADM_LK.OR_LINK_CSN=ENC_HSP.PAT_ENC_CSN_ID

  INNER JOIN EPICADM.V_FV_MRN V_MRN ON OR_LOG.PAT_ID=V_MRN.PAT_ID

  INNER JOIN EPICADM.CLARITY_LOC CLARITY_LOC ON OR_LOG.LOC_ID=CLARITY_LOC.LOC_ID

  LEFT OUTER JOIN EPICADM.CLARITY_SER room ON OR_LOG.ROOM_ID = ROOM.PROV_ID

  INNER JOIN

  (

SELECT DISTINCT PAT_ID,REF_BILL_CODE,PARENT_NAME,DX_NAME

FROM

(

SELECT PROBLEM_LIST.PAT_ID,DIAB.REF_BILL_CODE,DIAB.DX_NAME,DIAB.PARENT_NAME

FROM PROBLEM_LIST INNER JOIN

(

SELECT EDG.DX_ID,EDG.DX_NAME,EDG.REF_BILL_CODE,EDG_PAR.DX_NAME AS PARENT_NAME,EDG_PAR.PAT_FRIENDLY_TEXT

FROM CLARITY_EDG EDG INNER JOIN CLARITY_EDG EDG_PAR ON EDG.PARENT_DX_ID=EDG_PAR.DX_ID

(

SELECT EDG_CURRENT_ICD9.CODE,CLARITY_EDG.DX_NAME

FROM EDG_CURRENT_ICD9 ON CLARITY_EDG .DX_ID=EDG_CURRENT_ICD9.DX_ID

WHERE (EDG_CURRENT_ICD9.CODE BETWEEN '250' and '250.99' OR EDG_CURRENT_ICD9.CODE BETWEEN '305' AND '305.99')

) DIAB ON PROBLEM_LIST.DX_ID=DIAB.DX_ID

UNION ALL

SELECT MEDICAL_HX.PAT_ID,DIAB2.REF_BILL_CODE,DIAB2.DX_NAME,DIAB2.PARENT_NAME

FROM MEDICAL_HX INNER JOIN

(

SELECT EDG.DX_ID,EDG.DX_NAME,EDG.REF_BILL_CODE,EDG_PAR.DX_NAME AS PARENT_NAME,EDG_PAR.PAT_FRIENDLY_TEXT

FROM CLARITY_EDG EDG INNER JOIN CLARITY_EDG EDG_PAR ON EDG.PARENT_DX_ID=EDG_PAR.DX_ID

(

SELECT EDG_CURRENT_ICD9.CODE,CLARITY_EDG.DX_NAME

FROM EDG_CURRENT_ICD9 ON CLARITY_EDG .DX_ID=EDG_CURRENT_ICD9.DX_ID

WHERE (EDG_CURRENT_ICD9.CODE BETWEEN '250' and '250.99' OR EDG_CURRENT_ICD9.CODE BETWEEN '305' AND '305.99')

)DIAB2 ON MEDICAL_HX.DX_ID=DIAB2.DX_ID

) CMB

) DIABETES ON OR_LOG.PAT_ID=DIABETES.PAT_ID

    WHERE (PATIENT.TEST_PATIENT_YN <> 'Y'

  OR PATIENT.TEST_PATIENT_YN     IS NULL)

  and OR_LOG.SURGERY_DATE BETWEEN epicadm.report_run_options('{?Report Run Options}','s',{?Start Date}) AND epicadm.report_run_options('{?Report Run Options}','e',{?End Date})

  AND OR_LOG.PROC_NOT_PERF_C     IS NULL

[DellSC]

SELECT EDG.DX_ID,EDG.DX_NAME,EDG.REF_BILL_CODE,EDG_PAR.DX_NAME AS PARENT_NAME,EDG_PAR.PAT_FRIENDLY_TEXT

FROM CLARITY_EDG EDG INNER JOIN CLARITY_EDG EDG_PAR ON EDG.PARENT_DX_ID=EDG_PAR.DX_ID

(

SELECT EDG_CURRENT_ICD9.CODE,CLARITY_EDG.DX_NAME

FROM EDG_CURRENT_ICD9 ON CLARITY_EDG
.DX_ID=EDG_CURRENT_ICD9.DX_ID

WHERE (EDG_CURRENT_ICD9.CODE BETWEEN '250' and '250.99' OR
EDG_CURRENT_ICD9.CODE BETWEEN '305' AND '305.99')

) DIAB ON PROBLEM_LIST.DX_ID=DIAB.DX_ID

See the part I highlighted in blue.  You're treating this as if it's a join - but you can't do that inside a sub-query.  You're also not including the EDG_CURRENT_ICD9.DX_ID code in the select so you can't use it in the join.  See below for the corrected SQL:

SELECT EDG.DX_ID,EDG.DX_NAME,EDG.REF_BILL_CODE,EDG_PAR.DX_NAME AS PARENT_NAME,EDG_PAR.PAT_FRIENDLY_TEXT

FROM CLARITY_EDG EDG INNER JOIN CLARITY_EDG EDG_PAR ON EDG.PARENT_DX_ID=EDG_PAR.DX_ID

INNER JOIN

(

SELECT EDG_CURRENT_ICD9.CODE,CLARITY_EDG.DX_NAME, EDG_CURRENT_ICD9.DX_ID

FROM EDG_CURRENT_ICD9

WHERE (EDG_CURRENT_ICD9.CODE BETWEEN '250' and '250.99' OR EDG_CURRENT_ICD9.CODE BETWEEN '305' AND '305.99')

) DIAB ON PROBLEM_LIST.DX_ID=DIAB.DX_ID

-Dell

[Former Member]

Now I'm getting a missing righ parenthesis error.

SELECT DISTINCT OR_LOG.LOG_ID,OR_LOG.LOC_ID,PATIENT.PAT_NAME,V_MRN.MRN,PATIENT.PAT_ID

,TO_CHAR(OR_LOG.SURGERY_DATE,'MM/DD/YYYY') AS SURGERY_DATE,ROOM.PROV_NAME AS ROOM_NAME

,DIABETES.REF_BILL_CODE,DIABETES.PARENT_NAME,DIABETES.DX_NAME

  FROM EPICADM.OR_LOG OR_LOG INNER JOIN EPICADM.PATIENT PATIENT ON OR_LOG.PAT_ID=PATIENT.PAT_ID

  INNER JOIN EPICADM.PAT_OR_ADM_LINK ADM_LINK ON OR_LOG.LOG_ID=ADM_LINK.OR_CASELOG_ID

  INNER JOIN EPICADM.PAT_ENC_HSP ENC_HSP ON ADM_LINK.OR_LINK_CSN=ENC_HSP.PAT_ENC_CSN_ID

  INNER JOIN EPICADM.V_FV_MRN V_MRN ON OR_LOG.PAT_ID=V_MRN.PAT_ID

  INNER JOIN EPICADM.CLARITY_LOC CLARITY_LOC ON OR_LOG.LOC_ID=CLARITY_LOC.LOC_ID

  LEFT OUTER JOIN EPICADM.CLARITY_SER room ON OR_LOG.ROOM_ID = ROOM.PROV_ID

  INNER JOIN

  (

SELECT DISTINCT PAT_ID,REF_BILL_CODE,PARENT_NAME,DX_NAME

FROM

(

SELECT PROBLEM_LIST.PAT_ID,DIAB.REF_BILL_CODE,DIAB.DX_NAME,DIAB.PARENT_NAME

FROM PROBLEM_LIST INNER JOIN

(

SELECT EDG.DX_ID,EDG.DX_NAME,EDG.REF_BILL_CODE,EDG_PAR.DX_NAME AS PARENT_NAME,EDG_PAR.PAT_FRIENDLY_TEXT

FROM CLARITY_EDG EDG INNER JOIN CLARITY_EDG EDG_PAR ON EDG.PARENT_DX_ID=EDG_PAR.DX_ID

INNER JOIN

(

SELECT EDG_CURRENT_ICD9.CODE, CLARITY_EDG.DX_NAME

FROM EDG_CURRENT_ICD9 ON CLARITY_EDG .DX_ID=EDG_CURRENT_ICD9.DX_ID

WHERE (EDG_CURRENT_ICD9.CODE BETWEEN '250' and '250.99' OR EDG.REF_BILL_CODE BETWEEN '305' AND '305.99')

) DIAB ON PROBLEM_LIST.DX_ID=DIAB.DX_ID

UNION ALL

SELECT MEDICAL_HX.PAT_ID,DIAB2.REF_BILL_CODE,DIAB2.DX_NAME,DIAB2.PARENT_NAME

FROM MEDICAL_HX INNER JOIN

(

SELECT EDG.DX_ID,EDG.DX_NAME,EDG.REF_BILL_CODE,EDG_PAR.DX_NAME AS PARENT_NAME,EDG_PAR.PAT_FRIENDLY_TEXT

FROM CLARITY_EDG EDG INNER JOIN CLARITY_EDG EDG_PAR ON EDG.PARENT_DX_ID=EDG_PAR.DX_ID

(

SELECT EDG_CURRENT_ICD9.CODE, CLARITY_EDG.DX_NAME

FROM EDG_CURRENT_ICD9 ON CLARITY_EDG .DX_ID=EDG_CURRENT_ICD9.DX_ID

WHERE (EDG_CURRENT_ICD9.CODE BETWEEN '250' and '250.99' OR EDG.REF_BILL_CODE BETWEEN '305' AND '305.99')

) DIAB2 ON MEDICAL_HX.DX_ID=DIAB2.DX_ID

) CMB

) DIABETES ON OR_LOG.PAT_ID=DIABETES.PAT_ID

    WHERE (PATIENT.TEST_PATIENT_YN <> 'Y'

  OR PATIENT.TEST_PATIENT_YN     IS NULL)

  and OR_LOG.SURGERY_DATE BETWEEN epicadm.report_run_options('{?Report Run Options}','s',{?Start Date}) AND epicadm.report_run_options('{?Report Run Options}','e',{?End Date})

  AND OR_LOG.PROC_NOT_PERF_C     IS NULL

[DellSC]

The Command Editor is not really designed for debugging/validating SQL, but other tools are.  So, I would move your SQL out to a tool like SQL Server Management Studio or Toad (whatever is applicable for your database) and get it working (providing "dummy" values for the parameters) there.  Then paste it into the Command Editor and add the parameters back into it.

-Dell