The following code in CONTRACT_PROJECTID_XREF_VW limits us to only one project per contract, when in reality there may be many projects per contract:
SELECT contract_num, business_unit_pc, project_id
FROM FS_PS_GM_AWD_PROJT_VW@dweprd proj
WHERE proj.project_id = (
SELECT MIN(proj2.project_id) -- AHA! This limits us to only one project per award/grant. Why?!
FROM FS_PS_GM_AWD_PROJT_VW@dweprd proj2
WHERE proj2.contract_num = proj.contract_num
AND proj2.project_id <= proj.project_id
);
Also this, from Andy Herzog, an example which excluded PI Heather Nelson (1417996) from a grant:
Multiple projects per grant?
- Contract number: CON000000103852
- Three projects associated with this grant
The following code in CONTRACT_PROJECTID_XREF_VW limits us to only one project per contract, when in reality there may be many projects per contract:
Also this, from Andy Herzog, an example which excluded PI Heather Nelson (1417996) from a grant: