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 Go to next message
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 #418033 is a reply to message #418032] Wed, 12 August 2009 11:14 Go to previous messageGo to next message
Michel Cadot
Messages: 64117
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

./fa/449/0/

Re: view to be split into multiple views [message #418034 is a reply to message #418033] Wed, 12 August 2009 11:25 Go to previous messageGo to next message
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 #418037 is a reply to message #418032] Wed, 12 August 2009 11:38 Go to previous messageGo to next message
BlackSwan
Messages: 25039
Registered: January 2009
Location: SoCal
Senior Member
ALTER SESSION SET SQL_TRACE=TRUE;
-- invoke the problem SQL here
ALTER SESSION SET SQL_TRACE=FALSE;

now find the trace file within ./udump folder
tkprof <trace_file.trc> trace_results.txt explain=<username>/<password>

post the contents of trace_results.txt back here
Re: view to be split into multiple views [message #418038 is a reply to message #418032] Wed, 12 August 2009 11:43 Go to previous messageGo to next message
cookiemonster
Messages: 12405
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 #418142 is a reply to message #418130] Thu, 13 August 2009 02:47 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Use an outer join and decode the nullable column
Re: view to be split into multiple views [message #418158 is a reply to message #418110] Thu, 13 August 2009 03:58 Go to previous messageGo to next message
cookiemonster
Messages: 12405
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
cookiemonster
Messages: 12405
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.
Re: view to be split into multiple views [message #418179 is a reply to message #418032] Thu, 13 August 2009 04:57 Go to previous messageGo to next message
ashoka_bl
Messages: 398
Registered: November 2006
Location: Bangalore
Senior Member

I must say, you people are real GURUs... ohh that's one scary code
Re: view to be split into multiple views [message #418180 is a reply to message #418175] Thu, 13 August 2009 04:58 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
... and take care of possible duplicates you get from joining instead of using WHERE EXISTS...
Previous Topic: avg function on a date field
Next Topic: Problems with ElseIf
Goto Forum:
  


Current Time: Tue Dec 06 04:13:38 CST 2016

Total time taken to generate the page: 0.08506 seconds