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

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

Apologies in advance for the length of the question:
I am working on a query to display data that will pull from different tables depending on the facility that is requesting the data.
We maintain many statistics from individual product lines, and each facility would like to be able to view the data on a line level; however some facilities also organize their products in groups by department and then even group departments by “value stream” (just another layer in the hierarchy).
What I would like to be able to do is display the data in the highest group available (facilities with value streams get a list of streams for the facility, facilities that only have product lines view a list of products, etc).
I have used the COALESCE function to help with this, as I am interested in testing for NULL values to determine what organizational group the facility uses.
Unfortunately, the necessary data is on 6 different tables, requiring a few joins. I have added additional parameters to each join to attempt to ensure that the joins are correct.
The query functions correctly with a facility that does not use streams or departments (1 in the below example); a list of products and their respective scores is generated.
The query does not provide the desired results for the other levels. For a facility that organizes with value streams (2), the desired result is a list of that facility’s streams and their respective scores. Instead, a list of product lines is again generated.
Here is my query:
select * from
(select a.*, rownum rnum from (

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 then ‘GREEN’ when SAFETY_VALUE > 0 then ‘RED’ else ‘WHITE’ end AS COLOR
FROM XX_SQDC_LINES_REF SLR
LEFT JOIN SQDC_DEPARTMENT_DETAILS DTD on SLR.SITE=DTD.FACILITY_ID AND SLR.LINE=DTD.LINE_ID
LEFT JOIN SQDC_VALUE_STREAM_DETAILS VSD ON DTD.FACILITY_ID= VSD.FACILITY_ID AND DTD.LINE_ID=VSD.LINE_ID
LEFT JOIN SQDC_SAFETY_MAX KPI ON VSD.FACILITY_ID=KPI.FACILITY_ID AND VSD.LINE_ID=KPI.LINE_ID
LEFT JOIN SQDC_DEPARTMENTS DT ON KPI.FACILITY_ID= DT.FACILITY_ID and dtd.dept_id=dt.id
LEFT JOIN SQDC_VALUE_STREAMS VS ON DT.FACILITY_ID= VS.FACILITY_ID AND DT.VS_ID=VS.ID
WHERE (SITE = 2)
ORDER by NAME

)a)

