Oracle SQL Joining two similar queries

Question

Okay, first off I will attempt to explain what I'm trying to do. I am trying to build a report that returns all assessments assigned to a client. Each assessment should have a corresponding event, and vice versa (unfortunately there is no token passed between the two so I have to determine the correct event based on dates). However, the event is sometimes missing, or the assessment is sometimes missing. I wish to capture all of these so that I can report where there are missing assessments or events and have them fixed. In it's most simple form, this is what I would expect the report to look like. However, there are a few other conditions, such as returning a particular type of assessment and a particular type of event.

ID  Assessment   Event
1   A1           B1
2   A2   
3                B3      

I currently have 2 queries written that show me all assessments with their corresponding events (if present), and all events with their corresponding assessments (if present). They both seem to work well, but I would really like to join the two queries (ID would be common in both queries). I've tried doing this myself, but it never quite works, so I'm hoping if I post both of my working queries, someone may be able to assist!

This brings back assessments with their corresponding event (where present).

 SELECT 
  "CLIENTID",
 "Name",
   "ASSESSMENT_ID",
 "Assessment_Start", 
 "Assessment_End",
  "Days open",
 "Authorised",
  "Worker",
  "Event_ID",
  "Event_Start"
 FROM 
 (SELECT 
 OAS.ASM_SUBJECT_ID as "CLIENTID",
   nvl(olm_bo.get_name(OAS.ASM_SUBJECT_ID,OAS.ASM_SUBJECT_IND),'') as "Name",
 OAS.ASM_ID as ASSESSMENT_ID,
 olm_bo.working_days(OAS.ASM_START_DATE, OAS.ASM_END_DATE) -1 as "Days open",
 OAS.ASM_AUTH_DATETIME as "Authorised",
 OAS.ASM_START_DATE as "Assessment_Start", 
 OAS.ASM_END_DATE as "Assessment_End",
 nvl(olm_bo.get_ref_desc(OAS.ASM_OUTCOME,'ASM_OUTCOME'),'') as "Outcome",
 nvl(olm_bo.get_org_name(ORE.RES_PARTY_OUN_ID),'') as "Team",
 initcap(nvl(olm_bo.get_per_name(ORE.RES_PARTY_ID),'')) as "Worker",
 OSE.SEV_ID as "Event_ID",
 to_char(trunc(OSE.SEV_ACTUAL_DATE),'DD/MM/YYYY') as "Event_Start",
 OSE.SEV_OUTCOME_DATE as "Event_End",
 nvl(decode(OSE.SEV_CLASS,'CPINVEST',olm_bo.get_ref_desc     (OSE.SEV_REASON_CODE,'CP_JUSTIFICATION'),olm_bo.get_ref_desc     (OSE.SEV_REASON_CODE,'REASON_CODE')),'') as "Event Outcome",
 row_number() over(PARTITION BY  OAS.ASM_ID                   
 ORDER BY 
 abs(OAS.ASM_START_DATE -  OSE.SEV_ACTUAL_DATE))as "Row Number"
 FROM O_ASSESSMENTS OAS      
 INNER JOIN O_RESPONSIBILITIES ORE ON OAS.ASM_ID = ORE.RES_REC_ID
 AND nvl(olm_bo.get_org_name(ORE.RES_PARTY_OUN_ID),'') = '#Team#' 
 LEFT JOIN O_SERVICE_EVENTS OSE ON OAS.ASM_SUBJECT_ID = OSE.SEV_SUBJECT_ID 
 AND            
 OSE.SEV_CODE IN ('INI') 
 AND             
 ROUND(OAS.ASM_START_DATE - OSE.SEV_ACTUAL_DATE,0) >= -7
 AND
 ROUND(OAS.ASM_START_DATE - OSE.SEV_ACTUAL_DATE,0) <= 7
 Where OAS.ASM_QSA_ID  IN ('A1','A2')
 AND
    nvl(olm_bo.get_ref_desc(OAS.ASM_OUTCOME,'ASM_OUTCOME'),'NULL')  NOT IN       ( 'Abandon'  )
 AND
    (
     OAS.ASM_END_DATE  Is Null  
     OR
     OAS.ASM_AUTH_DATETIME  Is Null  
    )
 ) WHERE "Row Number" = 1

