-2

enter image description here

I am attempting to write a validation report for a client that validates a number of rules for a workflow.

Test Matrix as follows (I've left out the PROJECT portion because that could just complicate things and if I get it working for ACCOMPLISHMENT I believe I should be able to figure out the PROJECT side)

enter image description here

The following query is what I have so far testing the "Accomplishment has Infrastructure but no matching BIL Funding Allocated" validation. It passes all the applicable tests except Test #1 (unsure why, just yet).

select a.id as accomp_id, sc.id as sc_id, sc.special_funding_cat_id, i.id as i_id, i.infrastructure_type_id
from accomplishment a
join infrastructure i on a.id = i.accomp_id
left join accomplishment_funding sc on a.id = sc.accomp_id
left join habits4.project p on a.project_id = p.id  
where a.report_fiscal_year in ( 2022)  
--and (sc.id is not null and sc.source_type_id != i.source_type_id)
group by a.id, sc.id, i.id, sc.source_type_id, i.source_type_id 
having (sc.source_type_id != i.source_type_id is null)

If I change the HAVING clause to be IS NOT NULL it works but then the rest of the tests fail. Removing the null check from the HAVING clause causes Test #8 to fail because, why there is a matching Infrastructure and Funding for both types, it must be also validating against them not matching. If there is a match the match should be valid and if all matches are valid then it should pass validation. What exactly am I missing here?

Vaesive
  • 105
  • 1
  • 11

1 Answers1

0

I'm an idiot, I forgot you could add "ands" to joins -.-

Here's the working query that passes all the applicable tests.

select a.id as accomp_id, sc.id as sc_id, sc.source_type_id, i.id as i_id, i.source_type_id
from habits4.accomplishment a
join habits4.infrastructure i on a.id = i.accomp_id
left join habits4.service_contribution sc on a.id = sc.accomp_id and i.source_type_id = sc.source_type_id
left join habits4.project p on a.project_id = p.id  
where a.anticipated_comp_fy in ( 2022)  
and ((sc.id is not null and i.id is not null and sc.source_type_id!= i.source_type_id) or sc.id is null)
group by a.id, sc.id, i.id, sc.source_type_id, i.source_type_id
Vaesive
  • 105
  • 1
  • 11