Tables (sample values; there are also additional columns that I have left out as they don’t relate to this 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 to anyone who can help point me in the right direction.
Edited to 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 some additional functions or operators to make this work the way I’d like it, so let’s see if I can rephrase the expectations:
I would like to display the ID, name and safety value color code for the highest level (stream, dept, or product) used at a facility and on the latest day information was provided.
Per the VS and Dept tables, Site 1 does not have any streams or depts, so a list of products should populate. With the data provided, Site 1 would display both products individually with GREEN values because both products data is from 31-Jan-16 and the values are 0. If there were another product under Site 1 with data from before that date, the color would be WHITE regardless of the actual safety value.
Site 2 would ideally display a list of just the two value streams (since that is the highest level).
The latest date for Site 2 is 31-Jan-16. In product lines under VS 1, only Line_ID=3 has this date (the other lines have earlier dates). So the average safety value would be 0 (the 10 for Line_ID=4 would not be part of the average due to the early date), and the color is then GREEN.
All safety values for product lines under VS 2 were before 31-Jan-16, and thus not calculated in the average, so the result is white.
Hope this helps to clear things up.
Single case queries per request from @Hogan :
Value stream level query (like site 2):
select *
from
(select a.*, rownum rnum
from
(SELECT stream_id, NAME, case when SUM(SAFETY_VALUE) = 0 then ‘GREEN’ when SUM(SAFETY_VALUE) > 0 then ‘RED’ else ‘WHITE’ end AS COLOR
FROM 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

WHERE (vs.facility_id = 2
)
group by name, STREAM_id
ORDER by NAME) a
)

Department level query (no site like this in example, but it would work the same way):
select *
from
(select a.*, rownum rnum
from
(SELECT dept_id, NAME, case when SUM(SAFETY_VALUE) = 0 then ‘GREEN’ when SUM(SAFETY_VALUE) > 0 then ‘RED’ else ‘WHITE’ end AS COLOR
FROM 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

WHERE (DT.facility_id = 7986128121911792
)
group by name, DEPT_id
ORDER by NAME) a
)

Product level query (like site 1):
select *
from
(select a.*, rownum rnum
from
(SELECT line, NAME, case when SAFETY_VALUE = 0 then ‘GREEN’ when SAFETY_VALUE > 0 then ‘RED’ else ‘WHITE’ end AS COLOR
FROM XX_SQDC_LINES_REF SLR LEFT JOIN SQDC_SAFETY_MAX KPI ON SLR.LINE=KPI.LINE_ID

WHERE (SITE = 1
)
ORDER by NAME) a
)

The goal is to combine these three queries into one and to ensure that only the highest org level (stream, dept, product) for the facility is displayed.

Solutions/Answers:

Solution 1:

Given your 3 queries it is not hard to merge them with the rules you describe.

I know, this is not as sexy as COALESCE and lots of fancy joins, but I think it is probably the best way to go because it is very clear and thus would be easy to maintain.

WITH stream_query AS
(
  SELECT 1 AS PRIORITY, stream_id, null as dept_id, null as line, NAME, COLOR, rownum rnum
  FROM (
    SELECT stream_id, NAME,  case when SUM(SAFETY_VALUE) = 0 then 'GREEN'  when SUM(SAFETY_VALUE) > 0 then 'RED' else 'WHITE' end AS COLOR
    FROM 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 
    WHERE vs.facility_id = 2
    group by name, STREAM_id
    ORDER by NAME
  ) a
), dept_query AS
(
  SELECT 2 AS PRIORITY, null as stream_id, dept_id, null as line, NAME, COLOR, rownum rnum 
  FROM (
    SELECT dept_id, NAME,  case when SUM(SAFETY_VALUE) = 0 then 'GREEN'  when SUM(SAFETY_VALUE) > 0 then 'RED' else 'WHITE' end AS COLOR 
    FROM 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
    WHERE DT.facility_id = 7986128121911792
    group by name, DEPT_id
    ORDER by NAME
  ) a
), prod_query AS
(
  SELECT 3 AS PRIORITY, null as stream_id, null as dept_id, line, NAME, COLOR, rownum rnum
  FROM (
    SELECT line, NAME,  case when SAFETY_VALUE = 0 then 'GREEN'  when      SAFETY_VALUE > 0 then 'RED' else 'WHITE' end AS COLOR 
    FROM XX_SQDC_LINES_REF SLR 
    LEFT JOIN SQDC_SAFETY_MAX KPI ON     SLR.LINE=KPI.LINE_ID 
    WHERE SITE = 1
    ORDER by NAME
  ) a
), merged AS
(
  SELECT a.*, MIN(PRIORITY) OVER () AS HIGHEST 
  FROM (
    SELECT * FROM stream_query
      UNION ALL
    SELECT * FROM dept_query
      UNION ALL
    SELECT * FROM prod_query
  ) a  
)
SELECT *
FROM merged
WHERE PRIORITY = HIGHEST

Your idea was find but you did not set up your joins correctly. You needed to left join from XX_SQDC_LINES_REF or SQDC_DEPARTMENT_DETAILS — you were not doing this, you were joining to the 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 then 'GREEN' when SAFETY_VALUE > 0 then 'RED' else 'WHITE' end AS COLOR,
  rownum rnum
FROM XX_SQDC_LINES_REF SLR  
LEFT JOIN SQDC_DEPARTMENT_DETAILS DTD   on SLR.SITE=DTD.FACILITY_ID AND SLR.LINE=DTD.LINE_ID
LEFT JOIN SQDC_VALUE_STREAM_DETAILS VSD ON SLR.SITE=VSD.FACILITY_ID AND SLR.LINE=VSD.LINE_ID
LEFT JOIN SQDC_SAFETY_MAX KPI           ON SLR.SITE=KPI.FACILITY_ID AND SLR.LINE=KPI.LINE_ID
LEFT JOIN SQDC_DEPARTMENTS DT           ON SLR.SITE=DT.FACILITY_ID and  DTD.dept_id=DT.id
LEFT JOIN SQDC_VALUE_STREAMS VS         ON SLR.SITE=VS.FACILITY_ID AND  DTD.VS_ID=VS.ID
WHERE (SITE = 2)
ORDER by NAME

I also removed all the sub-query stuff since that is not needed and just makes it more complicated (IMO)

Solution 2:

With much help from @Hogan here is the finished query:

SELECT COALESCE(STREAM_ID, DEPT_ID, TO_CHAR(LINE)) ID, NAME,COLOR
FROM(
WITH  stream_query AS
(
  SELECT 1 AS PRIORITY, stream_id, null as dept_id, null as line, NAME, COLOR, rownum rnum
  FROM (
    SELECT stream_id, NAME,  case when SUM(SAFETY_VALUE) = 0 then 'GREEN' when SUM(SAFETY_VALUE) > 0 then 'RED' else 'WHITE' end AS COLOR
    FROM SQDC_VALUE_STREAMS VLS
    LEFT JOIN SQDC_VALUE_STREAM_DETAILS VS ON VLS.ID = VS.STREAM_ID
    LEFT JOIN (select line_id, safety_value, actual_date from SQDC_SAFETY_MAX where actual_date in (select max(actual_date) from SQDC_SAFETY_MAX)) KPI ON VS.LINE_ID=KPI.LINE_ID 
    WHERE vs.facility_id = [replace1]
    group by name, STREAM_id
    ORDER by NAME
  ) a
), dept_query AS
(
  SELECT 2 AS PRIORITY, null as stream_id, dept_id, null as line, NAME, COLOR, rownum rnum 
  FROM (
    SELECT dept_id, NAME,  case when SUM(SAFETY_VALUE) = 0 then 'GREEN'  when SUM(SAFETY_VALUE) > 0 then 'RED' else 'WHITE' end AS COLOR 
    FROM SQDC_DEPARTMENTS DPTS  
    LEFT JOIN SQDC_DEPARTMENT_DETAILS DT ON DPTS.ID = DT.Dept_ID 
    LEFT JOIN (select line_id, safety_value, actual_date from SQDC_SAFETY_MAX where actual_date in (select max(actual_date) from SQDC_SAFETY_MAX)) KPI ON DT.LINE_ID=KPI.LINE_ID
    WHERE DT.facility_id = [replace1]
    group by name, DEPT_id
    ORDER by NAME
  ) a
), prod_query AS
(
  SELECT 3 AS PRIORITY, null as stream_id, null as dept_id, line, NAME, COLOR, rownum rnum
  FROM (
    SELECT line, NAME,  case when SAFETY_VALUE = 0 then 'GREEN'  when      SAFETY_VALUE > 0 then 'RED' else 'WHITE' end AS COLOR 
    FROM XX_SQDC_LINES_REF SLR 
    LEFT JOIN SQDC_SAFETY_MAX KPI ON     SLR.LINE=KPI.LINE_ID 
    WHERE SITE =[replace1]
    ORDER by NAME
  ) a
),merged AS
(
  SELECT a.*, MIN(PRIORITY) OVER () AS HIGHEST 
  FROM (
    SELECT * FROM stream_query
      UNION ALL
    SELECT * FROM dept_query
      UNION ALL
    SELECT * FROM prod_query
  ) a  
)
SELECT *
FROM merged
WHERE PRIORITY = HIGHEST
)
where rownum <=12
AND rnum >=1

I added a bit more to select for the latest available date and to limit the number of rows and columns, but this works for both test cases and for my actual data. Thanks again, @Hogan.

References