This brings back events with their corresponding assessment (where present).

 SELECT 
  "CLIENTID",
 "Name",
   "Event_ID",
 "Event_Start", 
 "Event_End",
  "Days_open",
  "Worker",
 "Team",
  "Assessment_ID",
  "Assessment_Start",
 "Assessment_End",
 FROM 
 (SELECT 
 OSE.SEV_SUBJECT_ID as "CLIENTID",
   nvl(olm_bo.get_name(OSE.SEV_SUBJECT_ID,OSE.SEV_SUBJECT_IND),'') as "Name",
 OAS.ASM_ID as  "Assessment_ID",
 olm_bo.working_days(OSE.SEV_ACTUAL_DATE, OSE.SEV_OUTCOME_DATE) -1 as "Days_open",
 OAS.ASM_START_DATE as "Assessment_Start", 
 OAS.ASM_END_DATE as "Assessment_End",
 olm_bo.get_org_name(OSE.SEV_OUN_SHORT_NAME) as "Team",
 olm_bo.get_event_worker(OSE.SEV_ID,'NAME') as "Worker",
 OSE.SEV_ID as "Event_ID",
 to_char(trunc(OSE.SEV_ACTUAL_DATE),'DD/MM/YYYY') as "Event_Start",
 OSE.SEV_OUTCOME_DATE as "Event_End",
 nvl(decode(OSE.SEV_CLASS,'CPINVEST',olm_bo.get_ref_desc     (OSE.SEV_REASON_CODE,'CP_JUSTIFICATION'),olm_bo.get_ref_desc(OSE.SEV_REASON_CODE,'REASON_CODE')),'') as "Event Outcome",
 row_number() over(PARTITION BY  OSE.SEV_SUBJECT_ID                   
 ORDER BY 
 abs(OAS.ASM_START_DATE -  OSE.SEV_ACTUAL_DATE))as "Row Number"
 FROM O_SERVICE_EVENTS OSE
 LEFT JOIN O_ASSESSMENTS OAS ON OAS.ASM_SUBJECT_ID = OSE.SEV_SUBJECT_ID AND nvl     (olm_bo.get_ref_desc(OAS.ASM_OUTCOME,'ASM_OUTCOME'),'NULL')  NOT IN  ( 'Abandon'  )
 AND
 OAS.ASM_QSA_ID  IN ('A1','A2')            
 AND             
 ROUND(OAS.ASM_START_DATE - OSE.SEV_ACTUAL_DATE,0) >= -7
 AND
 ROUND(OAS.ASM_START_DATE - OSE.SEV_ACTUAL_DATE,0) <= 7
 Where OSE.SEV_CODE IN ('INITASS') 
 AND
 olm_bo.get_org_name(OSE.SEV_OUN_SHORT_NAME) = '#Team#'
 AND
    (
     OSE.SEV_OUTCOME_DATE  Is Null  
    )
 ) WHERE "Row Number" = 1

There are also some catches with a joined report - events and assessments can have different teams or workers, so I'd probably need to define seperate event team and event workers from the assessment team and event worker. I did think that this could be achieved by doing an outer join, or by joining the assessments and the events to a seperate table which returns all clients with an assessment or an event, but as mentioned, the results never seemed to quite add up to the seperate queries. I think it could be something to do with the row number = 1 condition, this is there to assist with returning a corresponding assessment to an event or vice versa, but I think when trying to join the two, this might be causing some of the problems? Anyway, thanks for your time - I will continue to tinker and see if I can find a solution, but any tips or suggestions would be much appreciated.

Answer 1

Why don't you union your 2 queries? If applicable, use union all which is much performer that union.

Answer 2

Like BD said, a FULL OUTER JOIN would do the trick, but a UNION could also be a good idea

Answer 3

have you tried a FULL OUTER JOIN? http://www.oreillynet.com/network/2002/04/23/fulljoin.html

SQL> select p.part_id, s.supplier_name
  2  from part p full outer join supplier s
  3  on p.supplier_id = s.supplier_id;

PART SUPPLIER_NAME
---- --------------------
P1   Supplier#1
P2   Supplier#2
P4
P3
     Supplier#3