有关连接加入的ORACLE SQL查询

Question

我有一个问题。我目前正在尝试从我们数据库中的许多不同表格返回信息。该过程是向系统添加一个活动，理论上应该跟随某种类型的协议。然而，有时候这个活动没有跟随预期的协议，我想找出这种情况发生在哪里。

我当前有一个查询（如下所示），它返回的数据看起来像这样（从实际查询简化）：

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

数据应该长什么样子如下：

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

基本上，如果没有支付协议类型，我不想显示协议（但我仍然想看到活动）。ID用于将活动与协议连接起来，但协议和协议类型来自不同的表格，通过描述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'

问题在于包含协议类型描述的服务元素表与协议表是分开的。我可以使用协议类型代码（它实际上将协议类型连接到协议表），但我只想知道是否可以在不影响活动表的情况下在协议表和协议类型表之间实现内部连接。

欢迎提供任何指针和建议，希望我已经足够清楚地解释了自己。

- bawpie

Answer 1

最简单的方法（由于这是一个庞大的SQL语句）是将其包装起来并使用case语句，例如。

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

基本上，运行另一个 SQL 语句来处理你的第一个 SQL 语句会更容易，因为这比逐个筛选更简单。顺便提一下，在列名中避免使用空格总是一个好主意，所以在这种情况下我使用了下划线。

- Matt Donnan

---

谢谢，我确实考虑过类似的方法，但是我认为最好的做法是在一开始就阻止它们返回（请参见MB下面的解决方案）。 - bawpie

@bawpie 说得对，另外值得一提的是在那里别名化您的表名，因为这应该会显著减小 SQL 的大小，Oracle 的连接使用“+”比“Left”更容易处理。 - Matt Donnan

Answer 2

尝试：

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'

- user359040

---

再一次，这正是我想要的。我看到你已经通过在协议详情左连接中的“on”之前插入服务元素表来合并了这两个左连接。我从未想过以这种方式做，所以谢谢你。 - bawpie