Tutorial :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.


Solution:1

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?


Solution:2

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.


Solution:3

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);  


Solution:4

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?


Solution: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.


Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com
Previous
Next Post »