nested dependent sql question

Question

I'm trying to implement a front end for a reporting solution which is security dependent. The user has 12 levels of nested criteria to select from, the value of each affects all of the values below.

So the criteria selection on the page (each is a dropdown) looks something like this:

Criteria 1
Criteria 2
...
Criteria 12

There is a Security table that holds the values that are available to each user which has the following structure:

EmployeeID | Criteria_1_valid_Value | C2_valid_Value | ... | C12_valid_Value
x0001 | c1 | c2 | ... | c12

and each Employee will have one or (many) more rows in this table. Think of it as a flattened tree, with Criteria1 as the root node.

Based on keys, changing Criteria 1 will affect the values that are visible in Criteria 2 through 12. In the same way, changing the value in Criteria 2 affects the values available in Criteria 3 through Criteria 12. At each level, there is an option to select 'All Values,' which is represented by a space internally, for lookups. So I need a representation in the lookup table/view which takes into account that there may be a space at one or many levels.

Where I'm struggling is with finding a way to build the lookup view/table for each Criteria field using sql without having to resort to hardcoding.

For example, to build the lookup for criteria 2 the sql might look like this:

select EmployeeID, Criteria1, Criteria2
from Security
Union
select EmployeeID, ' ', Criteria2
from Security
Union
select EmployeeID, Criteria1, ' '
from Security
UNION
select EmployeeID, ' ', ' '
from Security

And so on. Unfortunately, of course, with 12 levels, the last works out to 2^12 unions, which frankly smells.

I've tried building a table for each level in batch, committing after each, then using the previous table joined to the Security table to build the next with a single UNION in each, but I can't seem to get the joins to work properly with the spaces.

I don't know if I'm overthinking this or completely missing something, but I feel like there has to be a simpler solution.

EDIT: This is on Oracle and I'm working with an ERP product as the underlying technology.

EDIT2: Thanks for the input everyone. I got the joins eorking correctly using joins like in the example proc from @Alex Poole below:

and (v_Criteria_1 = ' ' or Criteria_1_valid_Value = v_Criteria_1)

I was missing the v_Criteria_1 = ' ' or.

So I've got the tables loaded correctly (enough) now. This is turning into a tuning/optimization exercise. I'm going to look at the proc from @Alex Poole and the artithmetic approach of @JD_55 which I think might be very quick.

Answer 1

I don't fully understand your requirements, but I would have thought that the query for criteria2 would be:

select distinct Criteria2
from Security
where EmployeeID = :the_user
and Criteria1 = :Criteria1

if the user must enter Criteria1 before Criteria2, or

select distinct Criteria2
from Security
where EmployeeID = :the_user
and (:Criteria1 is null or Criteria1 = :Criteria1)

otherwise?

Answer 2

Consider a series of left outer self join with each criteria item dependent on the values of the prior criteria. You can use the NVL() function to return spaces instead of nulls when the left join produces null results:

select a.employeeId, 
   nvl(c1.criteria_1, ' '), 
   nvl(c2.criteria_2, ' '), 
   nvl(c3.criteria_3, ' '), 
   nvl(c4.criteria_4, ' '), 
   nvl(c5.criteria_5, ' '), 
   nvl(c6.criteria_6, ' '), 
   nvl(c7.criteria_7, ' '), 
   nvl(c8.criteria_8, ' '), 
   nvl(c9.criteria_9, ' '), 
   nvl(c10.criteria_10, ' '), 
   nvl(c11.criteria_11, ' '), 
   nvl(c12.criteria_12, ' ')
