CODEWITHSUNDEEP
sqloracle-database
O P
O P

Reputation: 2365

Replace hard coded values with data from table

Currently, I have 3 affiliations hard-coded in a query. They serve as a heirarchy: 1 = Faculty, 2 = Staff, 3 = Student. If a user from the affiliations_tbl table has more than one affiliation (example: a Staff member who is also a Student), it will use their Staff affiliation since it is higher on the heirarchy that is defined with the partition by and decode().

SELECT x2.emplid,
       scc_afl_code
  FROM (SELECT x.emplid,
               scc_afl_code,
               row_number() over(partition BY x.emplid ORDER BY x.affil_order) r
          FROM (SELECT t.emplid,
                       scc_afl_code,
                       DECODE(scc_afl_code,
                              'FACULTY',
                              1,
                              'STAFF',
                              2,
                              'STUDENT',
                              3,
                              999) affil_order
                  FROM affiliations_tbl t
                 WHERE t.scc_afl_code IN
                       (SELECT a.scc_afl_code
                          FROM affiliation_groups_tbl a
                         WHERE a.group = 'COLLEGE')) x) x2
 WHERE x2.r = 1;

I have created a table that will store affiliation groups affiliation_groups_tbl so I can scale this by adding data to the table, rather than changing the hard-coded values in this query. Example: Instead of adding 'CONSULTANT', 4 to the decode() list, I would add it to the table, so I wouldn't have to modify the SQL.

scc_afl_code |  group  | group_name | sort_order 
-------------+---------+------------+-----------
FACULTY      | COLLEGE | Faculty    |     1
STAFF        | COLLEGE | Staff      |     2
STUDENT      | COLLEGE | Student    |     3

I've already updated the latter half of the query to only select scc_afl_code that are in the COLLEGE_GROUP group. How can I properly update the first part of the query to use the table as a hierarchy?

Upvotes: 1

Views: 1001

Answers (2)

0xdb
0xdb

Reputation: 3697

Try a piece of code below instead decode in the select clause of your statement:

coalesce((
    select g.sort_order 
    from affiliation_groups_tbl g
    where g.scc_afl_code = t.scc_afl_code ), 999)

Upvotes: 2

Elkhan Ibrahimov
Elkhan Ibrahimov

Reputation: 234

You can try like that

    create table dictionary
    (id number,
    code varchar2(32),
    name varchar2(32),
    sort number);

    insert into dictionary (id, code, name, sort) values (16, 'B', 'B name', 1);
    insert into dictionary (id, code, name, sort) values (23, 'A', 'A name', 2);
    insert into dictionary (id, code, name, sort) values (15, 'C', 'C name', 4);
    insert into dictionary (id, code, name, sort) values (22, 'D', 'D name', 3);

    select partition,
           string,
           decode(string, 'B', 1, 'A', 2, 'D', 3, 'C', 4, 999) decode,
           row_number() over(partition by partition order by decode(string, 'B', 1, 'A', 2, 'D', 3, 'C', 4, 999)) ordering
      from (select mod(level, 3) partition, chr(65 + mod(level, 5)) string
              from dual
            connect by level <= 8)
    minus

    -- Alternate --
    select partition,
           string,
           nvl(t.sort, 999) nvl,
           row_number() over(partition by partition order by nvl(t.sort, 999)) ordering
      from (select mod(level, 3) partition, chr(65 + mod(level, 5)) string
              from dual
            connect by level <= 8) r
      left join dictionary t
        on t.code = r.string;

Upvotes: 1

Related Questions