Skip to Content

Archived discussions are read-only. Learn more about SAP Q&A

Error while adding cmd line

The sql below runs without any errors in Oracle SQL developer but when I add it to crystal I get the following error ORA-24374 define not done before fetch or execute and  fetch.  How can I fix this?

-- 4-14-15 12:52 PM combining lab and flowsheet data
Select
vf.pat_name,
vf.mrn,
vf.pat_id,
(penc2.HEIGHT) as HEIGHT,
round(penc2.WEIGHT/16,0) AS WEIGHT,
logs.log_id,
logs.surgery_date,
penc.hsp_account_id,
max(val.SMRTDTA_ELEM_VALUE) as ASA,
surg.PROV_NAME as Surgeon,
anes.PROV_NAME as Anes,
max(case when staff.staff_type_c = '70'  then perf.prov_name --Perf Name
else null end) Perfusionist,


aproc.PROC_DISPLAY_NAME as Procedure,
room.PROV_NAME as Room,
max(TO_CHAR(startt.TRACKING_TIME_IN ,'HH24:MI'))as InTime,
max(TO_CHAR(endt.TRACKING_TIME_IN ,'HH24:MI')) as OutTime,

meas.RECORDED_TIME,
meas.line,

max(case when meas.flo_meas_id = '6214' then to_number(meas.meas_value)--Est Blood Loss
else null end) EBL_VAL,

max(case when meas.flo_meas_id = '11215852'  then to_number (meas.meas_value) --CELL Saver
else null end) CEL_VAL,

max(case when meas.flo_meas_id = '6256'  then to_number(meas.meas_value) --CRYO
else null end) CRY_VAL,

max(case when meas.flo_meas_id in ('6227','3040101404') then to_number( meas.meas_value) --RBC
else null end) RBC_VAL,

max(case when meas.flo_meas_id in ('6233','3040101401','3040101402') then to_number( meas.meas_value) --PLT
else null end) PLT_VAL,

max(case when meas.flo_meas_id in ('6230','3040101397')  then to_number(meas.meas_value) --Fresh Plasma Trns
else null end) FP_VAL,


max(case when meas.flo_meas_id = '61' then to_number( meas.meas_value) --Urine Output
else null end)URINE_VAL,

max(case when meas.flo_meas_id = '64' then to_number(meas.meas_value) --All other output during surgery
else null end) OUTPUT_VAL,


(lab_291.SPECIMN_TAKEN_DATE) as Collect_date,
(TO_CHAR(lab_291.SPECIMN_TAKEN_TIME ,'HH24:MI'))as Colllect_time,

max(CASE WHEN lab_291.COMPONENT_ID = 3  then lab_291.ORD_VALUE
   END) HGB,
  
lab_291.enc_rank,
LAB_291.InTime_RES,
lab_291.RES_LOG_ID,
   
meds.DESCRIPTION as Fluid, 

max(CASE WHEN to_number(mar.SIG) >= 100 and unit.DISP_QTYUNIT_C = 1 then (mar.SIG)
    ELSE Null END) Total,
unit.name as Unit_Name
   

    
from or_log logs
left join epicadm.v_fv_mrn  vf on logs.pat_id = vf.PAT_ID
left join PAT_OR_ADM_LINK plink on logs.log_id = plink.log_id
left join pat_enc penc2 on plink.OR_LINK_CSN = penc2.PAT_ENC_CSN_ID
left join OR_LOG_ALL_PROC aproc on logs.log_id = aproc.log_id
left join OR_LOG_ALL_SURG asurg on logs.log_id = asurg.log_id
left outer join OR_LOG_CASE_TIMES startt on logs.log_id = startt.log_id
left outer join or_log_case_times endt on logs.log_id = endt.log_id
left join CLARITY_SER surg on asurg.SURG_ID = surg.PROV_ID
left join EPICADM.OR_LOG_ALL_STAFF staff on (logs.log_id = staff.LOG_ID--staff
and staff.staff_type_c = '70')
left join epicadm.clarity_ser perf on staff.STAFF_ID = perf.PROV_ID--Perfusionist
left join clarity_ser room on logs.ROOM_ID = room.PROV_ID
left join PAT_OR_ADM_LINK plink on logs.log_id = plink.log_id
left join F_AN_RECORD_SUMMARY summ on logs.log_id = summ.log_id
left join pat_enc penc on summ.AN_52_ENC_CSN_ID = penc.PAT_ENC_CSN_ID
left join IP_FLWSHT_REC rec on penc.INPATIENT_DATA_ID = rec.INPATIENT_DATA_ID
left join IP_FLWSHT_MEAS meas on (rec.FSD_ID = meas.FSD_ID
and meas.FLO_MEAS_ID in ('61','64','6214','11215852','6227','3040101404','6256',
'6233','3040101401','3040101402','6230','3040101397'))

