For a family violence incidents(FV) we want to find out if the 2 parties involved are a victim, suspect or offender in any previous FV incidents between them previously.
I can find the incidents where only the 2 parties involved (victim and suspect) previously.
However, in our DB there are incidents where only one of the parties is named, like victim is there but no suspect or offender or vice versa.
So for a given incident I am using a DB function to find out all previous FVIRs where only the two people (victim and suspect) are involved together in any of these three roles Vic, Sus, Off.
In my function I have incident Id as as the input. This incident is a historical incident between the 2 parties (victim and suspect) of our selected incident. I also have the 2 primary keys of the victim and suspect as input.
Now for THIS incident, I can get the victim, suspect, and offender primary keys. I have them in 3 variables.
Like this:
FUNCTION f1 (pn_inc_id IN NUMBER, pn_victim_pk IN NUMBER, pn_suspect_pk IN NUMBER) RETURN VARCHAR2 IS
-- PK = primary key
DECLARE
ln_ret VARCHAR2(1) := 'N';
ln_pk_vic NUMBER;
ln_pk_sus NUMBER;
ln_pk_off NUMBER;
BEGIN
-- I use SQL to fill the 3 vars using pn_inc_id;
ln_pk_vic := has value (or null);
ln_pk_sus := has value (or null);
ln_pk_off := has value (or null);
-- Now I want is, how to find out that if the input victim and suspect are the parties involved in THIS incident??
END;