from security as a,
 left outer join security as c1
        on (c1.employeeId = a.employeeId)
 left outer join security as c2
        on (c2.employeeId = a.employeeId and 
            c2.criteria_1 = a.criteria_1)
 left outer join security as c3
        on (c3.employeeId = a.employeeId and 
            c3.criteria_1 = a.criteria_1 and 
            c3.criteria_2 = a.criteria_2)
 left outer join security as c4
        on (c4.employeeId = a.employeeId and 
            c4.criteria_1 = a.criteria_1 and 
            c4.criteria_2 = a.criteria_2 and
            c4.criteria_3 = a.criteria_3)
 left outer join security as c5
        on (c5.employeeId = a.employeeId and 
            c5.criteria_1 = c1.criteria_1 and 
            c5.criteria_2 = a.criteria_2 and
            c5.criteria_3 = a.criteria_3 and
            c5.criteria_4 = a.criteria_4)
 left outer join security as c6
        on (c6.employeeId = a.employeeId and 
            c6.criteria_1 = c1.criteria_1 and 
            c6.criteria_2 = a.criteria_2 and
            c6.criteria_3 = a.criteria_3 and
            c6.criteria_4 = a.criteria_4 and
            c6.criteria_5 = a.criteria_5)
 left outer join security as c7
        on (c7.employeeId = a.employeeId and 
            c7.criteria_1 = c1.criteria_1 and 
            c7.criteria_2 = a.criteria_2 and
            c7.criteria_3 = a.criteria_3 and
            c7.criteria_4 = a.criteria_4 and
            c7.criteria_5 = a.criteria_5 and
            c7.criteria_6 = a.criteria_6)
 left outer join security as c8
        on (c8.employeeId = a.employeeId and 
            c8.criteria_1 = c1.criteria_1 and 
            c8.criteria_2 = a.criteria_2 and
            c8.criteria_3 = a.criteria_3 and
            c8.criteria_4 = a.criteria_4 and
            c8.criteria_5 = a.criteria_5 and
            c8.criteria_6 = a.criteria_6 and
            c8.criteria_7 = a.criteria_7)
 left outer join security as c9
        on (c9.employeeId = a.employeeId and 
            c9.criteria_1 = c1.criteria_1 and 
            c9.criteria_2 = a.criteria_2 and
            c9.criteria_3 = a.criteria_3 and
            c9.criteria_4 = a.criteria_4 and
            c9.criteria_5 = a.criteria_5 and
            c9.criteria_6 = a.criteria_6 and
            c9.criteria_7 = a.criteria_7 and
            c9.criteria_8 = a.criteria_8)
 left outer join security as c10
        on (c10.employeeId = a.employeeId and 
            c10.criteria_1 = c1.criteria_1 and 
            c10.criteria_2 = a.criteria_2 and
            c10.criteria_3 = a.criteria_3 and
            c10.criteria_4 = a.criteria_4 and
            c10.criteria_5 = a.criteria_5 and
            c10.criteria_6 = a.criteria_6 and
            c10.criteria_7 = a.criteria_7 and
            c10.criteria_8 = a.criteria_8 and
            c10.criteria_9 = a.criteria_9)
 left outer join security as c11
        on (c11.employeeId = a.employeeId and 
            c11.criteria_1 = c1.criteria_1 and 
            c11.criteria_2 = a.criteria_2 and
            c11.criteria_3 = a.criteria_3 and
            c11.criteria_4 = a.criteria_4 and
            c11.criteria_5 = a.criteria_5 and
            c11.criteria_6 = a.criteria_6 and
            c11.criteria_7 = a.criteria_7 and
            c11.criteria_8 = a.criteria_8 and
            c11.criteria_9 = a.criteria_9 and
            c11.criteria_10 = a.criteria_10)
 left outer join security as c12
        on (c12.employeeId = a.employeeId and 
            c12.criteria_1 = c1.criteria_1 and 
            c12.criteria_2 = a.criteria_2 and
            c12.criteria_3 = a.criteria_3 and
            c12.criteria_4 = a.criteria_4 and
            c12.criteria_5 = a.criteria_5 and
            c12.criteria_6 = a.criteria_6 and
            c12.criteria_7 = a.criteria_7 and
            c12.criteria_8 = a.criteria_8 and
            c12.criteria_9 = a.criteria_9 and
            c12.criteria_10 = a.criteria_10 and
            c12.criteria_11 = a.criteria_11);

Answer 3

If your security table structure is something like

Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
EMPLOYEEID                                         VARCHAR2(9)
CRITERIA_1_VALID_VALUE                             VARCHAR2(15)
CRITERIA_2_VALID_VALUE                             VARCHAR2(15)
CRITERIA_3_VALID_VALUE                             VARCHAR2(15)

with data

EMPLOYEEI CRITERIA_1_VALI CRITERIA_2_VALI CRITERIA_3_VALI
--------- --------------- --------------- ---------------
alex      crit 1a         crit 1a 2a      crit 1a 2a 3a
alex      crit 1a         crit 1a 2b      crit 1a 2b 3a
alex      crit 1a         crit 1a 2c      crit 1a 2c 3a
alex      crit 1a         crit 1a 2c      crit 1a 2c 3b
alex      crit 1b         crit 1b 2a      crit 1b 2a 3a
alex      crit 1b         crit 1b 2b      crit 1b 2b 3a
alex      crit 1b         crit 1b 2c      crit 1b 2c 3a
alex      crit 1c         crit 1c 2a      crit 1c 2a 3a

then does this give the result you need?

create or replace type t_crit_values as table of varchar2(15)
/

show errors

create or replace function get_criteria(v_EmployeeID in varchar2,
    v_Level in number,
    v_Criteria_1 in varchar2 default ' ',
    v_Criteria_2 in varchar2 default ' ',
    v_Criteria_3 in varchar2 default ' ')
