CODEWITHSUNDEEP
sqloracle-database
Vamekh
Vamekh

Reputation: 11

How to Find index of some value in passed array in Oracle query

I want to find index of column value in passed array for each row. Finding if that value is presented in array is not enough because i want to compare that index with value of another column.

I expect something like this.

SELECT * FROM my_table t WHERE :argumentArray.indexOf(t.col1) BETWEEN 0 AND t.col2

Maybe that will be possible if I convert that array to table or something like that.

Here is structure of my real example: Below is hierarchy of employee tree.

          1000         
          /  \         
       1001  1002      
       /        \      
     1003       1004   
     /                 
   1005

I have table where I store document share information

document_id | shared_under | level
----------- | ------------ | ---------
123         | 1000         | 2

this record means that document with id 123 is shared with employee with id 1000 and its children only by level 2. (employees with id 1000, 1001, 1002, 1003, 1004 can access that document and 1005 can not)

Desired result:

If employee 1005 searches documents, he passes his hierarchy as array argument, which is (1005, 1003, 1001, 1000)

query will look like:

SELECT `document_id` FROM `shared_documents` WHERE (1005, 1003, 1001, 1000).indexOf(shared_under) BETWEEN 0 AND `level`

This query must return no data because emp.1005 is under emp.1000 on level 3 but document is shared only 2 level deep.

If emp.1003 searches for shared documents he passes (1003, 1001, 1000) and query must return document 123, because it is shared with employee 1000, and employee 1003 is under him within 2 level depth.

I have solved this problem with regex, but i wonder if Oracle supports this kind of solution.

Upvotes: 0

Views: 2136

Answers (1)

MT0
MT0

Reputation: 168361

Use a table collection expression in a sub-query:

SELECT *
FROM   mytable t
WHERE  t.col1 IN ( SELECT COLUMN_VALUE
                   FROM   TABLE( :argumentArray )
                   WHERE  ROWNUM <= t."level" )

Update with an example:

Oracle Setup:

CREATE TABLE mytable ( lvl, col1 ) AS
  SELECT 1, 'a' FROM DUAL UNION ALL
  SELECT 2, 'a' FROM DUAL UNION ALL
  SELECT 1, 'b' FROM DUAL UNION ALL
  SELECT 3, 'b' FROM DUAL;

CREATE OR REPLACE TYPE stringlist IS TABLE OF VARCHAR2(10);
/

Query:

Calling this with a hard-coded collection but you can pass it in as a bind variable in PL/SQL or from other languages:

SELECT *
FROM   mytable t
WHERE  col1 IN ( SELECT COLUMN_VALUE
                 FROM   TABLE( stringlist( 'a', 'b' ) )
                 WHERE  ROWNUM <= t.lvl );

Output:

LVL COL1
--- ----
  1 a
  2 a
  3 b

Upvotes: 0

Related Questions