left join SMRTDTA_ELEM_DATA data on summ.AN_PREOP_NOTE_ID = data.RECORD_ID_VARCHAR
left join SMRTDTA_ELEM_VALUE val on data.HLV_ID = val.HLV_ID

left join clarity_ser anes on summ.AN_RESP_PROV_ID = anes.PROV_ID

--Fluids
left join MAR_ADMIN_INFO mar on (summ.AN_52_ENC_CSN_ID = mar.MAR_ENC_CSN
and to_number(mar.SIG) >= 100 and mar.DOSE_UNIT_C= 1)
left join order_med meds on mar.ORDER_MED_ID = meds.ORDER_MED_ID
left join ZC_MED_UNIT unit on mar.DOSE_UNIT_C = unit.DISP_QTYUNIT_C


left join
(
SELECT  ORDER_PROC.PAT_ID
  , ORDER_PROC.DESCRIPTION
  , ORDER_PROC.ORDER_TIME
  , ORDER_PROC.PROC_CODE
  , ORDER_PROC_2.SPECIMN_TAKEN_DATE
  , ORDER_PROC_2.SPECIMN_TAKEN_TIME
  , ORDER_RESULTS.ORD_VALUE
  , ORDER_RESULTS.COMPONENT_ID
  , LOGS2.LOG_ID "RES_LOG_ID"
  ,MAX( TO_CHAR(startt.TRACKING_TIME_IN ,'HH24:MI'))as InTime_RES
  , ROW_NUMBER() OVER(PARTITION BY ORDER_PROC.PAT_ID ORDER BY ORDER_PROC_2.SPECIMN_TAKEN_DATE  DESC NULLS LAST) ENC_RANK
FROM EPICADM.ORDER_PROC ORDER_PROC

  LEFT OUTER JOIN EPICADM.ORDER_PROC_2 ORDER_PROC_2
    ON ORDER_PROC.ORDER_PROC_ID = ORDER_PROC_2.ORDER_PROC_ID
    
  LEFT OUTER JOIN EPICADM.ORDER_RESULTS ORDER_RESULTS
    ON ORDER_PROC.ORDER_PROC_ID = ORDER_RESULTS.ORDER_PROC_ID
 
  LEFT JOIN OR_LOG LOGS2  ON ORDER_RESULTS.PAT_ID = LOGS2.PAT_ID
 
  left outer join OR_LOG_CASE_TIMES startt on logs2.log_id = startt.log_id
 
  LEFT JOIN EPICADM.V_FV_MRN VF2 ON ORDER_PROC.PAT_ID = VF2.PAT_ID
   
      where vf2.mrn = '00001
      AND ORDER_RESULTS.COMPONENT_ID = 3
      AND ORDER_RESULTS.LAB_STATUS_C = 3
      and ORDER_PROC_2.SPECIMN_TAKEN_DATE BETWEEN LOGS2.SURGERY_DATE -20
      AND LOGS2.SURGERY_DATE
     
      GROUP BY
      ORDER_PROC.PAT_ID
    , ORDER_PROC.DESCRIPTION
    , ORDER_PROC.ORDER_TIME
    , ORDER_PROC.PROC_CODE
    , ORDER_PROC_2.SPECIMN_TAKEN_DATE
    , ORDER_PROC_2.SPECIMN_TAKEN_TIME
    , ORDER_RESULTS.ORD_VALUE
    , ORDER_RESULTS.COMPONENT_ID
    , LOGS2.LOG_ID
     
      ) lab_291 on lab_291.RES_LOG_ID = LOGS.LOG_ID and lab_291.ENC_RANK = 1

where
logs.surgery_date between  TO_DATE('2015/05/06', 'yyyy/mm/dd') and
TO_DATE('2015/05/06', 'yyyy/mm/dd')
--{?Start Date} and {?End Date}
and logs.status_c = 2
--and logs.loc_id in {?Location}
and vf.mrn = '00001'
and aproc.LINE = 1
and asurg.ROLE_C = 1
and asurg.LINE =1
and startt.TRACKING_EVENT_C = '60'
and endt.TRACKING_EVENT_C= '110'
and data.ELEMENT_ID = 'EPIC#10040'

group by
vf.mrn,
vf.pat_name,
logs.surgery_date,
logs.log_id,
surg.PROV_NAME,
anes.PROV_NAME,
aproc.PROC_DISPLAY_NAME,
lab_291.SPECIMN_TAKEN_TIME,
lab_291.SPECIMN_TAKEN_DATE,
lab_291.RES_LOG_ID,
lab_291.enc_rank,
LAB_291.InTime_RES,
meds.DESCRIPTION,
surg.PROV_NAME,
anes.PROV_NAME,
room.PROV_NAME,
penc2.HEIGHT,
penc2.WEIGHT,
unit.name,
meas.RECORDED_TIME,
meas.line,
penc.hsp_account_id,
vf.pat_id

order by vf.pat_name,vf.mrn,logs.log_id

Former Member
Not what you were looking for? View more on this topic or Ask a question