我有一个问题。我目前正在尝试从我们数据库中的许多不同表格返回信息。该过程是向系统添加一个活动,理论上应该跟随某种类型的协议。然而,有时候这个活动没有跟随预期的协议,我想找出这种情况发生在哪里。
我当前有一个查询(如下所示),它返回的数据看起来像这样(从实际查询简化):
数据应该长什么样子如下:
我当前有一个查询(如下所示),它返回的数据看起来像这样(从实际查询简化):
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'
问题在于包含协议类型描述的服务元素表与协议表是分开的。我可以使用协议类型代码(它实际上将协议类型连接到协议表),但我只想知道是否可以在不影响活动表的情况下在协议表和协议类型表之间实现内部连接。
欢迎提供任何指针和建议,希望我已经足够清楚地解释了自己。