How to join var array values

Question

SELECT
      person.bu_id,
      key.key_val,
      obj_person_add.person_contact_freq,
      obj_person_add.person_contact_best, 
      OBJ_PERSON_ADD.PERSON_CONTACT_INST,
      OBJ_PERSON_ADD.PERSON_CONTACT_DATE,
      OBJ_PERSON_ADD.PERSON_BROCHURES,
      OBJ_PERSON_ADD.PERSON_NEWSLETTER
FROM obj_person person
INNER JOIN obj_person_add 
      ON person.obj_id = obj_person_add.obj_id
LEFT JOIN obj_rel_key key 
      ON key.obj_id = person.obj_id
WHERE person.bu_id in (6,7)
      AND person.close_date IS NULL
      AND key.obj_key_id = 806
      AND (   
           obj_person_add.person_contact_freq    IS NOT NULL 
           OR obj_person_add.person_contact_best IS NOT NULL 
           OR OBJ_PERSON_ADD.PERSON_CONTACT_INST IS NOT NULL 
           OR OBJ_PERSON_ADD.PERSON_CONTACT_DATE IS NOT NULL 
           OR OBJ_PERSON_ADD.PERSON_BROCHURES    IS NOT NULL 
           OR OBJ_PERSON_ADD.PERSON_NEWSLETTER   IS NOT NULL
         );

I have built this query, however person_brochures and person_newsletters are stored in a var array. This means that they return (6172,6544...) for example. I want to do a left join to match the ID's in the var array to a name in a different table.

Do I have to loop the var array and somehow match the ID's then?

Is this Oracle Enterprise Database or MySQL? – Mureinik Aug 27 '15 at 10:05 — Mureinik, Aug 27 '15 at 10:05

score 1 · Answer 1 · answered Aug 27 '15 at 17:55

You might try something called collection unnesting:

SELECT
      person.bu_id,
      ...
      OBJ_PERSON_ADD.PERSON_BROCHURES,
      OBJ_PERSON_ADD.PERSON_NEWSLETTER
FROM obj_person person
INNER JOIN obj_person_add 
      ON person.obj_id = obj_person_add.obj_id
...,
TABLE(OBJ_PERSON_ADD.PERSON_BROCHURES) x
INNER JOIN your_other_table
      ON x.column_value = your_other_table.id
...

column_value is the name of Oracle pseudocolumn that is assigned to the VARRAY elements.

score 0 · Answer 2 · edited May 23 '17 at 12:06

I am not 100% clear, I am guessing that you are writing this code in C# .Net.

You need to decide where the problem is going to be solved in , are you going to solve it in your App tier or persistence tier ?

I am assuming that you want to solve it in the persistence tier , you have couple of options 1. Generate a prepared statement and execute the statement ..instead of join you will have to rely on IN operator ..etc

Use a stored procedure , pass the two arrays as comma separated string to a stored procedure

you can look at this example if you need to solve it in Oracle function ..so you can do a join as you originally thought.

Splitting string into multiple rows in Oracle

use oracle object relational features and map the data structure appropriately to a structure and use oracle MULTISET like collections to solve the query in a stored procedure ..etc

How to join var array values

2 Answers2