return t_crit_values as

    cursor c_values is
        select distinct(case v_Level
            when 1 then Criteria_1_valid_Value
            when 2 then Criteria_2_valid_Value
            when 3 then Criteria_3_valid_Value
        end) value
        from security
        where EmployeeID = v_EmployeeID
        and (v_Criteria_1 = ' ' or Criteria_1_valid_Value = v_Criteria_1)
        and (v_Criteria_2 = ' ' or Criteria_2_valid_Value = v_Criteria_2)
        and (v_Criteria_3 = ' ' or Criteria_3_valid_Value = v_Criteria_3);

    l_crit_values t_crit_values;
    i number;
begin
    l_crit_values := t_crit_values();

    for r_value in c_values loop
        l_crit_values.EXTEND;
        l_crit_values(l_crit_values.LAST) := r_value.value;
    end loop;

    return l_crit_values;
end;
/

show errors

Then call the function, each time passing in the level you need and the selected values from all higher levels (which may be ' '). Something like

// first level
select * from table(get_criteria('alex', 1));

COLUMN_VALUE
---------------
crit 1a
crit 1b
crit 1c

// second level with 'crit 1b' selected
select * from table(get_criteria('alex', 2, 'crit 1b'));

COLUMN_VALUE
---------------
crit 1b 2a
crit 1b 2b
crit 1b 2c

// second level with 'crit 1c' selected
select * from table(get_criteria('alex', 2, 'crit 1c'));

COLUMN_VALUE
---------------
crit 1c 2a

// third level with 'crit 1b' and 'crit 1b 2a' selected
select * from table(get_criteria('alex', 3, 'crit 1b', 'crit 1b 2a'));

COLUMN_VALUE
---------------
crit 1b 2a 3a

// third level with 'crit 1b' and 'all values' selected
select * from table(get_criteria('alex', 3, 'crit 1b', ' '));

COLUMN_VALUE
---------------
crit 1b 2a 3a
crit 1b 2b 3a
crit 1b 2c 3a

I've only gone to three levels for brevity but it would be easy to expand. Or have I not understood what you're trying to do?

Answer 4

Say there were only 3 criteria you want a table like this?

id c1 c2 c3
0 a b c
1 a b space
2 a space c
3 a space space
4 space b c
5 space b space
6 space space c
7 space space space

If you create such a table using sqlloader for example including an id column from 0 to 2^12 -1 , put spaces into all the criteria columns then you could update it using arithmetic:
update temp set c1 = (select criteria1 ...) where mod(id,2) < 1;
update temp set c2 = (select criteria2 ...) where mod(id,4) < 2;
update temp set c3 = (select criteria3 ...) where mod(id,8) < 4;

Does seem like a weird requirement.

Answer 5

So in the end it did come down to a performance issue. I created a table that held the binary representation of 2^10 integers in reverse (litte-endian, if you will).

DECBIN:

decimal   binary
0         0000000000
1         1000000000
2         0100000000
...
1023      1111111111

I then cartesian join this to the security table and decode each bit to get the correct value.

So the sql looks something like this:

    SELECT DISTINCT
       t.employeeID,
       DECODE (SUBSTR (x.binary,  1, 1), 0, ' ', t.c1)  AS crit1,
       DECODE (SUBSTR (x.binary,  2, 1), 0, ' ', t.c2)  AS crit2,
       DECODE (SUBSTR (x.binary,  3, 1), 0, ' ', t.c3)  AS crit3,
       DECODE (SUBSTR (x.binary,  4, 1), 0, ' ', t.c4)  AS crit4,
       DECODE (SUBSTR (x.binary,  5, 1), 0, ' ', t.c5)  AS crit5,
       DECODE (SUBSTR (x.binary,  6, 1), 0, ' ', t.c6)  AS crit6,
       DECODE (SUBSTR (x.binary,  7, 1), 0, ' ', t.c7)  AS crit7,
       DECODE (SUBSTR (x.binary,  8, 1), 0, ' ', t.c8)  AS crit8,
       DECODE (SUBSTR (x.binary,  9, 1), 0, ' ', t.c9)  AS crit9,
       DECODE (SUBSTR (x.binary, 10, 1), 0, ' ', t.c10) AS crit10,
       DECODE (SUBSTR (x.binary, 10, 1), 0, 'Choose All',t.c11) AS crit10Descr
  FROM Security t, DECBIN x
 WHERE TO_NUMBER (x.decimal) BETWEEN 0 AND POWER (2, 10) - 1

This is faster by a factor of 10. Thanks @JD_55 for getting me tho think about the problem in a new way.