SQL: How can I get the first_value ignoring nulls as an aggregate?

Question

I have a table that is being used as a hierarchical data set of settings for employees, departments, and company. The employees can work in any department and when they do they inherit the departments settings if they have non specified. More specific settings win and I want to write a query to get the settings for a particular employee/department pair.

The settings table has an employeeid which can be null and departmentid which also can be null. If both are null that is the row for the company-wide settings. There is a unique constraint on "nvl(employeeid,0) and nvl(departmentid,0)".

Sample data set for settings:

employeeid    departmentid    address        phone
null          null            123 Corp Dr.   800-555-1212
10            null            1 ABC Ave.     null
null          1               2 Dept Rd.     null
null          2               3 Dept Rd.     617-555-1212           

When running my query with employee 10 and department 1, I should get one row: address = 1 ABC Ave, phone=800-555-1212

For employee 10 and department 2 I should get the updated phone number: address = 1 ABC Ave, phone = 617-555-1212

The best I could do so far uses first_value, ignoring nulls, over the table and ordering by a precedence I added. The problem is that first_value over is not aggregate so I need a separate outer query to select a particular precedence. This seems to me like something I should be able to make aggregate.

select
    address,
    phone
from (
    select
        precedence,
        first_value(address ignore nulls) over (order by precedence) address,
        first_value(phone ignore nulls) over (order by precedence) phone
    from (
        select
            1 precedence,
            *
        from
            settings
        where
            settings.employeeid = ?
            and settings.departmentid is null
        union
        select
            2 precedence,
            *
        from
            settings
        where
            settings.departmentid = ?
            and settings.employeeid is null
        union
        select
            3 precedence,
            *
        from
            settings
        where
            settings.departmentid is null
            and settings.employeeid is null
    )
)
where
    precedence = 3

This gets the right answer but I feel as though there should be a way to roll up the first_values as aggregates in the middle query and drop the outer query as well as potentially just rely on the explicit ordering of the unions rather than introducing a precedence column, though that is less important.

I'm using Oracle 11 for this.

Answer 1

You can do this with keep dense rank and a single level of inline view, which only needs to hit the table once - instead of unioning you can use a case to decide the preference level of each relevant row in the base table:

select min(address) keep (dense_rank first
    order by case when address is null then 1 else 0 end, preference) as address,
  min(phone) keep (dense_rank first
    order by case when phone is null then 1 else 0 end, preference) as phone
from (
  select s.address, s.phone, case when s.employeeid is not null then 1
    when s.employeeid is null and s.departmentid is not null then 2
    else 3 end as preference
  from settings s
  where s.employeeid = 10
  or (s.employeeid is null and s.departmentid = 2)
  or (s.employeeid is null and s.departmentid is null)
);

ADDRESS      PHONE      
------------ ------------
1 ABC Ave.   617-555-1212

... changing the = 10 and = 2 to bind variable placeholders, of course.

Passing in employee 1 and department ten gets:

ADDRESS      PHONE      
------------ ------------
1 ABC Ave.   800-555-1212

You could still use first_value instead:

first_value(address) over (order by case when address is null then 1 else 0 end,
  preference) as address,

... but then you have to use distinct to remove duplicates.

Answer 2

One option that avoids the three unions to set the precedence is as follows. It needs to push it all down into a nested query to get rid of the null on the first_value, but it does it all in one pass through the table

(note that I'm using the Oracle colon prefix for variable replacement on :emp and :dept placeholders):

WITH data as (
select null emp_id, null dept_id, '123 Corp Dr.' addr,   '800-555-1212' phn from dual union all
select 10,          null,         '1 ABC Ave.',           null from dual union all
select null,        1,            '2 Dept Rd.',           null from dual union all
select null ,       2,            '3 Dept Rd.',           '617-555-1212'    from dual )
SELECT DISTINCT addr, phn
FROM (
    SELECT first_value(addr ignore nulls) over (order by precedence) as addr
         , first_value(phn ignore nulls) over (order by precedence)  as phn
    FROM (
        select CASE WHEN emp_id = :emp then 1
                    when dept_id = :dept then 2
                    WHEN emp_id is null and dept_id is null then 3 end as precedence
              , addr
              , phn       
        from data
        )
    where precedence is not null
    )
where addr is not null and phn is not null;