ORACLE SQL Query regarding joining to a join

Question

I have the following problem. I am currently trying to return information from a number of different tables in our database. The process is that an activity is added to the system, which should be theoretically followed by an agreement of a certain type. However, sometimes the activity is not followed by the expected agreement, and I would like to identify where this is the case.

I currently have a query (shown below) which returns data which looks like this (simplified from the actual query):

ID  Activity  Agreement     Agreement Type
1   X         Budgets       Payment
2   X
3   X         Budgets

What the data should look like follows:

ID  Activity  Agreement     Agreement Type
1   X         Budgets       Payment
2   X
3   X         

Basically, if there is no agreement type of payment, I don't want the agreement to show (but I still want to see the activity). ID is used to join activity to agreements, but agreements and agreement type are from seperate tables joined on a description ID.

SELECT  O_ACTIVITIES.ACT_SUBJECT_ID as "ID",
        initcap(olm_bo.get_per_name(O_ACTIVITIES.ACT_SUBJECT_ID)) as "Name",
        O_ACTIVITIES.ACT_ID as "Activity ID",
        initcap(O_ACTIVITY_TYPES.ACT_DESC) as "Activity Type",
        O_ACTIVITIES.ACT_REQUESTED_DATE as "Start Date",
        case when olm_bo.get_ref_desc(O_ACTIVITIES.ACT_STATUS,'ACTIVITY_STATUS') = 'Newly generated' 
                  then null 
                  else O_ACTIVITIES.ACT_STATUS_DATE 
             end as "End Date",
        olm_bo.get_ref_desc(O_ACTIVITIES.ACT_STATUS,'ACTIVITY_STATUS') as "Status",
        O_ACTIVITIES.ACT_CREATED_BY as "Created by",
        O_AGREEMENT_DETAILS.ADE_ID as "Agreement ID",
        initcap(olm_bo.get_sty_name(O_AGREEMENT_DETAILS.ADE_STY_ID)) as "Service Type",
        initcap(olm_bo.get_sty_name(O_SERVICE_ELEMENTS.SEL_STY_CHILD_ID)) as "Service Element",
        O_AGREEMENT_DETAILS.ADE_START_DATE as "Agreement Start",
        O_AGREEMENT_DETAILS.ADE_END_DATE as "Agreement End",
        O_AGREEMENT_DETAILS.ADE_ENTERED_BY as "Entered by"
FROM O_ACTIVITIES
LEFT JOIN O_ACTIVITY_TYPES 
       ON O_ACTIVITY_TYPES.ACT_CLASS= O_ACTIVITIES.ACT_CLASS and 
          O_ACTIVITY_TYPES.ACT_TYPE=O_ACTIVITIES.ACT_TYPE AND
          initcap(O_ACTIVITY_TYPES.ACT_DESC)  =  'X'
LEFT OUTER JOIN O_AGREEMENT_DETAILS 
       ON O_AGREEMENT_DETAILS.ADE_SUBJECT_ID=O_ACTIVITIES.ACT_SUBJECT_ID  AND 
          initcap(olm_bo.get_sty_name(O_AGREEMENT_DETAILS.ADE_STY_ID)) IN ('Budgets') AND 
          O_AGREEMENT_DETAILS.ADE_START_DATE >= O_ACTIVITIES.ACT_REQUESTED_DATE
LEFT JOIN O_SERVICE_ELEMENTS 
       ON O_AGREEMENT_DETAILS.ADE_SEL_ID=O_SERVICE_ELEMENTS.SEL_ID AND
          initcap(olm_bo.get_sty_name(O_SERVICE_ELEMENTS.SEL_STY_CHILD_ID)) IN ('Payment')
WHERE (O_ACTIVITIES.ACT_SYSTEM_IND IS NULL and 
      NVL(O_ACTIVITIES.ACT_REC_TYPE,'???') NOT IN ('Y')  ) AND
      initcap(O_ACTIVITY_TYPES.ACT_DESC)  =  'X'

