I’m failing to properly structure this script to output the results of a join based on an array of numbers, I’ve went through some failed attempts and I’m getting a bit lost!

I start off with a single entitykey, I join this key to another table which produces several more keys, these keys are joined to another table which produces a new key for each previous key, using this final set of keys I must join to a final table where I will receive a result for each key.

I have a script working which fetches the first set of keys and assigns them to an array, and I have a simple join that works by manually inputting each key and running the script each time per key.

I’m trying to write the script so that the I generate an array, and loop a query for each number in the array.

Here is the query that generates the array:

`CREATE OR REPLACE TYPE varray IS TABLE OF number;

DECLARE
      releasableentitykey  varray := varray();
BEGIN
      FOR i IN (SELECT * FROM masterbatchrecordreleasesignat  
              INNER JOIN masterbatchrecord ON masterbatchrecordreleasesignat.releasableentitykey = masterbatchrecord.entitykey
              WHERE masterbatchrecord.entitykey='7884867406')
            LOOP
        releasableentitykey.extend;
        releasableentitykey(releasableentitykey.count) := i.Releasesignaturecollectionenti;
            END LOOP;
 END;`

This works and produces the list of keys I expect.

The second query which is not working:

SELECT * FROM signature  
INNER JOIN releasesignature ON signature.entitykey = releasesignature.signatureentitykey
INNER JOIN masterbatchrecordreleasesignat ON releasesignature.entitykey = masterbatchrecordreleasesignat.releasesignaturecollectionenti
WHERE masterbatchrecordreleasesignat.releasesignaturecollectionenti IN (SELECT column_value FROM TABLE(releasableentitykey);

How do I go about scripting and structuring this?