oracle - SQL Conditional SELECT with COALESCE (possible issue with grouping or table joins) -

apologies in advance length of question:

i working on query display data pull different tables depending on facility requesting data.

we maintain many statistics individual product lines, , each facility able view data on line level; facilities organize products in groups department , group departments "value stream" (just layer in hierarchy).

what able display data in highest group available (facilities value streams list of streams facility, facilities have product lines view list of products, etc).

i have used coalesce function this, interested in testing null values determine organizational group facility uses.

unfortunately, necessary data on 6 different tables, requiring few joins. have added additional parameters each join attempt ensure joins correct.

the query functions correctly facility not use streams or departments (1 in below example); list of products , respective scores generated.

the query not provide desired results other levels. facility organizes value streams (2), desired result list of facility's streams , respective scores. instead, list of product lines again generated.

here query:

select * (select a.*, rownum rnum (  select /*stream_id, dept_id ,line,*/    coalesce(vs.id, dt.id, slr.line) "id",    coalesce(vs.name, dt.name, slr.name) "name",    case when safety_value = 0 'green' when safety_value > 0 'red' else 'white' end color  xx_sqdc_lines_ref slr   left join sqdc_department_details dtd on slr.site=dtd.facility_id , slr.line=dtd.line_id left join sqdc_value_stream_details vsd on dtd.facility_id= vsd.facility_id , dtd.line_id=vsd.line_id left join sqdc_safety_max kpi on vsd.facility_id=kpi.facility_id , vsd.line_id=kpi.line_id left join sqdc_departments dt on kpi.facility_id= dt.facility_id , dtd.dept_id=dt.id left join sqdc_value_streams vs on dt.facility_id= vs.facility_id , dt.vs_id=vs.id (site = 2) order name  )a) 

