我试图返回完整的人员列表,但只在文档类型为"安全许可报告"的"文档类型"列中显示数据
我是SQL的新手,正在尝试弄清楚这些东西。目的是让管理层可以很容易地看到谁拥有这种文档类型,但也可以看到谁没有。
SELECT papf.person_id
, (ppnf.first_name||' '||ppnf.last_name) e_name
, dor.document_name
, dor_type.document_type
, TO_CHAR(dor.creation_date, 'DD/MM/YYYY') dor_issue_date
FROM per_all_people_f papf
, per_person_names_f ppnf
, hr_document_types_vl dor_type
, hr_documents_of_record dor
WHERE SYSDATE BETWEEN papf.effective_start_date AND papf.effective_end_date
AND papf.person_id = ppnf.person_id
AND SYSDATE BETWEEN ppnf.effective_start_date AND ppnf.effective_end_date
AND ppnf.name_type = 'GLOBAL'
AND papf.person_id = dor.person_id (+)
AND dor_type.document_type_id = dor.document_type_id
AND dor_type.document_type = 'Security Clearance Report'
ORDER BY e_name DESC
正如这里其他地方所指出的,如果使用ANSI样式的联接,这类操作要简单得多。
即便如此,这还是有点棘手,因为您想将您的人员列表LEFT JOIN
HR_DOCUMENTS_OF_RECORD
和HR_DOCUMENT_TYPES_VL
之间的INNER JOIN
。也就是说,对于每个人,您都需要类型为"安全许可报告"的记录文档(如果存在(。
以下是您的操作方法:
SELECT papf.person_id
, (ppnf.first_name||' '||ppnf.last_name) e_name
, dor.document_name
, dor_type.document_type
, TO_CHAR(dor.creation_date, 'DD/MM/YYYY') dor_issue_date
FROM per_all_people_f papf
INNER JOIN per_person_names_f ppnf ON ppnf.person_id = papf.person_id
AND ppnf.name_type = 'GLOBAL'
AND SYSDATE BETWEEN ppnf.effective_start_date AND NVL(ppnf.effective_end_date,SYSDATE)
LEFT JOIN ( hr_documents_of_record dor
INNER JOIN hr_document_types_vl dor_type ON dor_type.document_type_id = dor.document_type_id
AND dor_type.document_type = 'Security Clearance Report' ) ON dor.person_id = papf.person_id
WHERE SYSDATE BETWEEN papf.effective_start_date AND NVL(papf.effective_end_date,SYSDATE)
ORDER BY e_name DESC;
请注意,DOR
和DOR_TYPE
之间的INNER JOIN
在括号中,LEFT JOIN
条件在括号后。
如果你只是天真地LEFT JOIN
所有的东西和任何人除了安全许可报告之外都有记录文件,你会得到太多的行。
您可能想要一个LEFT JOIN
SELECT *
FROM per_all_people_f papf
JOIN hr_documents_of_record dor
ON papf.person_id = dor.person_id
LEFT JOIN hr_document_types_vl dor_type
ON dor_type.document_type_id = dor.document_type_id
AND dor_type.document_type = 'Security Clearance Report'
我正试图返回完整的人员列表,
要获得完整的人员列表,我将删除该要求AND dor_type.document_type = 'Security Clearance Report'
目的是让管理层可以很容易地看到谁拥有这种文档类型,但也可以看到谁没有。
在您的选择中,您可以这样做:
, DECODE(dor_type.document_type, 'Security Clearance Report', dor_type.document_type ,null)
对于document_type是您想要的行,它会显示它。对于其他行,它只显示null。
SELECT papf.person_id
, (ppnf.first_name||' '||ppnf.last_name) e_name
, dor.document_name
, DECODE(dor_type.document_type, 'Security Clearance Report', dor_type.document_type ,null)
, TO_CHAR(dor.creation_date, 'DD/MM/YYYY') dor_issue_date
FROM per_all_people_f papf
, per_person_names_f ppnf
, hr_document_types_vl dor_type
, hr_documents_of_record dor
WHERE SYSDATE BETWEEN papf.effective_start_date AND papf.effective_end_date
AND papf.person_id = ppnf.person_id
AND SYSDATE BETWEEN ppnf.effective_start_date AND ppnf.effective_end_date
AND ppnf.name_type = 'GLOBAL'
AND papf.person_id = dor.person_id (+)
AND dor_type.document_type_id = dor.document_type_id
ORDER BY e_name DESC