Clever Geek Handbook

ORACLE SQL Query regarding joining a connection

I have the following problem. I am currently trying to get information from multiple tables back into our database. The process consists in adding activity to the system, which should theoretically be accompanied by an agreement of a certain type. However, sometimes the action is not accompanied by the expected agreement, and I would like to determine where this happens.

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

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

What the data looks like:

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

Basically, if there is no agreement about the type of payment, I don't want the agreement to show (but I still want to see the activity). The identifier is used to join agreements, and agreements and agreement type are from separate tables connected by a description identifier.

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 element of service table that contains the agreement type description is separate from the agreements table. I could use the agreement type code (which actually joins the agreement type to the agreements table), however, really, I just want to know if I can achieve an inner join between the agreement table and the agreement type table that does not affect the action table.

Any pointers and tips are appreciated, I hope I have explained enough.

+3


source to share


2 answers


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'
+1


source


The easiest way (due to the fact that it is one SQL monster) is to wrap it up and use a case statement for example.

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 in all of your first SQL, as it's easier than choosing your path. By the way, it's always good to avoid spaces in column names, so I used _ in this case.

+1


source






More articles:


All Articles