tables (sample values; there additional columns have left out don't relate query):

xx_sqdc_lines_ref

    line name    site     1    table   1     2    lamp    1     3    screen  2     4    forcep  2     5    brush   2     6    camera2 2     7    screen2 2     8    forcep2 2     9    brush2  2     10   camera2 2 

sqdc_department_details

    dept_id line_id facility_id     1       3       2     1       4       2     2       5       2      2       6       2     3       7       2      3       8       2     4       9       2      4       10      2 

sqdc_value_stream_details

    stream_id line_id facility_id     1         3       2     1         4       2     1         5       2      1         6       2     2         7       2      2         8       2     2         9       2      2         10      2 

sqdc_safety_max

    facility_id line_id actual_date safety_value     1           1       31-jan-16   0     1           2       31-jan-16   0     2           3       31-jan-16   0     2           4       24-jan-16   10     2           5       24-jan-16   0     2           7       24-jan-16   0     2           9       24-jan-16   0 

sqdc_departments

    id name   facility_id vs_id     1  dept 1 2           1     2  dept 2 2           1     3  dept 3 2           2     4  dept 4 2           2 

sqdc_value_streams

    id name   facility_id     1  vs 1   2     2  vs 2   2 

thanks in advance can point me in right direction.

edited add expected results:

site 1 (no stream):

    id name  color rnum     1  table green 1     2  lamp  green 2 

site 2 (with stream):

    id name color rnum     1  vs 1 green 1     2  vs 2 white 2 

i know i'm missing additional functions or operators make work way i'd it, let's see if can rephrase expectations:

i display id, name , safety value color code highest level (stream, dept, or product) used @ facility , on latest day information provided.

per vs , dept tables, site 1 not have streams or depts, list of products should populate. data provided, site 1 display both products individually green values because both products data 31-jan-16 , values 0. if there product under site 1 data before date, color white regardless of actual safety value.

site 2 ideally display list of 2 value streams (since highest level).

the latest date site 2 31-jan-16. in product lines under vs 1, line_id=3 has date (the other lines have earlier dates). average safety value 0 (the 10 line_id=4 not part of average due date), , color green.

all safety values product lines under vs 2 before 31-jan-16, , not calculated in average, result white.

hope helps clear things up.

single case queries per request @hogan :

value stream level query (like site 2):

    select *                   (select a.*, rownum rnum                       (select stream_id, name,  case when sum(safety_value) = 0 'green'  when sum(safety_value) > 0 'red' else 'white' end color      sqdc_value_streams vls  left join sqdc_value_stream_details vs on vls.id = vs.stream_id left join sqdc_safety_max kpi on vs.line_id=kpi.line_id       (vs.facility_id = 2     )     group name, stream_id     order name)     ) 

department level query (no site in example, work same way):

    select *                   (select a.*, rownum rnum                       (select dept_id, name,  case when sum(safety_value) = 0 'green'  when sum(safety_value) > 0 'red' else 'white' end color      sqdc_departments dpts  left join sqdc_department_details dt on dpts.id = dt.dept_id left join sqdc_safety_max kpi on dt.line_id=kpi.line_id      (dt.facility_id = 7986128121911792     )     group name, dept_id     order name)     ) 

product level query (like site 1):

    select *                   (select a.*, rownum rnum                       (select line, name,  case when safety_value = 0 'green'  when      safety_value > 0 'red' else 'white' end color      xx_sqdc_lines_ref slr  left join sqdc_safety_max kpi on     slr.line=kpi.line_id       (site = 1     )     order name)     ) 

the goal combine these 3 queries 1 , ensure highest org level (stream, dept, product) facility displayed.

given 3 queries not hard merge them rules describe.

i know, not sexy coalesce , lots of fancy joins, think best way go because clear , easy maintain.

with stream_query (   select 1 priority, stream_id, null dept_id, null line, name, color, rownum rnum   (     select stream_id, name,  case when sum(safety_value) = 0 'green'  when sum(safety_value) > 0 'red' else 'white' end color     sqdc_value_streams vls     left join sqdc_value_stream_details vs on vls.id = vs.stream_id     left join sqdc_safety_max kpi on vs.line_id=kpi.line_id      vs.facility_id = 2     group name, stream_id     order name   ) ), dept_query (   select 2 priority, null stream_id, dept_id, null line, name, color, rownum rnum    (     select dept_id, name,  case when sum(safety_value) = 0 'green'  when sum(safety_value) > 0 'red' else 'white' end color      sqdc_departments dpts       left join sqdc_department_details dt on dpts.id = dt.dept_id      left join sqdc_safety_max kpi on dt.line_id=kpi.line_id     dt.facility_id = 7986128121911792     group name, dept_id     order name   ) ), prod_query (   select 3 priority, null stream_id, null dept_id, line, name, color, rownum rnum   (     select line, name,  case when safety_value = 0 'green'  when      safety_value > 0 'red' else 'white' end color      xx_sqdc_lines_ref slr      left join sqdc_safety_max kpi on     slr.line=kpi.line_id      site = 1     order name   ) ), merged (   select a.*, min(priority) on () highest    (     select * stream_query       union     select * dept_query       union     select * prod_query   )   ) select * merged priority = highest 

---

your idea find did not set joins correctly. needed left join xx_sqdc_lines_ref or sqdc_department_details -- not doing this, joining prior join

select /*stream_id, dept_id ,line,*/    coalesce(vs.id, dt.id, slr.line) "id",    coalesce(vs.name, dt.name, slr.name) "name",    case when safety_value = 0 'green' when safety_value > 0 'red' else 'white' end color,   rownum rnum xx_sqdc_lines_ref slr   left join sqdc_department_details dtd   on slr.site=dtd.facility_id , slr.line=dtd.line_id left join sqdc_value_stream_details vsd on slr.site=vsd.facility_id , slr.line=vsd.line_id left join sqdc_safety_max kpi           on slr.site=kpi.facility_id , slr.line=kpi.line_id left join sqdc_departments dt           on slr.site=dt.facility_id ,  dtd.dept_id=dt.id left join sqdc_value_streams vs         on slr.site=vs.facility_id ,  dtd.vs_id=vs.id (site = 2) order name 

i removed sub-query stuff since not needed , makes more complicated (imo)