Home » SQL & PL/SQL » SQL & PL/SQL » view to be split into multiple views (oracle 10g)
view to be split into multiple views [message #418032] |
Wed, 12 August 2009 11:04  |
rajasekhar857
Messages: 500 Registered: December 2008
|
Senior Member |
|
|
hi the below view is causing performance issue.is there any possibilty to solit into multiple views.everything here is based on user roles.how can i do this.please consider for 2 or 3 blocks atmost and provide me the way to do so feeling free
CREATE OR REPLACE FORCE VIEW emrts
(EMR_TRANSACTION_ID, TASK_ID, TASK_NAME, NODE_DEFINITION_ID, ID_VALUE, FLOW_ID, APPOINTMENT_DATE, APPOINTMENT_TIME, CREATED_DATE, APPOINTMENT_ID, FRONTDESK_COMMENTS, APPOINTMENT_STATUS, PATIENT_FIRSTNAME, PATIENT_LASTNAME, PATIENT_DOB, PATIENT_HOMEPHONE, PATIENT_ID, PATIENT_TYPE, PATIENT_GENDER, RACE_ETHNICITY_ID, MARITAL_STATUS, NAME, CONSULTANT_ID, CONSULTANT_NAME, ROLE_NAME, LOCATION_ID, REGISTER_ID, IS_EXPRESS, LAB_REQUEST_ID, SPECIMEN_ID , IS_ACTIVE) AS
SELECT EMR_TRANSACTION_ID, ETL.TASK_ID, TASK_NAME, '0' AS NODE_DEFINITION_ID, 0 AS ID_VALUE, '0' AS FLOW_ID,
'1/1/1900' AS APPOINTMENT_DATE, '12:00:00' AS APPOINTMENT_TIME, '1/1/1900' AS CREATED_DATE,
'0' AS APPOINTMENT_ID, '' AS FRONTDESK_COMMENTS, '' AS APPOINTMENT_STATUS, EPM.PATIENT_FIRSTNAME, EPM.PATIENT_LASTNAME, EPM.PATIENT_DOB,EPM.PATIENT_HOMEPHONE,
EPM.PATIENT_ID, '' AS PATIENT_TYPE, '' AS PATIENT_GENDER, '' AS RACE_ETHNICITY_ID, '' AS MARITAL_STATUS, '' AS NAME,
'' AS CONSULTANT_ID, '' AS CONSULTANT_NAME, ET.ROLE_NAME, EPM.LOCATION_ID AS LOCATION_ID, EPM.REGISTER_ID AS REGISTER_ID,
NVL(GEA.IS_EXPRESS,0) AS IS_EXPRESS, 0 AS LAB_REQUEST_ID, 0 AS SPECIMEN_ID , 0 AS IS_ACTIVE
FROM EMRTASKLISTLKUP ETL, EMRPATIENTSMASTER EPM ,EMRTRANSACTIONS ET,(SELECT MAX(IS_EXPRESS) AS IS_EXPRESS,PATIENT_ID
FROM EMRAPPOINTMENTDETAILS EA LEFT JOIN EMRSERVICEITEMS SRV ON EA.TP_ITEM_ID=SRV.TP_ITEM_DEAL_ID
WHERE EA.IS_LAB_APMT != 1 AND EA.APPOINTMENT_DATE<=(SELECT SERVER_DATE FROM EMRSERVERDATE) GROUP BY EA.PATIENT_ID ) GEA ,EMRPatientTaskInfo PTI
WHERE ETL.TASK_ID = ET.TASK_ID AND ET.PATIENT_ID = EPM.PATIENT_ID AND ET.TASK_ID IN (1,2) AND ET.PATIENT_ID = GEA.PATIENT_ID
AND ET.PATIENT_ID = PTI.PATIENT_ID AND PTI.TASK_ID IN (1,2) AND PTI.TASK_COUNT > 1
UNION ALL
SELECT EMR_TRANSACTION_ID, ETL.TASK_ID, TASK_NAME, '0' AS NODE_DEFINITION_ID, 0 AS ID_VALUE, '0' AS FLOW_ID,
TO_CHAR(EAD.APPOINTMENT_DATE,'DD/MM/YYYY') AS APPOINTMENT_DATE, TO_CHAR(EAD.APPOINTMENT_TIME,'HH:MI:SS AM') AS APPOINTMENT_TIME,
TO_CHAR(EAD.CREATED_DATE,'DD/MM/YYYY') AS CREATED_DATE, TO_CHAR(EAD.APPOINTMENT_ID) AS APPOINTMENT_ID, EAD.FRONTDESK_COMMENTS,
'' AS APPOINTMENT_STATUS, EPM.PATIENT_FIRSTNAME, EPM.PATIENT_LASTNAME, EPM.PATIENT_DOB,EPM.PATIENT_HOMEPHONE,
EPM.PATIENT_ID, '' AS PATIENT_TYPE, '' AS PATIENT_GENDER, '' AS RACE_ETHNICITY_ID, '' AS MARITAL_STATUS, '' AS NAME,
EAD.CONSULTANT_ID, ECD.CONSULTANT_NAME, ET.ROLE_NAME, EPM.LOCATION_ID AS LOCATION_ID, EPM.REGISTER_ID AS REGISTER_ID,
NVL(GEA.IS_EXPRESS,0) AS IS_EXPRESS, 0 AS LAB_REQUEST_ID, 0 AS SPECIMEN_ID , 0 AS IS_ACTIVE
FROM EMRTASKLISTLKUP ETL, EMRPATIENTSMASTER EPM ,EMRTRANSACTIONS ET,(SELECT MAX(IS_EXPRESS) AS IS_EXPRESS,PATIENT_ID
FROM EMRAPPOINTMENTDETAILS EA LEFT JOIN EMRSERVICEITEMS SRV ON EA.TP_ITEM_ID=SRV.TP_ITEM_DEAL_ID
WHERE EA.IS_LAB_APMT != 1 AND EA.APPOINTMENT_DATE<=(SELECT SERVER_DATE FROM EMRSERVERDATE) GROUP BY EA.PATIENT_ID ) GEA,
EMRAPPOINTMENTDETAILS EAD,EMRCONSULTANTDETAILS ECD,EMRPatientTaskInfo PTI WHERE ETL.TASK_ID = ET.TASK_ID AND ET.PATIENT_ID = EPM.PATIENT_ID
AND ET.TASK_ID IN (1,2)AND ET.PATIENT_ID = GEA.PATIENT_ID AND ET.PATIENT_ID = EAD.PATIENT_ID AND ECD.CONSULTANT_ID = EAD.CONSULTANT_ID
AND ET.PATIENT_ID = PTI.PATIENT_ID AND PTI.TASK_ID IN (1,2) AND PTI.TASK_COUNT=1
UNION ALL
SELECT ET.EMR_TRANSACTION_ID, ET.TASK_ID, ETL.TASK_NAME, TO_CHAR(ET.NODE_DEFINITION_ID) AS NODE_DEFINITION_ID, ET.ID_VALUE, TO_CHAR(ET.FLOW_ID) AS FLOW_ID,
TO_CHAR(EA.APPOINTMENT_DATE,'DD/MM/YYYY') AS APPOINTMENT_DATE, TO_CHAR(EA.APPOINTMENT_TIME,'HH:MI:SS AM') AS APPOINTMENT_TIME,
TO_CHAR(EA.CREATED_DATE,'DD/MM/YYYY') AS CREATED_DATE, TO_CHAR(EA.APPOINTMENT_ID) AS APPOINTMENT_ID,EA.FRONTDESK_COMMENTS, EAL.APPOINTMENT_STATUS,
EPM.PATIENT_FIRSTNAME, EPM.PATIENT_LASTNAME, EPM.PATIENT_DOB, EPM.PATIENT_HOMEPHONE, EPM.PATIENT_ID, EPM.PATIENT_TYPE,EPM.PATIENT_GENDER,EPM.RACE_ETHNICITY_ID,EPM.MARITAL_STATUS,
ETN.NAME, EA.CONSULTANT_ID,ECD.CONSULTANT_NAME, ET.ROLE_NAME, EA.APPOINTMENT_LOCATION_ID AS LOCATION_ID, EPM.REGISTER_ID AS REGISTER_ID, NVL(IS_EXPRESS,0) AS IS_EXPRESS, 0 AS LAB_REQUEST_ID, 0 AS SPECIMEN_ID , 0 AS IS_ACTIVE
FROM EMRTransactions ET, EMRTaskListLkup ETL, EMRAppointmentDetails EA, EMRPatientsMaster EPM,
EMRAppointmentStatusLkup EAL, EMRTempNodeDetails ETN ,EMRCONSULTANTDETAILS ECD
WHERE ETL.TASK_ID = ET.TASK_ID AND EA.APPOINTMENT_ID = ET.ID_VALUE AND EPM.PATIENT_ID = EA.PATIENT_ID AND
EAL.APPOINTMENT_STATUS_ID = EA.APPOINTMENT_STATUS_ID AND ETN.NODE_DEFINITION_ID = ET.NODE_DEFINITION_ID AND ET.TASK_ID IN ( 3,28) AND ECD.CONSULTANT_ID = EA.CONSULTANT_ID
UNION ALL
SELECT ET.EMR_TRANSACTION_ID, ET.TASK_ID, ETL.TASK_NAME, '0' AS NODE_DEFINITION_ID, NVL(ET.ID_VALUE, '0') AS ID_VALUE, '0' AS FLOW_ID,
'1/1/1900' AS APPOINTMENT_DATE, '12:00:00' AS APPOINTMENT_TIME, '1/1/1900' AS CREATED_DATE, '0' AS APPOINTMENT_ID, '' AS FRONTDESK_COMMENTS,
'' AS APPOINTMENT_STATUS, EPM.PATIENT_FIRSTNAME, EPM.PATIENT_LASTNAME, EPM.PATIENT_DOB, EPM.PATIENT_HOMEPHONE,
EPM.PATIENT_ID, '' AS PATIENT_TYPE,'' AS PATIENT_GENDER,'' AS RACE_ETHNICITY_ID,'' AS MARITAL_STATUS, '' AS NAME, ET.USER_LOGIN AS CONSULTANT_ID, '' AS CONSULTANT_NAME, ET.ROLE_NAME, EPM.LOCATION_ID, EPM.REGISTER_ID AS REGISTER_ID, NVL(IS_EXPRESS,0) AS IS_EXPRESS, 0 AS LAB_REQUEST_ID, 0 AS SPECIMEN_ID , 0 AS IS_ACTIVE
FROM EMRTransactions ET, EMRTaskListLkup ETL, EMRPatientsMaster EPM
WHERE ETL.TASK_ID = ET.TASK_ID AND ET.PATIENT_ID = EPM.PATIENT_ID AND ET.TASK_ID IN (5, 8, 71)
UNION ALL
SELECT ET.EMR_TRANSACTION_ID, ET.TASK_ID,'ENCOUNTER' AS TASK_NAME, TO_CHAR(ET.NODE_DEFINITION_ID) AS NODE_DEFINITION_ID, ET.ID_VALUE, TO_CHAR(ET.FLOW_ID) AS FLOW_ID,
TO_CHAR(EA.APPOINTMENT_DATE,'DD/MM/YYYY') AS APPOINTMENT_DATE, TO_CHAR(EA.APPOINTMENT_TIME,'HH:MI:SS AM') AS APPOINTMENT_TIME,
TO_CHAR(EA.CREATED_DATE,'DD/MM/YYYY') AS CREATED_DATE, TO_CHAR(EA.APPOINTMENT_ID) AS APPOINTMENT_ID,EA.REASON || ' ' || EA.FRONTDESK_COMMENTS AS FRONTDESK_COMMENTS, EAL.APPOINTMENT_STATUS,
EPM.PATIENT_FIRSTNAME, EPM.PATIENT_LASTNAME, EPM.PATIENT_DOB, EPM.PATIENT_HOMEPHONE, EPM.PATIENT_ID, EPM.PATIENT_TYPE,EPM.PATIENT_GENDER,EPM.RACE_ETHNICITY_ID,EPM.MARITAL_STATUS,
'' AS NAME, ET.USER_LOGIN AS CONSULTANT_ID, ECD.CONSULTANT_NAME, ET.ROLE_NAME, EA.APPOINTMENT_LOCATION_ID AS LOCATION_ID, EPM.REGISTER_ID AS REGISTER_ID, NVL(IS_EXPRESS,0) AS IS_EXPRESS, 0 AS LAB_REQUEST_ID, 0 AS SPECIMEN_ID, 1 AS IS_ACTIVE
FROM EMRAppointmentDetailsHistory EA
LEFT JOIN EMRTransactions ET ON ET.ID_VALUE = EA.APPOINTMENT_ID
LEFT JOIN EMRPatientsMaster EPM ON EA.PATIENT_ID = EPM.PATIENT_ID
LEFT JOIN EMRAppointmentStatusLkup EAL ON EAL.APPOINTMENT_STATUS_ID = EA.APPOINTMENT_ID
LEFT JOIN EMRConsultantDetails ECD ON ECD.CONSULTANT_ID = EA.CONSULTANT_ID
WHERE ET.TASK_ID = 9 AND EA.APPOINTMENT_STATUS_ID = 2
AND EXISTS (SELECT EMR_TRANSACTION_ID FROM EMRTRANSACTIONS ET1
WHERE ET1.TASK_ID=108 AND ET1.PATIENT_ID = ET.PATIENT_ID AND ET.ID_VALUE = ET1.ID_VALUE)
UNION ALL
SELECT ET.EMR_TRANSACTION_ID, ET.TASK_ID,'ENCOUNTER' AS TASK_NAME, TO_CHAR(ET.NODE_DEFINITION_ID) AS NODE_DEFINITION_ID, ET.ID_VALUE, TO_CHAR(ET.FLOW_ID) AS FLOW_ID,
TO_CHAR(EA.APPOINTMENT_DATE,'DD/MM/YYYY') AS APPOINTMENT_DATE, TO_CHAR(EA.APPOINTMENT_TIME,'HH:MI:SS AM') AS APPOINTMENT_TIME,
TO_CHAR(EA.CREATED_DATE,'DD/MM/YYYY') AS CREATED_DATE, TO_CHAR(EA.APPOINTMENT_ID) AS APPOINTMENT_ID,EA.REASON || ' ' || EA.FRONTDESK_COMMENTS AS FRONTDESK_COMMENTS, EAL.APPOINTMENT_STATUS,
EPM.PATIENT_FIRSTNAME, EPM.PATIENT_LASTNAME, EPM.PATIENT_DOB, EPM.PATIENT_HOMEPHONE, EPM.PATIENT_ID, EPM.PATIENT_TYPE,EPM.PATIENT_GENDER,EPM.RACE_ETHNICITY_ID,EPM.MARITAL_STATUS,
'' AS NAME, ET.USER_LOGIN AS CONSULTANT_ID, ECD.CONSULTANT_NAME, ET.ROLE_NAME, EA.APPOINTMENT_LOCATION_ID AS LOCATION_ID, EPM.REGISTER_ID AS REGISTER_ID, NVL(IS_EXPRESS,0) AS IS_EXPRESS, 0 AS LAB_REQUEST_ID, 0 AS SPECIMEN_ID, 0 AS IS_ACTIVE
FROM EMRAppointmentDetailsHistory EA
LEFT JOIN EMRTransactions ET ON ET.ID_VALUE = EA.APPOINTMENT_ID
LEFT JOIN EMRPatientsMaster EPM ON EA.PATIENT_ID = EPM.PATIENT_ID
LEFT JOIN EMRAppointmentStatusLkup EAL ON EAL.APPOINTMENT_STATUS_ID = EA.APPOINTMENT_ID
LEFT JOIN EMRConsultantDetails ECD ON ECD.CONSULTANT_ID = EA.CONSULTANT_ID
WHERE ET.TASK_ID = 9 AND EA.APPOINTMENT_STATUS_ID = 2
AND NOT EXISTS (SELECT EMR_TRANSACTION_ID FROM EMRTRANSACTIONS ET1
WHERE ET1.TASK_ID=108 AND ET1.PATIENT_ID = ET.PATIENT_ID AND ET.ID_VALUE = ET1.ID_VALUE)
UNION ALL
SELECT ET.EMR_TRANSACTION_ID, ET.TASK_ID, ETL.TASK_NAME, '0' AS NODE_DEFINITION_ID, EEC.ECHART_ID AS ID_VALUE, '0' AS FLOW_ID,
'1/1/1900' AS APPOINTMENT_DATE, '12:00:00' AS APPOINTMENT_TIME, '1/1/1900' AS CREATED_DATE, '0' AS APPOINTMENT_ID, '' AS FRONTDESK_COMMENTS,
'' AS APPOINTMENT_STATUS, EPM.PATIENT_FIRSTNAME, EPM.PATIENT_LASTNAME, EPM.PATIENT_DOB, EPM.PATIENT_HOMEPHONE,
EPM.PATIENT_ID, '' AS PATIENT_TYPE,'' AS PATIENT_GENDER,'' AS RACE_ETHNICITY_ID,'' AS MARITAL_STATUS, '' AS NAME, EEC.CONSULTANT_ID, '' AS CONSULTANT_NAME, ET.ROLE_NAME, EPM.LOCATION_ID AS LOCATION_ID, EPM.REGISTER_ID AS REGISTER_ID, NVL(IS_EXPRESS,0) AS IS_EXPRESS, 0 AS LAB_REQUEST_ID, 0 AS SPECIMEN_ID , 0 AS IS_ACTIVE
FROM EMRTransactions ET, EMRTaskListLkup ETL, EMRPatientsMaster EPM, EMREcharts EEC
WHERE ETL.TASK_ID = ET.TASK_ID AND ET.PATIENT_ID = EPM.PATIENT_ID AND ET.PATIENT_ID = EEC.PATIENT_ID AND
EEC.CONSULTANT_ID = ET.USER_LOGIN AND ET.ID_VALUE = EEC.ECHART_ID AND EEC.VERIFICATION_REQD = 1 AND ET.TASK_ID = 10
UNION ALL
SELECT ET.EMR_TRANSACTION_ID, ET.TASK_ID, ETL.TASK_NAME, '0' AS NODE_DEFINITION_ID, NVL(ET.ID_VALUE, '0') AS ID_VALUE, '0' AS FLOW_ID,
'1/1/1900' AS APPOINTMENT_DATE, '12:00:00' AS APPOINTMENT_TIME, '1/1/1900' AS CREATED_DATE, '0' AS APPOINTMENT_ID, '' AS FRONTDESK_COMMENTS,
'' AS APPOINTMENT_STATUS, EPM.PATIENT_FIRSTNAME, EPM.PATIENT_LASTNAME, EPM.PATIENT_DOB, EPM.PATIENT_HOMEPHONE,
EPM.PATIENT_ID, '' AS PATIENT_TYPE,'' AS PATIENT_GENDER,'' AS RACE_ETHNICITY_ID,'' AS MARITAL_STATUS, '' AS NAME, '' AS CONSULTANT_ID, '' AS CONSULTANT_NAME, ET.ROLE_NAME, EPM.LOCATION_ID AS LOCATION_ID, EPM.REGISTER_ID AS REGISTER_ID, NVL(IS_EXPRESS,0) AS IS_EXPRESS, 0 AS LAB_REQUEST_ID, 0 AS SPECIMEN_ID , 0 AS IS_ACTIVE
FROM EMRTransactions ET, EMRTaskListLkup ETL, EMRPatientsMaster EPM
WHERE ETL.TASK_ID = ET.TASK_ID AND ET.PATIENT_ID = EPM.PATIENT_ID AND ET.TASK_ID = 11
UNION ALL
SELECT ET.EMR_TRANSACTION_ID, ET.TASK_ID, ETL.TASK_NAME, '0' AS NODE_DEFINITION_ID, NVL(ET.ID_VALUE, '0') AS ID_VALUE, '0' AS FLOW_ID,
'1/1/1900' AS APPOINTMENT_DATE, '12:00:00' AS APPOINTMENT_TIME, '1/1/1900' AS CREATED_DATE, '0' AS APPOINTMENT_ID, '' AS FRONTDESK_COMMENTS,
'' AS APPOINTMENT_STATUS, EPM.PATIENT_FIRSTNAME, EPM.PATIENT_LASTNAME, EPM.PATIENT_DOB, EPM.PATIENT_HOMEPHONE,
EPM.PATIENT_ID, '' AS PATIENT_TYPE,'' AS PATIENT_GENDER,'' AS RACE_ETHNICITY_ID,'' AS MARITAL_STATUS, '' AS NAME, ET.USER_LOGIN AS CONSULTANT_ID, '' AS CONSULTANT_NAME, ET.ROLE_NAME, EPM.LOCATION_ID, EPM.REGISTER_ID AS REGISTER_ID, NVL(IS_EXPRESS,0) AS IS_EXPRESS, 0 AS LAB_REQUEST_ID, 0 AS SPECIMEN_ID , 0 AS IS_ACTIVE
FROM EMRTransactions ET, EMRTaskListLkup ETL, EMRPatientsMaster EPM, EMRPatientNotes EPN
WHERE ETL.TASK_ID = ET.TASK_ID AND ET.PATIENT_ID = EPM.PATIENT_ID AND EPN.NOTES_ID = ET.ID_VALUE AND ET.TASK_ID = 12
UNION ALL
SELECT ET.EMR_TRANSACTION_ID, ET.TASK_ID, ETL.TASK_NAME, '0' AS NODE_DEFINITION_ID, NVL(ET.ID_VALUE, '0') AS ID_VALUE, '0' AS FLOW_ID,
'1/1/1900' AS APPOINTMENT_DATE, '12:00:00' AS APPOINTMENT_TIME, '1/1/1900' AS CREATED_DATE, '0' AS APPOINTMENT_ID, '' AS FRONTDESK_COMMENTS,
'' AS APPOINTMENT_STATUS, EPM.PATIENT_FIRSTNAME, EPM.PATIENT_LASTNAME, EPM.PATIENT_DOB, EPM.PATIENT_HOMEPHONE,
EPM.PATIENT_ID, '' AS PATIENT_TYPE,'' AS PATIENT_GENDER,'' AS RACE_ETHNICITY_ID,'' AS MARITAL_STATUS, '' AS NAME, ET.USER_LOGIN AS CONSULTANT_ID, '' AS CONSULTANT_NAME, ET.ROLE_NAME AS ROLE_NAME, EPM.LOCATION_ID, EPM.REGISTER_ID AS REGISTER_ID, NVL(IS_EXPRESS,0) AS IS_EXPRESS, 0 AS LAB_REQUEST_ID, 0 AS SPECIMEN_ID , 0 AS IS_ACTIVE
FROM EMRTransactions ET, EMRTaskListLkup ETL, EMRPatientsMaster EPM, EMRPatientNotes EPN
WHERE ETL.TASK_ID = ET.TASK_ID AND ET.PATIENT_ID = EPM.PATIENT_ID AND EPN.NOTES_ID = ET.ID_VALUE AND ET.TASK_ID = 13
UNION ALL
SELECT ET.EMR_TRANSACTION_ID, ET.TASK_ID, ETL.TASK_NAME, '0' AS NODE_DEFINITION_ID, NVL(ET.ID_VALUE, '0') AS ID_VALUE, '0' AS FLOW_ID,
NVL(TO_CHAR(EED.APPOINTMENT_DATE,'DD/MM/YYYY'), '1/1/1900') AS APPOINTMENT_DATE, '12:00:00' AS APPOINTMENT_TIME, NVL(TO_CHAR(EED.CREATED_DATE,'DD/MM/YYYY'), '1/1/1900')
AS CREATED_DATE, '0' AS APPOINTMENT_ID, '' AS FRONTDESK_COMMENTS, '' AS APPOINTMENT_STATUS, EPM.PATIENT_FIRSTNAME, EPM.PATIENT_LASTNAME,
EPM.PATIENT_DOB, EPM.PATIENT_HOMEPHONE, EPM.PATIENT_ID, '' AS PATIENT_TYPE,'' AS PATIENT_GENDER,'' AS RACE_ETHNICITY_ID,'' AS MARITAL_STATUS, '' AS NAME, EED.CONSULTANT_ID, '' AS CONSULTANT_NAME, ET.ROLE_NAME,
EPM.LOCATION_ID, EPM.REGISTER_ID AS REGISTER_ID, NVL(IS_EXPRESS,0) AS IS_EXPRESS, 0 AS LAB_REQUEST_ID, 0 AS SPECIMEN_ID , 0 AS IS_ACTIVE
FROM EMRTransactions ET, EMRTaskListLkup ETL, EMRPatientsMaster EPM, EMREncounterDetails EED, EMRFollowupAppointments EFA
WHERE ETL.TASK_ID = ET.TASK_ID AND ET.PATIENT_ID = EPM.PATIENT_ID AND ET.ID_VALUE = EFA.FOLLOWUP_ID AND
EFA.ENCOUNTER_ID = EED.ENCOUNTER_ID AND ET.TASK_ID = 14
UNION ALL
SELECT ET.EMR_TRANSACTION_ID, ET.TASK_ID, ETL.TASK_NAME, '0' AS NODE_DEFINITION_ID, ET.ID_VALUE, '0' AS FLOW_ID, TO_CHAR(EED.APPOINTMENT_DATE,'DD/MM/YYYY') AS APPOINTMENT_DATE,
'12:00:00' AS APPOINTMENT_TIME, TO_CHAR(EED.CREATED_DATE,'DD/MM/YYYY') AS CREATED_DATE,
TO_CHAR(EED.APPOINTMENT_ID) AS APPOINTMENT_ID,'' AS FRONTDESK_COMMENTS, '' AS APPOINTMENT_STATUS, EPM.PATIENT_FIRSTNAME,
EPM.PATIENT_LASTNAME, EPM.PATIENT_DOB, EPM.PATIENT_HOMEPHONE, EPM.PATIENT_ID, '' AS PATIENT_TYPE,'' AS PATIENT_GENDER,'' AS RACE_ETHNICITY_ID,'' AS MARITAL_STATUS, '' AS NAME,
EED.CONSULTANT_ID, '' AS CONSULTANT_NAME, ET.ROLE_NAME, EPM.LOCATION_ID, EPM.REGISTER_ID AS REGISTER_ID, NVL(IS_EXPRESS,0) AS IS_EXPRESS, 0 AS LAB_REQUEST_ID, 0 AS SPECIMEN_ID , 0 AS IS_ACTIVE
FROM EMRTransactions ET, EMRTaskListLkup ETL, EMRPatientsMaster EPM, EMRTranscriptionMapping ETM, EMREncounterDetails EED,
EMRTranscriptionData ETD
WHERE ETL.TASK_ID = ET.TASK_ID AND ET.ID_VALUE = ETM.TRANSCRIPTION_ID AND ETM.ENCOUNTER_ID = EED.ENCOUNTER_ID AND
ETD.IS_VERIFIED = 0 AND ETD.TRANSCRIPTION_ID = ETM.TRANSCRIPTION_ID AND EED.PATIENT_ID = EPM.PATIENT_ID AND ET.TASK_ID = 15
UNION ALL
SELECT ET.EMR_TRANSACTION_ID, ET.TASK_ID, 'SAVED ENCOUNTER' AS TASK_NAME, '0' AS NODE_DEFINITION_ID, NVL(ET.ID_VALUE, '0') AS ID_VALUE, '0' AS FLOW_ID,
TO_CHAR(EA.APPOINTMENT_DATE,'DD/MM/YYYY') AS APPOINTMENT_DATE, TO_CHAR(EA.APPOINTMENT_TIME,'HH:MI:SS AM') AS APPOINTMENT_TIME, TO_CHAR(EED.CREATED_DATE,'DD/MM/YYYY') AS CREATED_DATE, TO_CHAR(EA.APPOINTMENT_ID) AS APPOINTMENT_ID, EA.REASON || ' ' || EA.FRONTDESK_COMMENTS AS FRONTDESK_COMMENTS,
'' AS APPOINTMENT_STATUS, EPM.PATIENT_FIRSTNAME, EPM.PATIENT_LASTNAME, EPM.PATIENT_DOB, EPM.PATIENT_HOMEPHONE,
EPM.PATIENT_ID, EPM.PATIENT_TYPE,EPM.PATIENT_GENDER,EPM.RACE_ETHNICITY_ID,EPM.MARITAL_STATUS, '' AS NAME, ET.USER_LOGIN AS CONSULTANT_ID, ECD.CONSULTANT_NAME, ET.ROLE_NAME,
EPM.LOCATION_ID AS LOCATION_ID, EPM.REGISTER_ID AS REGISTER_ID, NVL(IS_EXPRESS,0) AS IS_EXPRESS, 0 AS LAB_REQUEST_ID, 0 AS SPECIMEN_ID , 0 AS IS_ACTIVE
FROM EMREncounterDetails EED
LEFT JOIN EMRAppointmentDetailsHistory EA ON EA.APPOINTMENT_ID = EED.APPOINTMENT_ID AND EA.APPOINTMENT_STATUS_ID = 2
LEFT JOIN EMRConsultantDetails ECD ON ECD.CONSULTANT_ID = EED.CONSULTANT_ID
LEFT JOIN EMRTransactions ET ON ET.ID_VALUE = EED.ENCOUNTER_ID
LEFT JOIN EMRPatientsMaster EPM ON EED.PATIENT_ID = EPM.PATIENT_ID
WHERE ET.TASK_ID = 16
UNION ALL
SELECT ET.EMR_TRANSACTION_ID, ET.TASK_ID, 'SAVED DM ENCOUNTER' AS TASK_NAME, '0' AS NODE_DEFINITION_ID, NVL(ET.ID_VALUE, '0') AS ID_VALUE, '0' AS FLOW_ID,
TO_CHAR(EA.APPOINTMENT_DATE,'DD/MM/YYYY') AS APPOINTMENT_DATE, TO_CHAR(EA.APPOINTMENT_TIME,'HH:MI:SS AM') AS APPOINTMENT_TIME,
TO_CHAR(EED.CREATED_DATE,'DD/MM/YYYY') AS CREATED_DATE, TO_CHAR(EA.APPOINTMENT_ID) AS APPOINTMENT_ID,EA.FRONTDESK_COMMENTS,
'' AS APPOINTMENT_STATUS, EPM.PATIENT_FIRSTNAME, EPM.PATIENT_LASTNAME, EPM.PATIENT_DOB, EPM.PATIENT_HOMEPHONE,
EPM.PATIENT_ID,EPM.PATIENT_TYPE,EPM.PATIENT_GENDER,EPM.RACE_ETHNICITY_ID,EPM.MARITAL_STATUS, '' AS NAME, ET.USER_LOGIN AS CONSULTANT_ID, ECD.CONSULTANT_NAME, ET.ROLE_NAME,
EPM.LOCATION_ID AS LOCATION_ID, EPM.REGISTER_ID AS REGISTER_ID, NVL(IS_EXPRESS,0) AS IS_EXPRESS, 0 AS LAB_REQUEST_ID, 0 AS SPECIMEN_ID , 0 AS IS_ACTIVE
FROM EMREncounterDetails EED
LEFT JOIN EMRAppointmentDetailsHistory EA ON EA.APPOINTMENT_ID = EED.APPOINTMENT_ID AND EA.APPOINTMENT_STATUS_ID = 2
LEFT JOIN EMRConsultantDetails ECD ON ECD.CONSULTANT_ID = EED.CONSULTANT_ID
LEFT JOIN EMRTransactions ET ON ET.ID_VALUE = EED.ENCOUNTER_ID
LEFT JOIN EMRPatientsMaster EPM ON EED.PATIENT_ID = EPM.PATIENT_ID
WHERE ET.TASK_ID = 17
UNION ALL
SELECT ET.EMR_TRANSACTION_ID, ET.TASK_ID, ETL.TASK_NAME, '0' AS NODE_DEFINITION_ID, NVL(ET.ID_VALUE, '0') AS ID_VALUE, '0' AS FLOW_ID,
'1/1/1900' AS APPOINTMENT_DATE, '12:00:00' AS APPOINTMENT_TIME, '1/1/1900' AS CREATED_DATE, '0' AS APPOINTMENT_ID, '' AS FRONTDESK_COMMENTS,
'' AS APPOINTMENT_STATUS, EPM.PATIENT_FIRSTNAME, EPM.PATIENT_LASTNAME, EPM.PATIENT_DOB, EPM.PATIENT_HOMEPHONE,
EPM.PATIENT_ID, '' AS PATIENT_TYPE,'' AS PATIENT_GENDER,'' AS RACE_ETHNICITY_ID,'' AS MARITAL_STATUS, '' AS NAME, ET.USER_LOGIN AS CONSULTANT_ID, '' AS CONSULTANT_NAME, ET.ROLE_NAME,
EPM.LOCATION_ID AS LOCATION_ID, EPM.REGISTER_ID AS REGISTER_ID, NVL(IS_EXPRESS,0) AS IS_EXPRESS, 0 AS LAB_REQUEST_ID, 0 AS SPECIMEN_ID , 0 AS IS_ACTIVE
FROM EMRTransactions ET, EMRTaskListLkup ETL, EMRPatientsMaster EPM
WHERE ETL.TASK_ID = ET.TASK_ID AND ET.PATIENT_ID = EPM.PATIENT_ID AND ET.TASK_ID = 18
UNION ALL
SELECT ET.EMR_TRANSACTION_ID, ET.TASK_ID, ETL.TASK_NAME, '0' AS NODE_DEFINITION_ID, NVL(ET.ID_VALUE, '0') AS ID_VALUE, '0' AS FLOW_ID,
'1/1/1900' AS APPOINTMENT_DATE, '12:00:00' AS APPOINTMENT_TIME, '1/1/1900' AS CREATED_DATE, '0' AS APPOINTMENT_ID, '' AS FRONTDESK_COMMENTS,
'' AS APPOINTMENT_STATUS, EPM.PATIENT_FIRSTNAME, EPM.PATIENT_LASTNAME, EPM.PATIENT_DOB, EPM.PATIENT_HOMEPHONE,
EPM.PATIENT_ID, '' AS PATIENT_TYPE,'' AS PATIENT_GENDER,'' AS RACE_ETHNICITY_ID,'' AS MARITAL_STATUS, '' AS NAME, ET.USER_LOGIN AS CONSULTANT_ID, '' AS CONSULTANT_NAME, ET.ROLE_NAME,
EPM.LOCATION_ID AS LOCATION_ID, EPM.REGISTER_ID AS REGISTER_ID, NVL(IS_EXPRESS,0) AS IS_EXPRESS, 0 AS LAB_REQUEST_ID, 0 AS SPECIMEN_ID , 0 AS IS_ACTIVE
FROM EMRTransactions ET, EMRTaskListLkup ETL, EMRPatientsMaster EPM
WHERE ETL.TASK_ID = ET.TASK_ID AND ET.PATIENT_ID = EPM.PATIENT_ID AND ET.TASK_ID = 32
UNION ALL
SELECT ET.EMR_TRANSACTION_ID, ET.TASK_ID, ETL.TASK_NAME, '0' AS NODE_DEFINITION_ID, NVL(ET.ID_VALUE, '0') AS ID_VALUE, '0' AS FLOW_ID,
'1/1/1900' AS APPOINTMENT_DATE, '12:00:00' AS APPOINTMENT_TIME, '1/1/1900' AS CREATED_DATE, '0' AS APPOINTMENT_ID, '' AS FRONTDESK_COMMENTS,
'' AS APPOINTMENT_STATUS, EPM.PATIENT_FIRSTNAME, EPM.PATIENT_LASTNAME, EPM.PATIENT_DOB, EPM.PATIENT_HOMEPHONE,
EPM.PATIENT_ID, '' AS PATIENT_TYPE,'' AS PATIENT_GENDER,'' AS RACE_ETHNICITY_ID,'' AS MARITAL_STATUS, '' AS NAME, ET.USER_LOGIN AS CONSULTANT_ID, '' AS CONSULTANT_NAME, ET.ROLE_NAME AS ROLE_NAME, EPM.LOCATION_ID, EPM.REGISTER_ID AS REGISTER_ID, NVL(IS_EXPRESS,0) AS IS_EXPRESS, 0 AS LAB_REQUEST_ID, 0 AS SPECIMEN_ID , 0 AS IS_ACTIVE
FROM EMRTransactions ET, EMRTaskListLkup ETL, EMRPatientsMaster EPM
WHERE ETL.TASK_ID = ET.TASK_ID AND ET.PATIENT_ID = EPM.PATIENT_ID AND ET.TASK_ID IN (4, 19, 20, 21)
UNION ALL
SELECT ET.EMR_TRANSACTION_ID, ET.TASK_ID, ETL.TASK_NAME, '0' AS NODE_DEFINITION_ID, NVL(ET.ID_VALUE, '0') AS ID_VALUE, '0' AS FLOW_ID,
'1/1/1900' AS APPOINTMENT_DATE, '12:00:00' AS APPOINTMENT_TIME, '1/1/1900' AS CREATED_DATE, '0' AS APPOINTMENT_ID, '' AS FRONTDESK_COMMENTS,
'' AS APPOINTMENT_STATUS, EPM.PATIENT_FIRSTNAME, EPM.PATIENT_LASTNAME, EPM.PATIENT_DOB, EPM.PATIENT_HOMEPHONE,
EPM.PATIENT_ID, '' AS PATIENT_TYPE,'' AS PATIENT_GENDER,'' AS RACE_ETHNICITY_ID,'' AS MARITAL_STATUS, '' AS NAME, ET.USER_LOGIN AS CONSULTANT_ID, '' AS CONSULTANT_NAME, ET.ROLE_NAME,
EPM.LOCATION_ID AS LOCATION_ID, EPM.REGISTER_ID AS REGISTER_ID, NVL(IS_EXPRESS,0) AS IS_EXPRESS, 0 AS LAB_REQUEST_ID, 0 AS SPECIMEN_ID , 0 AS IS_ACTIVE
FROM EMRTransactions ET, EMRTaskListLkup ETL, EMRPatientsMaster EPM
WHERE ETL.TASK_ID = ET.TASK_ID AND ET.PATIENT_ID = EPM.PATIENT_ID AND ET.TASK_ID = 22
UNION ALL
SELECT ET.EMR_TRANSACTION_ID, ET.TASK_ID, ETL.TASK_NAME, '0' AS NODE_DEFINITION_ID, NVL(ET.ID_VALUE, '0') AS ID_VALUE, '0' AS FLOW_ID,
'1/1/1900' AS APPOINTMENT_DATE, '12:00:00' AS APPOINTMENT_TIME, '1/1/1900' AS CREATED_DATE, '0' AS APPOINTMENT_ID, '' AS FRONTDESK_COMMENTS,
'' AS APPOINTMENT_STATUS, EPM.PATIENT_FIRSTNAME, EPM.PATIENT_LASTNAME, EPM.PATIENT_DOB, EPM.PATIENT_HOMEPHONE,
EPM.PATIENT_ID, '' AS PATIENT_TYPE,'' AS PATIENT_GENDER,'' AS RACE_ETHNICITY_ID,'' AS MARITAL_STATUS, '' AS NAME, ET.USER_LOGIN AS CONSULTANT_ID, '' AS CONSULTANT_NAME, ET.ROLE_NAME,
EPM.LOCATION_ID AS LOCATION_ID, EPM.REGISTER_ID AS REGISTER_ID, NVL(IS_EXPRESS,0) AS IS_EXPRESS, 0 AS LAB_REQUEST_ID, 0 AS SPECIMEN_ID , 0 AS IS_ACTIVE
FROM EMRTransactions ET, EMRTaskListLkup ETL, EMRPatientsMaster EPM
WHERE ETL.TASK_ID = ET.TASK_ID AND ET.PATIENT_ID = EPM.PATIENT_ID AND ET.TASK_ID IN (23,105,106)
UNION ALL
SELECT ET.EMR_TRANSACTION_ID, ET.TASK_ID, ETL.TASK_NAME, '0' AS NODE_DEFINITION_ID, NVL(ET.ID_VALUE, '0') AS ID_VALUE, '0' AS FLOW_ID,
'1/1/1900' AS APPOINTMENT_DATE, '12:00:00' AS APPOINTMENT_TIME, '1/1/1900' AS CREATED_DATE, '0' AS APPOINTMENT_ID, '' AS FRONTDESK_COMMENTS,
'' AS APPOINTMENT_STATUS, EPM.PATIENT_FIRSTNAME, EPM.PATIENT_LASTNAME, EPM.PATIENT_DOB, EPM.PATIENT_HOMEPHONE,
EPM.PATIENT_ID, '' AS PATIENT_TYPE,'' AS PATIENT_GENDER,'' AS RACE_ETHNICITY_ID,'' AS MARITAL_STATUS, '' AS NAME, ET.USER_LOGIN AS CONSULTANT_ID, '' AS CONSULTANT_NAME, ET.ROLE_NAME,
EPM.LOCATION_ID AS LOCATION_ID, EPM.REGISTER_ID AS REGISTER_ID, NVL(IS_EXPRESS,0) AS IS_EXPRESS, 0 AS LAB_REQUEST_ID, 0 AS SPECIMEN_ID , 0 AS IS_ACTIVE
FROM EMRTransactions ET, EMRTaskListLkup ETL, EMRPatientsMaster EPM
WHERE ETL.TASK_ID = ET.TASK_ID AND ET.PATIENT_ID = EPM.PATIENT_ID AND ET.TASK_ID = 24
UNION ALL
SELECT ET.EMR_TRANSACTION_ID, ET.TASK_ID, 'ENCOUNTER' AS TASK_NAME, '0' AS NODE_DEFINITION_ID, NVL(ET.ID_VALUE, '0') AS ID_VALUE, '0' AS FLOW_ID,
TO_CHAR(EA.APPOINTMENT_DATE,'DD/MM/YYYY') AS APPOINTMENT_DATE, TO_CHAR(EA.APPOINTMENT_TIME,'HH:MI:SS AM') AS APPOINTMENT_TIME,
TO_CHAR(EA.CREATED_DATE,'DD/MM/YYYY') AS CREATED_DATE, TO_CHAR(EA.APPOINTMENT_ID) AS APPOINTMENT_ID,EA.FRONTDESK_COMMENTS,
'' AS APPOINTMENT_STATUS, EPM.PATIENT_FIRSTNAME, EPM.PATIENT_LASTNAME, EPM.PATIENT_DOB, EPM.PATIENT_HOMEPHONE,
EPM.PATIENT_ID, EPM.PATIENT_TYPE,EPM.PATIENT_GENDER,EPM.RACE_ETHNICITY_ID,EPM.MARITAL_STATUS, '' AS NAME, ET.USER_LOGIN AS CONSULTANT_ID,ECD.CONSULTANT_NAME, ET.ROLE_NAME,
EPM.LOCATION_ID AS LOCATION_ID, EPM.REGISTER_ID AS REGISTER_ID, NVL(IS_EXPRESS,0) AS IS_EXPRESS, 0 AS LAB_REQUEST_ID, 0 AS SPECIMEN_ID , 0 AS IS_ACTIVE
FROM EMREncounterDetails EED
LEFT JOIN EMRAppointmentDetailsHistory EA ON EA.APPOINTMENT_ID = EED.APPOINTMENT_ID AND EA.APPOINTMENT_STATUS_ID = 2
LEFT JOIN EMRConsultantDetails ECD ON ECD.CONSULTANT_ID = EED.CONSULTANT_ID
LEFT JOIN EMRTransactions ET ON ET.ID_VALUE = EED.ENCOUNTER_ID
LEFT JOIN EMRPatientsMaster EPM ON EED.PATIENT_ID = EPM.PATIENT_ID
WHERE ET.TASK_ID = 25
UNION ALL
SELECT ET.EMR_TRANSACTION_ID, ET.TASK_ID,'DM ENCOUNTER' AS TASK_NAME, TO_CHAR(ET.NODE_DEFINITION_ID) AS NODE_DEFINITION_ID, ET.ID_VALUE, TO_CHAR(ET.FLOW_ID) AS FLOW_ID,
TO_CHAR(EA.APPOINTMENT_DATE,'DD/MM/YYYY') AS APPOINTMENT_DATE, TO_CHAR(EA.APPOINTMENT_TIME,'HH:MI:SS AM') AS APPOINTMENT_TIME,
TO_CHAR(EA.CREATED_DATE,'DD/MM/YYYY') AS CREATED_DATE, TO_CHAR(EA.APPOINTMENT_ID) AS APPOINTMENT_ID,EA.REASON || ' ' || EA.FRONTDESK_COMMENTS AS FRONTDESK_COMMENTS, EAL.APPOINTMENT_STATUS,
EPM.PATIENT_FIRSTNAME, EPM.PATIENT_LASTNAME, EPM.PATIENT_DOB, EPM.PATIENT_HOMEPHONE, EPM.PATIENT_ID, EPM.PATIENT_TYPE,EPM.PATIENT_GENDER,EPM.RACE_ETHNICITY_ID,EPM.MARITAL_STATUS,
'' AS NAME, ET.USER_LOGIN AS CONSULTANT_ID, ECD.CONSULTANT_NAME, ET.ROLE_NAME, EA.APPOINTMENT_LOCATION_ID AS LOCATION_ID, EPM.REGISTER_ID AS REGISTER_ID, NVL(IS_EXPRESS,0) AS IS_EXPRESS, 0 AS LAB_REQUEST_ID, 0 AS SPECIMEN_ID, 1 AS IS_ACTIVE
FROM EMRAppointmentDetailsHistory EA
LEFT JOIN EMRTransactions ET ON ET.ID_VALUE = EA.APPOINTMENT_ID
LEFT JOIN EMRPatientsMaster EPM ON EA.PATIENT_ID = EPM.PATIENT_ID
LEFT JOIN EMRAppointmentStatusLkup EAL ON EAL.APPOINTMENT_STATUS_ID = EA.APPOINTMENT_ID
LEFT JOIN EMRConsultantDetails ECD ON ECD.CONSULTANT_ID = EA.CONSULTANT_ID
WHERE ET.TASK_ID = 26 AND EA.APPOINTMENT_STATUS_ID = 2
AND EXISTS (SELECT EMR_TRANSACTION_ID FROM EMRTRANSACTIONS ET1
WHERE ET1.TASK_ID=108 AND ET1.PATIENT_ID = ET.PATIENT_ID AND ET.ID_VALUE = ET1.ID_VALUE)
UNION ALL
SELECT ET.EMR_TRANSACTION_ID, ET.TASK_ID,'DM ENCOUNTER' AS TASK_NAME, TO_CHAR(ET.NODE_DEFINITION_ID) AS NODE_DEFINITION_ID, ET.ID_VALUE, TO_CHAR(ET.FLOW_ID) AS FLOW_ID,
TO_CHAR(EA.APPOINTMENT_DATE,'DD/MM/YYYY') AS APPOINTMENT_DATE, TO_CHAR(EA.APPOINTMENT_TIME,'HH:MI:SS AM') AS APPOINTMENT_TIME,
TO_CHAR(EA.CREATED_DATE,'DD/MM/YYYY') AS CREATED_DATE, TO_CHAR(EA.APPOINTMENT_ID) AS APPOINTMENT_ID,EA.REASON || ' ' || EA.FRONTDESK_COMMENTS AS FRONTDESK_COMMENTS, EAL.APPOINTMENT_STATUS,
EPM.PATIENT_FIRSTNAME, EPM.PATIENT_LASTNAME, EPM.PATIENT_DOB, EPM.PATIENT_HOMEPHONE, EPM.PATIENT_ID, EPM.PATIENT_TYPE,EPM.PATIENT_GENDER,EPM.RACE_ETHNICITY_ID,EPM.MARITAL_STATUS,
'' AS NAME, ET.USER_LOGIN AS CONSULTANT_ID, ECD.CONSULTANT_NAME, ET.ROLE_NAME, EA.APPOINTMENT_LOCATION_ID AS LOCATION_ID, EPM.REGISTER_ID AS REGISTER_ID, NVL(IS_EXPRESS,0) AS IS_EXPRESS, 0 AS LAB_REQUEST_ID, 0 AS SPECIMEN_ID, 0 AS IS_ACTIVE
FROM EMRAppointmentDetailsHistory EA
LEFT JOIN EMRTransactions ET ON ET.ID_VALUE = EA.APPOINTMENT_ID
LEFT JOIN EMRPatientsMaster EPM ON EA.PATIENT_ID = EPM.PATIENT_ID
LEFT JOIN EMRAppointmentStatusLkup EAL ON EAL.APPOINTMENT_STATUS_ID = EA.APPOINTMENT_ID
LEFT JOIN EMRConsultantDetails ECD ON ECD.CONSULTANT_ID = EA.CONSULTANT_ID
WHERE ET.TASK_ID = 26 AND EA.APPOINTMENT_STATUS_ID = 2
AND NOT EXISTS (SELECT EMR_TRANSACTION_ID FROM EMRTRANSACTIONS ET1
WHERE ET1.TASK_ID=108 AND ET1.PATIENT_ID = ET.PATIENT_ID AND ET.ID_VALUE = ET1.ID_VALUE)
UNION ALL
SELECT ET.EMR_TRANSACTION_ID, ET.TASK_ID, ETL.TASK_NAME, '0' AS NODE_DEFINITION_ID, NVL(ET.ID_VALUE, '0') AS ID_VALUE, '0' AS FLOW_ID,
TO_CHAR(EA.APPOINTMENT_DATE,'DD/MM/YYYY') AS APPOINTMENT_DATE, TO_CHAR(EA.APPOINTMENT_TIME,'HH:MI:SS AM') AS APPOINTMENT_TIME,
TO_CHAR(EA.CREATED_DATE,'DD/MM/YYYY') AS CREATED_DATE, TO_CHAR(EA.APPOINTMENT_ID) AS APPOINTMENT_ID,EA.FRONTDESK_COMMENTS,
'' AS APPOINTMENT_STATUS, EPM.PATIENT_FIRSTNAME, EPM.PATIENT_LASTNAME, EPM.PATIENT_DOB, EPM.PATIENT_HOMEPHONE,
EPM.PATIENT_ID,EPM.PATIENT_TYPE,EPM.PATIENT_GENDER,EPM.RACE_ETHNICITY_ID,EPM.MARITAL_STATUS, '' AS NAME, ET.USER_LOGIN AS CONSULTANT_ID, ECD.CONSULTANT_NAME, ET.ROLE_NAME,
EPM.LOCATION_ID AS LOCATION_ID, EPM.REGISTER_ID AS REGISTER_ID, NVL(IS_EXPRESS,0) AS IS_EXPRESS, 0 AS LAB_REQUEST_ID, 0 AS SPECIMEN_ID , 0 AS IS_ACTIVE
FROM EMREncounterDetails EED LEFT JOIN
EMRAppointmentDetailsHistory EA ON EA.APPOINTMENT_ID = EED.APPOINTMENT_ID AND EA.APPOINTMENT_STATUS_ID = 2 LEFT JOIN
EMRConsultantDetails ECD ON ECD.CONSULTANT_ID = EED.CONSULTANT_ID LEFT JOIN
EMRTransactions ET ON ET.ID_VALUE = EED.ENCOUNTER_ID LEFT JOIN
EMRPatientsMaster EPM ON EED.PATIENT_ID = EPM.PATIENT_ID
LEFT JOIN EMRTASKLISTLKUP ETL ON ETL.TASK_ID = ET.TASK_ID
LEFT JOIN EMRAppointmentStatusLkup EAL ON EAL.APPOINTMENT_STATUS_ID = EA.APPOINTMENT_ID
WHERE ET.TASK_ID = 27
UNION ALL
SELECT ET.EMR_TRANSACTION_ID, ET.TASK_ID, ETL.TASK_NAME, '0' AS NODE_DEFINITION_ID, NVL(ET.ID_VALUE, '0') AS ID_VALUE, '0' AS FLOW_ID,
'1/1/1900' AS APPOINTMENT_DATE, '12:00:00' AS APPOINTMENT_TIME, '1/1/1900' AS CREATED_DATE, '0' AS APPOINTMENT_ID, '' AS FRONTDESK_COMMENTS,
'' AS APPOINTMENT_STATUS, EPM.PATIENT_FIRSTNAME, EPM.PATIENT_LASTNAME, EPM.PATIENT_DOB, EPM.PATIENT_HOMEPHONE,
EPM.PATIENT_ID, '' AS PATIENT_TYPE,'' AS PATIENT_GENDER,'' AS RACE_ETHNICITY_ID,'' AS MARITAL_STATUS, '' AS NAME, ET.USER_LOGIN AS CONSULTANT_ID, '' AS CONSULTANT_NAME, ET.ROLE_NAME,
EPM.LOCATION_ID AS LOCATION_ID, EPM.REGISTER_ID AS REGISTER_ID, NVL(IS_EXPRESS,0) AS IS_EXPRESS, 0 AS LAB_REQUEST_ID, 0 AS SPECIMEN_ID , 0 AS IS_ACTIVE
FROM EMRTransactions ET, EMRTaskListLkup ETL, EMRPatientsMaster EPM
WHERE ETL.TASK_ID = ET.TASK_ID AND ET.PATIENT_ID = EPM.PATIENT_ID AND ET.TASK_ID IN ( 29, 30)
UNION ALL
SELECT ET.EMR_TRANSACTION_ID, ET.TASK_ID, ETL.TASK_NAME, '0' AS NODE_DEFINITION_ID, NVL(ET.ID_VALUE, '0') AS ID_VALUE, '0' AS FLOW_ID,
'1/1/1900' AS APPOINTMENT_DATE, '12:00:00' AS APPOINTMENT_TIME, '1/1/1900' AS CREATED_DATE, '0' AS APPOINTMENT_ID, '' AS FRONTDESK_COMMENTS,
'' AS APPOINTMENT_STATUS, EPM.PATIENT_FIRSTNAME, EPM.PATIENT_LASTNAME, EPM.PATIENT_DOB, EPM.PATIENT_HOMEPHONE,
EPM.PATIENT_ID, '' AS PATIENT_TYPE,EPM.PATIENT_GENDER,'' AS RACE_ETHNICITY_ID,'' AS MARITAL_STATUS, '' AS NAME, ET.USER_LOGIN AS CONSULTANT_ID, '' AS CONSULTANT_NAME, ET.ROLE_NAME,
EPM.LOCATION_ID AS LOCATION_ID, EPM.REGISTER_ID AS REGISTER_ID, NVL(IS_EXPRESS,0) AS IS_EXPRESS, 0 AS LAB_REQUEST_ID, 0 AS SPECIMEN_ID , 0 AS IS_ACTIVE
FROM EMRTransactions ET, EMRTaskListLkup ETL, EMRPatientsMaster EPM
WHERE ETL.TASK_ID = ET.TASK_ID AND ET.PATIENT_ID = EPM.PATIENT_ID AND ET.TASK_ID = 33
UNION ALL
SELECT ET.EMR_TRANSACTION_ID, ET.TASK_ID, ETL.TASK_NAME, '0' AS NODE_DEFINITION_ID, NVL(ET.ID_VALUE, '0') AS ID_VALUE, '0' AS FLOW_ID,
'1/1/1900' AS APPOINTMENT_DATE, '12:00:00' AS APPOINTMENT_TIME, '1/1/1900' AS CREATED_DATE, '0' AS APPOINTMENT_ID, '' AS FRONTDESK_COMMENTS,
'' AS APPOINTMENT_STATUS, EPM.PATIENT_FIRSTNAME, EPM.PATIENT_LASTNAME, EPM.PATIENT_DOB, EPM.PATIENT_HOMEPHONE,
EPM.PATIENT_ID, '' AS PATIENT_TYPE,'' AS PATIENT_GENDER,'' AS RACE_ETHNICITY_ID,'' AS MARITAL_STATUS, '' AS NAME, ET.USER_LOGIN AS CONSULTANT_ID, '' AS CONSULTANT_NAME, ET.ROLE_NAME,
EPM.LOCATION_ID AS LOCATION_ID, EPM.REGISTER_ID AS REGISTER_ID, NVL(IS_EXPRESS,0) AS IS_EXPRESS, 0 AS LAB_REQUEST_ID, 0 AS SPECIMEN_ID , 0 AS IS_ACTIVE
FROM EMRTransactions ET, EMRTaskListLkup ETL, EMRPatientsMaster EPM
WHERE ETL.TASK_ID = ET.TASK_ID AND ET.PATIENT_ID = EPM.PATIENT_ID AND ET.TASK_ID =31
UNION ALL
SELECT ET.EMR_TRANSACTION_ID, ET.TASK_ID, ETL.TASK_NAME, '0' AS NODE_DEFINITION_ID, NVL(ET.ID_VALUE, '0') AS ID_VALUE, '0' AS FLOW_ID,
'1/1/1900' AS APPOINTMENT_DATE, '12:00:00' AS APPOINTMENT_TIME, '1/1/1900' AS CREATED_DATE, '0' AS APPOINTMENT_ID, '' AS FRONTDESK_COMMENTS,
'' AS APPOINTMENT_STATUS, '' AS PATIENT_FIRSTNAME, '' AS PATIENT_LASTNAME, null AS PATIENT_DOB, null AS PATIENT_HOMEPHONE,
null AS PATIENT_ID, '' AS PATIENT_TYPE,'' AS PATIENT_GENDER,'' AS RACE_ETHNICITY_ID,'' AS MARITAL_STATUS, '' AS NAME, ET.USER_LOGIN AS CONSULTANT_ID, '' AS CONSULTANT_NAME, ET.ROLE_NAME ,
null AS LOCATION_ID, '' AS REGISTER_ID, NVL(IS_EXPRESS,0) AS IS_EXPRESS, 0 AS LAB_REQUEST_ID, 0 AS SPECIMEN_ID , 0 AS IS_ACTIVE
FROM EMRTransactions ET, EMRTaskListLkup ETL, USERS US
WHERE ETL.TASK_ID = ET.TASK_ID AND ET.USER_LOGIN = US.USER_LOGIN AND ET.TASK_ID IN (34, 61, 62, 77, 80)
UNION ALL
SELECT ET.EMR_TRANSACTION_ID, ET.TASK_ID, ETL.TASK_NAME, '0' AS NODE_DEFINITION_ID,
NVL(ET.ID_VALUE, '0') AS ID_VALUE, '0' AS FLOW_ID, '1/1/1900' AS APPOINTMENT_DATE,
'12:00:00' AS APPOINTMENT_TIME, '1/1/1900' AS CREATED_DATE, '0' AS APPOINTMENT_ID,
'' AS FRONTDESK_COMMENTS, '' AS APPOINTMENT_STATUS, EPM.PATIENT_FIRSTNAME AS PATIENT_FIRSTNAME,
EPM.PATIENT_LASTNAME AS PATIENT_LASTNAME, EPM.PATIENT_DOB AS PATIENT_DOB,
EPM.PATIENT_HOMEPHONE AS PATIENT_HOMEPHONE, EPM.PATIENT_ID AS PATIENT_ID, '' AS PATIENT_TYPE,
'' AS PATIENT_GENDER,'' AS RACE_ETHNICITY_ID,'' AS MARITAL_STATUS, '' AS NAME,
ET.USER_LOGIN AS CONSULTANT_ID, '' AS CONSULTANT_NAME, ET.ROLE_NAME , EPM.LOCATION_ID AS LOCATION_ID, EPM.REGISTER_ID AS REGISTER_ID, NVL(IS_EXPRESS,0) AS IS_EXPRESS, 0 AS LAB_REQUEST_ID, 0 AS SPECIMEN_ID , 0 AS IS_ACTIVE
FROM EMRTransactions ET, EMRTaskListLkup ETL, EMRPatientsMaster EPM
WHERE ETL.TASK_ID = ET.TASK_ID AND ET.PATIENT_ID = EPM.PATIENT_ID AND ET.TASK_ID IN (59, 60, 63, 64,67,76,94)
UNION ALL
SELECT ET.EMR_TRANSACTION_ID, ET.TASK_ID, ETL.TASK_NAME, '0' AS NODE_DEFINITION_ID, ESF.SCREENING_DOC_ID AS ID_VALUE, '0' AS FLOW_ID,
'1/1/1900' AS APPOINTMENT_DATE, '12:00:00' AS APPOINTMENT_TIME, '1/1/1900' AS CREATED_DATE, '0' AS APPOINTMENT_ID,
'' AS FRONTDESK_COMMENTS, '' AS APPOINTMENT_STATUS, EPM.PATIENT_FIRSTNAME, EPM.PATIENT_LASTNAME, EPM.PATIENT_DOB,
EPM.PATIENT_HOMEPHONE, EPM.PATIENT_ID, '' AS PATIENT_TYPE, '' AS PATIENT_GENDER, '' AS RACE_ETHNICITY_ID, '' AS MARITAL_STATUS,
'' AS NAME, ESD.USER_LOGIN AS CONSULTANT_ID, '' AS CONSULTANT_NAME, ET.ROLE_NAME, EPM.LOCATION_ID AS LOCATION_ID, EPM.REGISTER_ID AS REGISTER_ID, NVL(IS_EXPRESS,0) AS IS_EXPRESS, 0 AS LAB_REQUEST_ID, 0 AS SPECIMEN_ID , 0 AS IS_ACTIVE
FROM EMRTRANSACTIONS ET, EMRTASKLISTLKUP ETL, EMRPATIENTSMASTER EPM, EMRPatientScreeningFiles ESF, EMRPatientScreeningDetails ESD
WHERE ETL.TASK_ID = ET.TASK_ID AND ET.PATIENT_ID = EPM.PATIENT_ID AND ET.PATIENT_ID = ESD.PATIENT_ID AND
ESD.USER_LOGIN = ET.USER_LOGIN AND ET.ID_VALUE = ESF.SCREENING_DOC_ID AND ESF.VERIFICATION_REQUIRED = 1 AND ESF.Screening_ID = ESD.Screening_ID AND ET.TASK_ID = 65
UNION ALL
SELECT ET.EMR_TRANSACTION_ID, ET.TASK_ID, 'FINAL DIAGNOSIS' AS TASK_NAME, '0' AS NODE_DEFINITION_ID, NVL(ET.ID_VALUE, '0') AS ID_VALUE, '0' AS FLOW_ID,
TO_CHAR(EA.APPOINTMENT_DATE,'DD/MM/YYYY') AS APPOINTMENT_DATE, TO_CHAR(EA.APPOINTMENT_TIME,'HH:MI:SS AM') AS APPOINTMENT_TIME,
TO_CHAR(EA.CREATED_DATE,'DD/MM/YYYY') AS CREATED_DATE, TO_CHAR(EA.APPOINTMENT_ID) AS APPOINTMENT_ID,EA.REASON || ' ' || EA.FRONTDESK_COMMENTS AS FRONTDESK_COMMENTS,
'' AS APPOINTMENT_STATUS, EPM.PATIENT_FIRSTNAME, EPM.PATIENT_LASTNAME, EPM.PATIENT_DOB, EPM.PATIENT_HOMEPHONE,
EPM.PATIENT_ID, EPM.PATIENT_TYPE,EPM.PATIENT_GENDER,EPM.RACE_ETHNICITY_ID,EPM.MARITAL_STATUS, '' AS NAME, ET.USER_LOGIN AS CONSULTANT_ID,ECD.CONSULTANT_NAME, ET.ROLE_NAME,
EPM.LOCATION_ID AS LOCATION_ID, EPM.REGISTER_ID AS REGISTER_ID, NVL(IS_EXPRESS,0) AS IS_EXPRESS, 0 AS LAB_REQUEST_ID, 0 AS SPECIMEN_ID , 0 AS IS_ACTIVE
FROM EMREncounterDetails EED
LEFT JOIN EMRAppointmentDetailsHistory EA ON EA.APPOINTMENT_ID = EED.APPOINTMENT_ID AND EA.APPOINTMENT_STATUS_ID = 2
LEFT JOIN EMRConsultantDetails ECD ON ECD.CONSULTANT_ID = EED.CONSULTANT_ID
LEFT JOIN EMRTransactions ET ON ET.ID_VALUE = EED.ENCOUNTER_ID
LEFT JOIN EMRPatientsMaster EPM ON EED.PATIENT_ID = EPM.PATIENT_ID
WHERE ET.TASK_ID = 68
UNION ALL
SELECT EMR_TRANSACTION_ID, ETL.TASK_ID, TASK_NAME, '0' AS NODE_DEFINITION_ID, 0 AS ID_VALUE, '0' AS FLOW_ID,
'1/1/1900' AS APPOINTMENT_DATE, '12:00:00' AS APPOINTMENT_TIME, '1/1/1900' AS CREATED_DATE,
'0' AS APPOINTMENT_ID, '' AS FRONTDESK_COMMENTS, '' AS APPOINTMENT_STATUS, EPM.PATIENT_FIRSTNAME, EPM.PATIENT_LASTNAME, EPM.PATIENT_DOB,EPM.PATIENT_HOMEPHONE,
EPM.PATIENT_ID, '' AS PATIENT_TYPE, '' AS PATIENT_GENDER, '' AS RACE_ETHNICITY_ID, '' AS MARITAL_STATUS, '' AS NAME,
'' AS CONSULTANT_ID, '' AS CONSULTANT_NAME, ET.ROLE_NAME, EPM.LOCATION_ID AS LOCATION_ID, EPM.REGISTER_ID AS REGISTER_ID,
NVL(IS_EXPRESS,0) AS IS_EXPRESS, 0 AS LAB_REQUEST_ID, 0 AS SPECIMEN_ID , 0 AS IS_ACTIVE
FROM EMRTASKLISTLKUP ETL, EMRPATIENTSMASTER EPM ,EMRTRANSACTIONS ET,EMRPatientTaskInfo PTI
WHERE ETL.TASK_ID = ET.TASK_ID AND ET.PATIENT_ID = EPM.PATIENT_ID AND ET.TASK_ID IN (69)
AND ET.PATIENT_ID = PTI.PATIENT_ID AND PTI.TASK_ID IN (69) AND PTI.TASK_COUNT>1
UNION ALL
SELECT EMR_TRANSACTION_ID, ETL.TASK_ID, TASK_NAME, '0' AS NODE_DEFINITION_ID, 0 AS ID_VALUE, '0' AS FLOW_ID,
TO_CHAR(EAD.APPOINTMENT_DATE,'DD/MM/YYYY') AS APPOINTMENT_DATE, TO_CHAR(EAD.APPOINTMENT_TIME,'HH:MI:SS AM') AS APPOINTMENT_TIME,
TO_CHAR(EAD.CREATED_DATE,'DD/MM/YYYY') AS CREATED_DATE, TO_CHAR(EAD.APPOINTMENT_ID) AS APPOINTMENT_ID, EAD.FRONTDESK_COMMENTS,
'' AS APPOINTMENT_STATUS, EPM.PATIENT_FIRSTNAME, EPM.PATIENT_LASTNAME, EPM.PATIENT_DOB,EPM.PATIENT_HOMEPHONE,
EPM.PATIENT_ID, '' AS PATIENT_TYPE, '' AS PATIENT_GENDER, '' AS RACE_ETHNICITY_ID, '' AS MARITAL_STATUS, '' AS NAME,
EAD.CONSULTANT_ID, ECD.CONSULTANT_NAME, ET.ROLE_NAME, EPM.LOCATION_ID AS LOCATION_ID, EPM.REGISTER_ID AS REGISTER_ID,
NVL(IS_EXPRESS,0) AS IS_EXPRESS, 0 AS LAB_REQUEST_ID, 0 AS SPECIMEN_ID , 0 AS IS_ACTIVE
FROM EMRTASKLISTLKUP ETL, EMRPATIENTSMASTER EPM ,EMRTRANSACTIONS ET,EMRPatientTaskInfo PTI, EMRAPPOINTMENTDETAILSHISTORY EAD ,EMRCONSULTANTDETAILS ECD
WHERE ETL.TASK_ID = ET.TASK_ID AND ET.PATIENT_ID = EPM.PATIENT_ID AND ET.TASK_ID IN (69)
AND ET.PATIENT_ID = PTI.PATIENT_ID AND PTI.TASK_ID IN (69) AND PTI.TASK_COUNT=1
AND EAD.APPOINTMENT_ID=ET.ID_VALUE AND EAD.APPOINTMENT_STATUS_ID=2 AND ECD.CONSULTANT_ID = EAD.CONSULTANT_ID
UNION ALL
SELECT EMR_TRANSACTION_ID, ETL.TASK_ID, TASK_NAME, '0' AS NODE_DEFINITION_ID, 0 AS ID_VALUE, '0' AS FLOW_ID,
'1/1/1900' AS APPOINTMENT_DATE, '12:00:00' AS APPOINTMENT_TIME, '1/1/1900' AS CREATED_DATE,
'0' AS APPOINTMENT_ID, '' AS FRONTDESK_COMMENTS, '' AS APPOINTMENT_STATUS, EPM.PATIENT_FIRSTNAME, EPM.PATIENT_LASTNAME, EPM.PATIENT_DOB,EPM.PATIENT_HOMEPHONE,
EPM.PATIENT_ID, '' AS PATIENT_TYPE, '' AS PATIENT_GENDER, '' AS RACE_ETHNICITY_ID, '' AS MARITAL_STATUS, '' AS NAME,
'' AS CONSULTANT_ID, '' AS CONSULTANT_NAME, ET.ROLE_NAME, EPM.LOCATION_ID AS LOCATION_ID, EPM.REGISTER_ID AS REGISTER_ID,
NVL(IS_EXPRESS,0) AS IS_EXPRESS, 0 AS LAB_REQUEST_ID, 0 AS SPECIMEN_ID , 0 AS IS_ACTIVE
FROM EMRTASKLISTLKUP ETL, EMRPATIENTSMASTER EPM ,EMRTRANSACTIONS ET,EMRPatientTaskInfo PTI
WHERE ETL.TASK_ID = ET.TASK_ID AND ET.PATIENT_ID = EPM.PATIENT_ID AND ET.TASK_ID IN (70)
AND ET.PATIENT_ID = PTI.PATIENT_ID AND PTI.TASK_ID IN (70) AND PTI.TASK_COUNT>1
UNION ALL
SELECT EMR_TRANSACTION_ID, ETL.TASK_ID, TASK_NAME, '0' AS NODE_DEFINITION_ID, 0 AS ID_VALUE, '0' AS FLOW_ID,
TO_CHAR(EAD.APPOINTMENT_DATE,'DD/MM/YYYY') AS APPOINTMENT_DATE, TO_CHAR(EAD.APPOINTMENT_TIME,'HH:MI:SS AM') AS APPOINTMENT_TIME,
TO_CHAR(EAD.CREATED_DATE,'DD/MM/YYYY') AS CREATED_DATE, TO_CHAR(EAD.APPOINTMENT_ID) AS APPOINTMENT_ID, EAD.FRONTDESK_COMMENTS,
'' AS APPOINTMENT_STATUS, EPM.PATIENT_FIRSTNAME, EPM.PATIENT_LASTNAME, EPM.PATIENT_DOB,EPM.PATIENT_HOMEPHONE,
EPM.PATIENT_ID, '' AS PATIENT_TYPE, '' AS PATIENT_GENDER, '' AS RACE_ETHNICITY_ID, '' AS MARITAL_STATUS, '' AS NAME,
EAD.CONSULTANT_ID, ECD.CONSULTANT_NAME, ET.ROLE_NAME, EPM.LOCATION_ID AS LOCATION_ID, EPM.REGISTER_ID AS REGISTER_ID,
NVL(IS_EXPRESS,0) AS IS_EXPRESS, 0 AS LAB_REQUEST_ID, 0 AS SPECIMEN_ID , 0 AS IS_ACTIVE
FROM EMRTASKLISTLKUP ETL, EMRPATIENTSMASTER EPM ,EMRTRANSACTIONS ET,EMRPatientTaskInfo PTI, EMRAPPOINTMENTDETAILSHISTORY EAD ,EMRCONSULTANTDETAILS ECD
WHERE ETL.TASK_ID = ET.TASK_ID AND ET.PATIENT_ID = EPM.PATIENT_ID AND ET.TASK_ID IN (70)
AND ET.PATIENT_ID = PTI.PATIENT_ID AND PTI.TASK_ID IN (70) AND PTI.TASK_COUNT=1
AND EAD.APPOINTMENT_ID=ET.ID_VALUE AND EAD.APPOINTMENT_STATUS_ID=2 AND ECD.CONSULTANT_ID = EAD.CONSULTANT_ID
UNION ALL
SELECT DISTINCT ET.EMR_TRANSACTION_ID, ET.TASK_ID, ETL.TASK_NAME, '0' AS NODE_DEFINITION_ID, NVL(ET.ID_VALUE, '0') AS ID_VALUE, '0' AS FLOW_ID,
'1/1/1900' AS APPOINTMENT_DATE, '12:00:00' AS APPOINTMENT_TIME, '1/1/1900' AS CREATED_DATE, '0' AS APPOINTMENT_ID, '' AS FRONTDESK_COMMENTS,
'' AS APPOINTMENT_STATUS, '' AS PATIENT_FIRSTNAME, '' AS PATIENT_LASTNAME, null AS PATIENT_DOB, null AS PATIENT_HOMEPHONE,
null AS PATIENT_ID, '' AS PATIENT_TYPE,'' AS PATIENT_GENDER,'' AS RACE_ETHNICITY_ID,'' AS MARITAL_STATUS, '' AS NAME, ET.USER_LOGIN AS CONSULTANT_ID, '' AS CONSULTANT_NAME, ET.ROLE_NAME ,
ECT.LOCATION AS LOCATION_ID, '' AS REGISTER_ID, NVL(IS_EXPRESS,0) AS IS_EXPRESS, 0 AS LAB_REQUEST_ID, 0 AS SPECIMEN_ID , 0 AS IS_ACTIVE
FROM EMRTransactions ET, EMRTaskListLkup ETL, USERS US, EMRConsultantTemp ECT
WHERE ETL.TASK_ID = ET.TASK_ID AND ET.USER_LOGIN = US.USER_LOGIN
AND ECT.GROUP_NAME=US.USER_LOGIN AND ET.TASK_ID IN (74)
UNION ALL
SELECT DISTINCT ET.EMR_TRANSACTION_ID, ET.TASK_ID, ETL.TASK_NAME, '0' AS NODE_DEFINITION_ID,
NVL(ET.ID_VALUE, '0') AS ID_VALUE, '0' AS FLOW_ID, '1/1/1900' AS APPOINTMENT_DATE,
'12:00:00' AS APPOINTMENT_TIME, '1/1/1900' AS CREATED_DATE, '0' AS APPOINTMENT_ID,
ELEL.EQUIPMENT_NAME AS FRONTDESK_COMMENTS, '' AS APPOINTMENT_STATUS, EPM.PATIENT_FIRSTNAME AS PATIENT_FIRSTNAME,
EPM.PATIENT_LASTNAME AS PATIENT_LASTNAME, EPM.PATIENT_DOB AS PATIENT_DOB,
EPM.PATIENT_HOMEPHONE AS PATIENT_HOMEPHONE, EPM.PATIENT_ID AS PATIENT_ID, '' AS PATIENT_TYPE,
'' AS PATIENT_GENDER,'' AS RACE_ETHNICITY_ID,'' AS MARITAL_STATUS, '' AS NAME,
ET.USER_LOGIN AS CONSULTANT_ID, '' AS CONSULTANT_NAME, ET.ROLE_NAME , EPM.LOCATION_ID AS LOCATION_ID, EPM.REGISTER_ID AS REGISTER_ID, NVL(IS_EXPRESS,0) AS IS_EXPRESS, ELRM.LAB_REQUEST_ID, ELRM.SPECIMEN_ID, 0 AS IS_ACTIVE
FROM EMRSupplementaryTransactions ET, EMRTaskListLkup ETL, EMRPatientsMaster EPM, EMRLabRequest ELRM
INNER JOIN EMRLabRequestDetails ELRD ON ELRM.LAB_REQUEST_ID=ELRD.LAB_REQUEST_ID
LEFT OUTER JOIN EMRTestEquipments ETE ON (ETE.TEST_ID=ELRD.TEST_ID OR ETE.LAB_PROFILE_ID=ELRD.LAB_PROFILE_ID) LEFT OUTER JOIN
EMRLabEquipmentsLkup ELEL ON ELEL.EQUIPMENT_ID=ETE.EQUIPMENT_ID
WHERE ETL.TASK_ID = ET.TASK_ID AND ET.PATIENT_ID = EPM.PATIENT_ID AND ET.ID_VALUE=ELRM.LAB_REQUEST_ID AND ET.TASK_ID in(72,81,82,83,87,88,97,98)
UNION ALL
SELECT DISTINCT ET.EMR_TRANSACTION_ID, ET.TASK_ID, ETL.TASK_NAME, '0' AS NODE_DEFINITION_ID,
NVL(ET.ID_VALUE, '0') AS ID_VALUE, '0' AS FLOW_ID, '1/1/1900' AS APPOINTMENT_DATE,
'12:00:00' AS APPOINTMENT_TIME, '1/1/1900' AS CREATED_DATE, '0' AS APPOINTMENT_ID,
ELEL.EQUIPMENT_NAME AS FRONTDESK_COMMENTS, '' AS APPOINTMENT_STATUS, EPM.PATIENT_FIRSTNAME AS PATIENT_FIRSTNAME,
EPM.PATIENT_LASTNAME AS PATIENT_LASTNAME, EPM.PATIENT_DOB AS PATIENT_DOB,
EPM.PATIENT_HOMEPHONE AS PATIENT_HOMEPHONE, EPM.PATIENT_ID AS PATIENT_ID, '' AS PATIENT_TYPE,
'' AS PATIENT_GENDER,'' AS RACE_ETHNICITY_ID,'' AS MARITAL_STATUS, '' AS NAME,
ET.USER_LOGIN AS CONSULTANT_ID, '' AS CONSULTANT_NAME, ET.ROLE_NAME , EPM.LOCATION_ID AS LOCATION_ID, EPM.REGISTER_ID AS REGISTER_ID, NVL(IS_EXPRESS,0) AS IS_EXPRESS, LR.LAB_REQUEST_ID, LR.SPECIMEN_ID, 0 AS IS_ACTIVE
FROM EMRSupplementaryTransactions ET, EMRTaskListLkup ETL, EMRPatientsMaster EPM, EMRLabTransactions ELTD INNER JOIN EMRLabEquipmentsLkup ELEL ON ELEL.EQUIPMENT_ID=ELTD.EQUIPMENT_ID
INNER JOIN EMRLabRequestDetails LRD ON ELTD.LAB_REQUEST_DETAILS_ID = LRD.LAB_REQUEST_DETAILS_ID
INNER JOIN EMRLabRequest LR ON LRD.LAB_REQUEST_ID = LR.LAB_REQUEST_ID
WHERE ETL.TASK_ID = ET.TASK_ID AND ET.PATIENT_ID = EPM.PATIENT_ID AND ET.ID_VALUE = ELTD.LAB_TRANSACTION_ID
AND ET.TASK_ID in(84,85,86,89,90,99,100)
UNION ALL
SELECT ET.EMR_TRANSACTION_ID, ET.TASK_ID, ETL.TASK_NAME, '0' AS NODE_DEFINITION_ID, NVL(ET.ID_VALUE, '0') AS ID_VALUE, '0' AS FLOW_ID,
'1/1/1900' AS APPOINTMENT_DATE, '12:00:00' AS APPOINTMENT_TIME, '1/1/1900' AS CREATED_DATE, '0' AS APPOINTMENT_ID, '' AS FRONTDESK_COMMENTS,
'' AS APPOINTMENT_STATUS, EPM.PATIENT_FIRSTNAME, EPM.PATIENT_LASTNAME, EPM.PATIENT_DOB, EPM.PATIENT_HOMEPHONE,
EPM.PATIENT_ID, '' AS PATIENT_TYPE,'' AS PATIENT_GENDER,'' AS RACE_ETHNICITY_ID,'' AS MARITAL_STATUS, '' AS NAME, ET.USER_LOGIN AS CONSULTANT_ID, '' AS CONSULTANT_NAME, ET.ROLE_NAME AS ROLE_NAME, EPM.LOCATION_ID, EPM.REGISTER_ID AS REGISTER_ID, NVL(IS_EXPRESS,0) AS IS_EXPRESS, 0 AS LAB_REQUEST_ID, 0 AS SPECIMEN_ID, 0 AS IS_ACTIVE
FROM EMRTransactions ET, EMRTaskListLkup ETL, EMRPatientsMaster EPM
WHERE ETL.TASK_ID = ET.TASK_ID AND ET.PATIENT_ID = EPM.PATIENT_ID AND ET.TASK_ID IN (79, 91,102,103, 104)
UNION ALL
SELECT ET.EMR_TRANSACTION_ID, ET.TASK_ID, ETL.TASK_NAME, '0' AS NODE_DEFINITION_ID, NVL(ET.ID_VALUE, '0') AS ID_VALUE, '0' AS FLOW_ID,
'1/1/1900' AS APPOINTMENT_DATE, '12:00:00' AS APPOINTMENT_TIME, '1/1/1900' AS CREATED_DATE, '0' AS APPOINTMENT_ID, '' AS FRONTDESK_COMMENTS,
'' AS APPOINTMENT_STATUS, EPM.PATIENT_FIRSTNAME, EPM.PATIENT_LASTNAME, EPM.PATIENT_DOB, EPM.PATIENT_HOMEPHONE,
EPM.PATIENT_ID, '' AS PATIENT_TYPE,'' AS PATIENT_GENDER,'' AS RACE_ETHNICITY_ID,'' AS MARITAL_STATUS, '' AS NAME, ET.USER_LOGIN AS CONSULTANT_ID, '' AS CONSULTANT_NAME, ET.ROLE_NAME AS ROLE_NAME, EPM.LOCATION_ID, EPM.REGISTER_ID AS REGISTER_ID, NVL(IS_EXPRESS,0) AS IS_EXPRESS, 0 AS LAB_REQUEST_ID, 0 AS SPECIMEN_ID, 0 AS IS_ACTIVE
FROM EMRTransactions ET, EMRTaskListLkup ETL, EMRPatientsMaster EPM
WHERE ETL.TASK_ID = ET.TASK_ID AND ET.PATIENT_ID = EPM.PATIENT_ID AND ET.TASK_ID IN (73,92,93,114,127)
UNION ALL
SELECT ET.EMR_TRANSACTION_ID, ET.TASK_ID,ETL.TASK_NAME, TO_CHAR(ET.NODE_DEFINITION_ID) AS NODE_DEFINITION_ID, ET.ID_VALUE, TO_CHAR(ET.FLOW_ID) AS FLOW_ID,
TO_CHAR(EA.APPOINTMENT_DATE,'DD/MM/YYYY') AS APPOINTMENT_DATE, TO_CHAR(EA.APPOINTMENT_TIME,'HH:MI:SS AM') AS APPOINTMENT_TIME,
TO_CHAR(EA.CREATED_DATE,'DD/MM/YYYY') AS CREATED_DATE, TO_CHAR(EA.APPOINTMENT_ID) AS APPOINTMENT_ID, EA.FRONTDESK_COMMENTS AS FRONTDESK_COMMENTS, EAL.APPOINTMENT_STATUS,
EPM.PATIENT_FIRSTNAME, EPM.PATIENT_LASTNAME, EPM.PATIENT_DOB, EPM.PATIENT_HOMEPHONE, EPM.PATIENT_ID, EPM.PATIENT_TYPE,EPM.PATIENT_GENDER,EPM.RACE_ETHNICITY_ID,EPM.MARITAL_STATUS,
'' AS NAME, ET.USER_LOGIN AS CONSULTANT_ID, ECD.CONSULTANT_NAME, ET.ROLE_NAME, EA.APPOINTMENT_LOCATION_ID AS LOCATION_ID, EPM.REGISTER_ID AS REGISTER_ID, NVL(IS_EXPRESS,0) AS IS_EXPRESS, 0 AS LAB_REQUEST_ID, 0 AS SPECIMEN_ID,0 AS IS_ACTIVE
FROM EMRAppointmentDetailsHistory EA
LEFT JOIN EMRTransactions ET ON ET.ID_VALUE = EA.APPOINTMENT_ID
LEFT JOIN EMRPatientsMaster EPM ON EA.PATIENT_ID = EPM.PATIENT_ID
LEFT JOIN EMRAppointmentStatusLkup EAL ON EAL.APPOINTMENT_STATUS_ID = EA.APPOINTMENT_ID
LEFT JOIN EMRConsultantDetails ECD ON ECD.CONSULTANT_ID = EA.CONSULTANT_ID
LEFT JOIN EMRTaskListLkup ETL ON ET.TASK_ID = ETL.TASK_ID
WHERE ET.TASK_ID = 108 AND EA.APPOINTMENT_STATUS_ID = 2
WITH CHECK OPTION
/
|
|
|
|
Re: view to be split into multiple views [message #418034 is a reply to message #418033] |
Wed, 12 August 2009 11:25   |
andrew again
Messages: 2577 Registered: March 2000
|
Senior Member |
|
|
I'm sure you'll agree that to have separate views, they'll need to return different results, and you would need to know which one to query based on what each view showed. How can you come up with the different views without understanding the different scenarios?
Consider using a marerialized view that you refresh periodically, based on the one you posted and query that MV.
|
|
|
|
Re: view to be split into multiple views [message #418038 is a reply to message #418032] |
Wed, 12 August 2009 11:43   |
cookiemonster
Messages: 13965 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
That's a scary looking view.
You do realise it's got a lot of unnecessary duplication?
For example this select:
SELECT ET.EMR_TRANSACTION_ID, ET.TASK_ID, ETL.TASK_NAME,
'0' AS NODE_DEFINITION_ID, NVL(ET.ID_VALUE, '0') AS ID_VALUE, '0' AS FLOW_ID,
'1/1/1900' AS APPOINTMENT_DATE, '12:00:00' AS APPOINTMENT_TIME, '1/1/1900' AS CREATED_DATE,
'0' AS APPOINTMENT_ID, '' AS FRONTDESK_COMMENTS, '' AS APPOINTMENT_STATUS,
EPM.PATIENT_FIRSTNAME, EPM.PATIENT_LASTNAME, EPM.PATIENT_DOB,
EPM.PATIENT_HOMEPHONE, EPM.PATIENT_ID, '' AS PATIENT_TYPE,
'' AS PATIENT_GENDER,'' AS RACE_ETHNICITY_ID,'' AS MARITAL_STATUS,
'' AS NAME, ET.USER_LOGIN AS CONSULTANT_ID, '' AS CONSULTANT_NAME,
ET.ROLE_NAME AS ROLE_NAME, EPM.LOCATION_ID, EPM.REGISTER_ID AS REGISTER_ID,
NVL(IS_EXPRESS,0) AS IS_EXPRESS, 0 AS LAB_REQUEST_ID, 0 AS SPECIMEN_ID, 0 AS IS_ACTIVE
FROM EMRTransactions ET, EMRTaskListLkup ETL, EMRPatientsMaster EPM
WHERE ETL.TASK_ID = ET.TASK_ID AND ET.PATIENT_ID = EPM.PATIENT_ID AND ET.TASK_ID IN (79, 91,102,103, 104)
and this select:
SELECT ET.EMR_TRANSACTION_ID, ET.TASK_ID, ETL.TASK_NAME,
'0' AS NODE_DEFINITION_ID, NVL(ET.ID_VALUE, '0') AS ID_VALUE, '0' AS FLOW_ID,
'1/1/1900' AS APPOINTMENT_DATE, '12:00:00' AS APPOINTMENT_TIME, '1/1/1900' AS CREATED_DATE,
'0' AS APPOINTMENT_ID, '' AS FRONTDESK_COMMENTS, '' AS APPOINTMENT_STATUS,
EPM.PATIENT_FIRSTNAME, EPM.PATIENT_LASTNAME, EPM.PATIENT_DOB,
EPM.PATIENT_HOMEPHONE, EPM.PATIENT_ID, '' AS PATIENT_TYPE,
'' AS PATIENT_GENDER,'' AS RACE_ETHNICITY_ID,'' AS MARITAL_STATUS,
'' AS NAME, ET.USER_LOGIN AS CONSULTANT_ID, '' AS CONSULTANT_NAME,
ET.ROLE_NAME AS ROLE_NAME, EPM.LOCATION_ID, EPM.REGISTER_ID AS REGISTER_ID,
NVL(IS_EXPRESS,0) AS IS_EXPRESS, 0 AS LAB_REQUEST_ID, 0 AS SPECIMEN_ID, 0 AS IS_ACTIVE
FROM EMRTransactions ET, EMRTaskListLkup ETL, EMRPatientsMaster EPM
WHERE ETL.TASK_ID = ET.TASK_ID AND ET.PATIENT_ID = EPM.PATIENT_ID AND ET.TASK_ID IN (73,92,93,114,127)
Are identical apart from the values in the IN list.
There appear to be several others that duplicate in similar ways.
So I suggest you find all the duplicate selects in there and merge them. Then see what you're left with.
|
|
|
Re: view to be split into multiple views [message #418110 is a reply to message #418038] |
Thu, 13 August 2009 00:36   |
rajasekhar857
Messages: 500 Registered: December 2008
|
Senior Member |
|
|
SELECT ET.EMR_TRANSACTION_ID, ET.TASK_ID, ETL.TASK_NAME, '0' AS NODE_DEFINITION_ID, NVL(ET.ID_VALUE, '0') AS ID_VALUE, '0' AS FLOW_ID,
'1/1/1900' AS APPOINTMENT_DATE, '12:00:00' AS APPOINTMENT_TIME, '1/1/1900' AS CREATED_DATE, '0' AS APPOINTMENT_ID, '' AS FRONTDESK_COMMENTS,
'' AS APPOINTMENT_STATUS, EPM.PATIENT_FIRSTNAME, EPM.PATIENT_LASTNAME, EPM.PATIENT_DOB, EPM.PATIENT_HOMEPHONE,
EPM.PATIENT_ID, '' AS PATIENT_TYPE,'' AS PATIENT_GENDER,'' AS RACE_ETHNICITY_ID,'' AS MARITAL_STATUS, '' AS NAME, ET.USER_LOGIN AS CONSULTANT_ID, '' AS CONSULTANT_NAME, ET.ROLE_NAME, EPM.LOCATION_ID, EPM.REGISTER_ID AS REGISTER_ID, NVL(IS_EXPRESS,0) AS IS_EXPRESS, 0 AS LAB_REQUEST_ID, 0 AS SPECIMEN_ID , 0 AS IS_ACTIVE
FROM EMRTransactions ET, EMRTaskListLkup ETL, EMRPatientsMaster EPM, EMRPatientNotes EPN
WHERE ETL.TASK_ID = ET.TASK_ID AND ET.PATIENT_ID = EPM.PATIENT_ID AND EPN.NOTES_ID = ET.ID_VALUE AND ET.TASK_ID = 12
AND
SELECT ET.EMR_TRANSACTION_ID, ET.TASK_ID, ETL.TASK_NAME, '0' AS NODE_DEFINITION_ID, NVL(ET.ID_VALUE, '0') AS ID_VALUE, '0' AS FLOW_ID,
'1/1/1900' AS APPOINTMENT_DATE, '12:00:00' AS APPOINTMENT_TIME, '1/1/1900' AS CREATED_DATE, '0' AS APPOINTMENT_ID, '' AS FRONTDESK_COMMENTS,
'' AS APPOINTMENT_STATUS, EPM.PATIENT_FIRSTNAME, EPM.PATIENT_LASTNAME, EPM.PATIENT_DOB, EPM.PATIENT_HOMEPHONE,
EPM.PATIENT_ID, '' AS PATIENT_TYPE,'' AS PATIENT_GENDER,'' AS RACE_ETHNICITY_ID,'' AS MARITAL_STATUS, '' AS NAME, ET.USER_LOGIN AS CONSULTANT_ID, '' AS CONSULTANT_NAME, ET.ROLE_NAME AS ROLE_NAME, EPM.LOCATION_ID, EPM.REGISTER_ID AS REGISTER_ID, NVL(IS_EXPRESS,0) AS IS_EXPRESS, 0 AS LAB_REQUEST_ID, 0 AS SPECIMEN_ID , 0 AS IS_ACTIVE
FROM EMRTransactions ET, EMRTaskListLkup ETL, EMRPatientsMaster EPM, EMRPatientNotes EPN
WHERE ETL.TASK_ID = ET.TASK_ID AND ET.PATIENT_ID = EPM.PATIENT_ID AND EPN.NOTES_ID = ET.ID_VALUE AND ET.TASK_ID = 13
are they both identical because some columns are provided aliases in one select query and one select query don't.can i merge this into one query or not?please reply me for this as i am getting confusing in this
|
|
|
Re: view to be split into multiple views [message #418130 is a reply to message #418110] |
Thu, 13 August 2009 02:03   |
rajasekhar857
Messages: 500 Registered: December 2008
|
Senior Member |
|
|
SELECT ET.EMR_TRANSACTION_ID, ET.TASK_ID,'ENCOUNTER' AS TASK_NAME, TO_CHAR(ET.NODE_DEFINITION_ID) AS NODE_DEFINITION_ID, ET.ID_VALUE, TO_CHAR(ET.FLOW_ID) AS FLOW_ID,
TO_CHAR(EA.APPOINTMENT_DATE,'DD/MM/YYYY') AS APPOINTMENT_DATE, TO_CHAR(EA.APPOINTMENT_TIME,'HH:MI:SS AM') AS APPOINTMENT_TIME,
TO_CHAR(EA.CREATED_DATE,'DD/MM/YYYY') AS CREATED_DATE, TO_CHAR(EA.APPOINTMENT_ID) AS APPOINTMENT_ID,EA.REASON || ' ' || EA.FRONTDESK_COMMENTS AS FRONTDESK_COMMENTS, EAL.APPOINTMENT_STATUS,
EPM.PATIENT_FIRSTNAME, EPM.PATIENT_LASTNAME, EPM.PATIENT_DOB, EPM.PATIENT_HOMEPHONE, EPM.PATIENT_ID, EPM.PATIENT_TYPE,EPM.PATIENT_GENDER,EPM.RACE_ETHNICITY_ID,EPM.MARITAL_STATUS,
'' AS NAME, ET.USER_LOGIN AS CONSULTANT_ID, ECD.CONSULTANT_NAME, ET.ROLE_NAME, EA.APPOINTMENT_LOCATION_ID AS LOCATION_ID, EPM.REGISTER_ID AS REGISTER_ID, NVL(IS_EXPRESS,0) AS IS_EXPRESS, 0 AS LAB_REQUEST_ID, 0 AS SPECIMEN_ID, 1 AS IS_ACTIVE
FROM EMRAppointmentDetailsHistory EA
LEFT JOIN EMRTransactions ET ON ET.ID_VALUE = EA.APPOINTMENT_ID
LEFT JOIN EMRPatientsMaster EPM ON EA.PATIENT_ID = EPM.PATIENT_ID
LEFT JOIN EMRAppointmentStatusLkup EAL ON EAL.APPOINTMENT_STATUS_ID = EA.APPOINTMENT_ID
LEFT JOIN EMRConsultantDetails ECD ON ECD.CONSULTANT_ID = EA.CONSULTANT_ID
WHERE ET.TASK_ID = 9 AND EA.APPOINTMENT_STATUS_ID = 2
AND EXISTS (SELECT EMR_TRANSACTION_ID FROM EMRTRANSACTIONS ET1
WHERE ET1.TASK_ID=108 AND ET1.PATIENT_ID = ET.PATIENT_ID AND ET.ID_VALUE = ET1.ID_VALUE)
SELECT ET.EMR_TRANSACTION_ID, ET.TASK_ID,'ENCOUNTER' AS TASK_NAME, TO_CHAR(ET.NODE_DEFINITION_ID) AS NODE_DEFINITION_ID, ET.ID_VALUE, TO_CHAR(ET.FLOW_ID) AS FLOW_ID,
TO_CHAR(EA.APPOINTMENT_DATE,'DD/MM/YYYY') AS APPOINTMENT_DATE, TO_CHAR(EA.APPOINTMENT_TIME,'HH:MI:SS AM') AS APPOINTMENT_TIME,
TO_CHAR(EA.CREATED_DATE,'DD/MM/YYYY') AS CREATED_DATE, TO_CHAR(EA.APPOINTMENT_ID) AS APPOINTMENT_ID,EA.REASON || ' ' || EA.FRONTDESK_COMMENTS AS FRONTDESK_COMMENTS, EAL.APPOINTMENT_STATUS,
EPM.PATIENT_FIRSTNAME, EPM.PATIENT_LASTNAME, EPM.PATIENT_DOB, EPM.PATIENT_HOMEPHONE, EPM.PATIENT_ID, EPM.PATIENT_TYPE,EPM.PATIENT_GENDER,EPM.RACE_ETHNICITY_ID,EPM.MARITAL_STATUS,
'' AS NAME, ET.USER_LOGIN AS CONSULTANT_ID, ECD.CONSULTANT_NAME, ET.ROLE_NAME, EA.APPOINTMENT_LOCATION_ID AS LOCATION_ID, EPM.REGISTER_ID AS REGISTER_ID, NVL(IS_EXPRESS,0) AS IS_EXPRESS, 0 AS LAB_REQUEST_ID, 0 AS SPECIMEN_ID, 0 AS IS_ACTIVE
FROM EMRAppointmentDetailsHistory EA
LEFT JOIN EMRTransactions ET ON ET.ID_VALUE = EA.APPOINTMENT_ID
LEFT JOIN EMRPatientsMaster EPM ON EA.PATIENT_ID = EPM.PATIENT_ID
LEFT JOIN EMRAppointmentStatusLkup EAL ON EAL.APPOINTMENT_STATUS_ID = EA.APPOINTMENT_ID
LEFT JOIN EMRConsultantDetails ECD ON ECD.CONSULTANT_ID = EA.CONSULTANT_ID
WHERE ET.TASK_ID = 9 AND EA.APPOINTMENT_STATUS_ID = 2
AND NOT EXISTS (SELECT EMR_TRANSACTION_ID FROM EMRTRANSACTIONS ET1
WHERE ET1.TASK_ID=108 AND ET1.PATIENT_ID = ET.PATIENT_ID AND ET.ID_VALUE = ET1.ID_VALUE)
one is having exists and the other is having not exists conditions i want to make it in a single query for both ones.please help me out in doing so.
|
|
|
|
Re: view to be split into multiple views [message #418158 is a reply to message #418110] |
Thu, 13 August 2009 03:58   |
cookiemonster
Messages: 13965 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
rajasekhar857 wrote on Thu, 13 August 2009 06:36 |
SELECT ET.EMR_TRANSACTION_ID, ET.TASK_ID, ETL.TASK_NAME, '0' AS NODE_DEFINITION_ID, NVL(ET.ID_VALUE, '0') AS ID_VALUE, '0' AS FLOW_ID,
'1/1/1900' AS APPOINTMENT_DATE, '12:00:00' AS APPOINTMENT_TIME, '1/1/1900' AS CREATED_DATE, '0' AS APPOINTMENT_ID, '' AS FRONTDESK_COMMENTS,
'' AS APPOINTMENT_STATUS, EPM.PATIENT_FIRSTNAME, EPM.PATIENT_LASTNAME, EPM.PATIENT_DOB, EPM.PATIENT_HOMEPHONE,
EPM.PATIENT_ID, '' AS PATIENT_TYPE,'' AS PATIENT_GENDER,'' AS RACE_ETHNICITY_ID,'' AS MARITAL_STATUS, '' AS NAME, ET.USER_LOGIN AS CONSULTANT_ID, '' AS CONSULTANT_NAME, ET.ROLE_NAME, EPM.LOCATION_ID, EPM.REGISTER_ID AS REGISTER_ID, NVL(IS_EXPRESS,0) AS IS_EXPRESS, 0 AS LAB_REQUEST_ID, 0 AS SPECIMEN_ID , 0 AS IS_ACTIVE
FROM EMRTransactions ET, EMRTaskListLkup ETL, EMRPatientsMaster EPM, EMRPatientNotes EPN
WHERE ETL.TASK_ID = ET.TASK_ID AND ET.PATIENT_ID = EPM.PATIENT_ID AND EPN.NOTES_ID = ET.ID_VALUE AND ET.TASK_ID = 12
AND
SELECT ET.EMR_TRANSACTION_ID, ET.TASK_ID, ETL.TASK_NAME, '0' AS NODE_DEFINITION_ID, NVL(ET.ID_VALUE, '0') AS ID_VALUE, '0' AS FLOW_ID,
'1/1/1900' AS APPOINTMENT_DATE, '12:00:00' AS APPOINTMENT_TIME, '1/1/1900' AS CREATED_DATE, '0' AS APPOINTMENT_ID, '' AS FRONTDESK_COMMENTS,
'' AS APPOINTMENT_STATUS, EPM.PATIENT_FIRSTNAME, EPM.PATIENT_LASTNAME, EPM.PATIENT_DOB, EPM.PATIENT_HOMEPHONE,
EPM.PATIENT_ID, '' AS PATIENT_TYPE,'' AS PATIENT_GENDER,'' AS RACE_ETHNICITY_ID,'' AS MARITAL_STATUS, '' AS NAME, ET.USER_LOGIN AS CONSULTANT_ID, '' AS CONSULTANT_NAME, ET.ROLE_NAME AS ROLE_NAME, EPM.LOCATION_ID, EPM.REGISTER_ID AS REGISTER_ID, NVL(IS_EXPRESS,0) AS IS_EXPRESS, 0 AS LAB_REQUEST_ID, 0 AS SPECIMEN_ID , 0 AS IS_ACTIVE
FROM EMRTransactions ET, EMRTaskListLkup ETL, EMRPatientsMaster EPM, EMRPatientNotes EPN
WHERE ETL.TASK_ID = ET.TASK_ID AND ET.PATIENT_ID = EPM.PATIENT_ID AND EPN.NOTES_ID = ET.ID_VALUE AND ET.TASK_ID = 13
are they both identical because some columns are provided aliases in one select query and one select query don't.can i merge this into one query or not?please reply me for this as i am getting confusing in this
|
These selects all come from the same view. If the selected columns didn't end up with the same names for each select the view just ignores the names from the 2nd and subsequent selects anyway. So differences in aliases can be ignored.
In this case the difference is that one column is aliased to it's own name, which is pointless anyway.
|
|
|
Re: view to be split into multiple views [message #418162 is a reply to message #418158] |
Thu, 13 August 2009 04:09   |
rajasekhar857
Messages: 500 Registered: December 2008
|
Senior Member |
|
|
for my above requirement can i write like this
SELECT ET.EMR_TRANSACTION_ID, ET.TASK_ID,'ENCOUNTER' AS TASK_NAME, TO_CHAR(ET.NODE_DEFINITION_ID) AS NODE_DEFINITION_ID, ET.ID_VALUE, TO_CHAR(ET.FLOW_ID) AS FLOW_ID,
TO_CHAR(EA.APPOINTMENT_DATE,'DD/MM/YYYY') AS APPOINTMENT_DATE, TO_CHAR(EA.APPOINTMENT_TIME,'HH:MI:SS AM') AS APPOINTMENT_TIME,
TO_CHAR(EA.CREATED_DATE,'DD/MM/YYYY') AS CREATED_DATE, TO_CHAR(EA.APPOINTMENT_ID) AS APPOINTMENT_ID,EA.REASON || ' ' || EA.FRONTDESK_COMMENTS AS FRONTDESK_COMMENTS, EAL.APPOINTMENT_STATUS,
EPM.PATIENT_FIRSTNAME, EPM.PATIENT_LASTNAME, EPM.PATIENT_DOB, EPM.PATIENT_HOMEPHONE, EPM.PATIENT_ID, EPM.PATIENT_TYPE,EPM.PATIENT_GENDER,EPM.RACE_ETHNICITY_ID,EPM.MARITAL_STATUS,
'' AS NAME, ET.USER_LOGIN AS CONSULTANT_ID, ECD.CONSULTANT_NAME, ET.ROLE_NAME, EA.APPOINTMENT_LOCATION_ID AS LOCATION_ID, EPM.REGISTER_ID AS REGISTER_ID, NVL(IS_EXPRESS,0) AS IS_EXPRESS, 0 AS LAB_REQUEST_ID, 0 AS SPECIMEN_ID, 1 AS IS_ACTIVE
FROM EMRAppointmentDetailsHistory EA
LEFT JOIN EMRTransactions ET ON ET.ID_VALUE = EA.APPOINTMENT_ID
LEFT JOIN EMRPatientsMaster EPM ON EA.PATIENT_ID = EPM.PATIENT_ID
LEFT JOIN EMRAppointmentStatusLkup EAL ON EAL.APPOINTMENT_STATUS_ID = EA.APPOINTMENT_ID
LEFT JOIN EMRConsultantDetails ECD ON ECD.CONSULTANT_ID = EA.CONSULTANT_ID
WHERE ET.TASK_ID = 9 AND EA.APPOINTMENT_STATUS_ID = 2
AND EXISTS (SELECT EMR_TRANSACTION_ID FROM EMRTRANSACTIONS ET1
WHERE ET1.TASK_ID=108 AND ET1.PATIENT_ID = ET.PATIENT_ID AND ET.ID_VALUE = ET1.ID_VALUE)
AND NOT EXISTS (SELECT EMR_TRANSACTION_ID FROM EMRTRANSACTIONS ET1
WHERE ET1.TASK_ID=108 AND ET1.PATIENT_ID = ET.PATIENT_ID AND ET.ID_VALUE = ET1.ID_VALUE)
|
|
|
Re: view to be split into multiple views [message #418175 is a reply to message #418032] |
Thu, 13 August 2009 04:28   |
cookiemonster
Messages: 13965 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
No.
The EXISTS and NOT EXISTS can't both be true, so if you use that you'll get no rows.
As Frank suggested you need to use an outer join instead.
Remove both exists.
Outer join to EMRTRANSACTIONS using the same where clause as the exists.
Then replace the is_active column with a decode statement that returns 1 if you've found a record in EMRTRANSACTIONS and 0 otherwise.
|
|
|
|
|
Goto Forum:
Current Time: Tue Feb 18 13:13:31 CST 2025
|