The problem is that the service element table which contains the agreement type description is seperate from the agreements table. I could use the agreement type code (which actually joins the agreement type to the agreement table), however, really I just want to know if I can achieve an inner join between the agreement table and the agreement type table which doesn't impact the activity table.

Any pointers and advice are appreciated, hopefully I've explained myself sufficiently.

Answer 1

Try:

SELECT  O_ACTIVITIES.ACT_SUBJECT_ID as "ID",
        initcap(olm_bo.get_per_name(O_ACTIVITIES.ACT_SUBJECT_ID)) as "Name",
        O_ACTIVITIES.ACT_ID as "Activity ID",
        initcap(O_ACTIVITY_TYPES.ACT_DESC) as "Activity Type",
        O_ACTIVITIES.ACT_REQUESTED_DATE as "Start Date",
        case when olm_bo.get_ref_desc(O_ACTIVITIES.ACT_STATUS,'ACTIVITY_STATUS') = 'Newly generated' 
                  then null 
                  else O_ACTIVITIES.ACT_STATUS_DATE 
             end as "End Date",
        olm_bo.get_ref_desc(O_ACTIVITIES.ACT_STATUS,'ACTIVITY_STATUS') as "Status",
        O_ACTIVITIES.ACT_CREATED_BY as "Created by",
        O_AGREEMENT_DETAILS.ADE_ID as "Agreement ID",
        initcap(olm_bo.get_sty_name(O_AGREEMENT_DETAILS.ADE_STY_ID)) as "Service Type",
        initcap(olm_bo.get_sty_name(O_SERVICE_ELEMENTS.SEL_STY_CHILD_ID)) as "Service Element",
        O_AGREEMENT_DETAILS.ADE_START_DATE as "Agreement Start",
        O_AGREEMENT_DETAILS.ADE_END_DATE as "Agreement End",
        O_AGREEMENT_DETAILS.ADE_ENTERED_BY as "Entered by"
FROM O_ACTIVITIES
LEFT JOIN O_ACTIVITY_TYPES 
       ON O_ACTIVITY_TYPES.ACT_CLASS= O_ACTIVITIES.ACT_CLASS and 
          O_ACTIVITY_TYPES.ACT_TYPE=O_ACTIVITIES.ACT_TYPE AND
          initcap(O_ACTIVITY_TYPES.ACT_DESC)  =  'X'
LEFT JOIN O_AGREEMENT_DETAILS 
          JOIN O_SERVICE_ELEMENTS 
            ON O_AGREEMENT_DETAILS.ADE_SEL_ID=O_SERVICE_ELEMENTS.SEL_ID AND
               initcap(olm_bo.get_sty_name(O_SERVICE_ELEMENTS.SEL_STY_CHILD_ID)) IN ('Payment')
       ON O_AGREEMENT_DETAILS.ADE_SUBJECT_ID=O_ACTIVITIES.ACT_SUBJECT_ID  AND 
          initcap(olm_bo.get_sty_name(O_AGREEMENT_DETAILS.ADE_STY_ID)) IN ('Budgets') AND 
          O_AGREEMENT_DETAILS.ADE_START_DATE >= O_ACTIVITIES.ACT_REQUESTED_DATE
WHERE (O_ACTIVITIES.ACT_SYSTEM_IND IS NULL and 
      NVL(O_ACTIVITIES.ACT_REC_TYPE,'???') NOT IN ('Y')  ) AND
      initcap(O_ACTIVITY_TYPES.ACT_DESC)  =  'X'

Answer 2

The easiest way (due to that being one monster SQL) is to wrap it and use the case statement e.g.

SELECT ID, Activity, Case When Agreement_Type IS NULL THEN NULL ELSE Agreement END, Agreement_Type
FROM
(All the original SQL goes in here)

So basically run another SQL around the whole of your first SQL as it is easier than picking your way through, btw it's always a good idea to avoid spaces in your column names so I've used an _ in this case.