Fill data gaps - UNION, PARTITION BY, or JOIN?

Question

Problem

There are data gaps that need to be filled. Would like to avoid UNION or PARTITION BY if possible.

Query Statement

The select statement reads as follows:

SELECT
  count( r.incident_id ) AS incident_tally,
  r.severity_cd,
  r.incident_typ_cd
FROM
  report_vw r
GROUP BY
  r.severity_cd, r.incident_typ_cd
ORDER BY
  r.severity_cd,
  r.incident_typ_cd

Data Sources

The severity codes and incident type codes are from:

• severity_vw
• incident_type_vw

The columns are:

• incident_tally
• severity_cd
• incident_typ_cd

Actual Result Data

36  0   ENVIRONMENT
1   1   DISASTER
27  1   ENVIRONMENT
4   2   SAFETY
1   3   SAFETY

Required Result Data

36  0   ENVIRONMENT
0   0   DISASTER
0   0   SAFETY
27  1   ENVIRONMENT
0   1   DISASTER
0   1   SAFETY
0   2   ENVIRONMENT
0   2   DISASTER
4   2   SAFETY
0   3   ENVIRONMENT
0   3   DISASTER
1   3   SAFETY

Question

How would you use UNION, PARTITION BY, or LEFT JOIN to fill in the zero counts?

Answer 1

The only way i can think of is to further normalise that table and use a left join.

Answer 2

The easiest would be if you had incident type and incident severity in another table.

SELECT COALESCE(sub.incident_tally), s.severity_cd, i.incident_type_cd
FROM incident_type i
CROSS JOIN incident_severity s
LEFT JOIN (
   ... your original sql statement...
) sub ON i.incident_typ_cd = sub.incident_type_cd
  AND s.severity_cd = sub.severity_cd

And if you didn't have those tables, you could do something like this. I don't understand why the no UNION restriction. But this works.

CREATE TYPE VARCHAR_TABLE AS TABLE OF VARCHAR2(60);
CREATE TYPE NUMBER_TABLE AS TABLE OF NUMBER;

WITH inc AS (
  SELECT /*+ CARDINALITY(nt1 3) */ column_value AS incident_type_cd
  FROM TABLE(varchar_table('ENVIRONMENT', 'DISASTER', 'SAFETY')) nt1
),
sev AS (
  SELECT /*+ CARDINALITY(nt2 4) */ column_value AS severity_cd
  FROM TABLE(number_table(0,1,2,3)) nt2
) 
SELECT *
FROM inc
CROSS JOIN sev
LEFT JOIN (
   ... your original sql statement...
) sub ON i.incident_typ_cd = sub.incident_type_cd
  AND s.severity_cd = sub.severity_cd

4

Answer 3

How about something like

SELECT  COUNT(r.incident_id),
        crsjn.severity_cd,
        crsjn.incident_typ_cd
FROM    (
            SELECT  severity_cd,
                    incident_typ_cd
            FROM    severity_vw,
                    incident_type_vw
        ) crsjn LEFT JOIN
        report_vw r     ON  crsjn.severity_cd = r.severity_cd 
                        AND crsjn.incident_typ_cd = r.incident_typ_cd
GROUP BY crsjn.severity_cd,
        crsjn.incident_typ_cd
ORDER BY crsjn.severity_cd,
        crsjn.incident_typ_cd

Answer 4

This does the task:

SELECT
  SUM( incident_tally ) AS incident_tally,
  severity_cd,
  incident_typ_cd
FROM
(
  SELECT
    count( r.incident_id ) AS incident_tally,
    r.severity_cd,
    r.incident_typ_cd
  FROM
    report_vw r
  GROUP BY
    r.severity_cd, r.incident_typ_cd
  UNION ALL 
    SELECT
      0,
      s.code AS severity_cd,
      l.code AS incident_typ_cd
    FROM
      severity_vw s,
      incident_type_vw l
)
GROUP BY
  severity_cd, incident_typ_cd
ORDER BY
  severity_cd, incident_typ_cd

Is there a simpler solution?