Home » SQL & PL/SQL » SQL & PL/SQL » Fill in missing data (12.2)
Fill in missing data [message #673721] Thu, 06 December 2018 10:56 Go to next message
lott42_gmail
Messages: 133
Registered: June 2010
Senior Member
This is close to my previous topic of http://www.orafaq.com/forum/mv/msg/205236/673014/#msg_673014

but my data "system_data" is missing rows(they're commented out) of data
with system_data as 
(
select 'system 1' system, 2 err_code, 10 err_code_cnt from dual union all
select 'system 1' system, 3 err_code, 10 err_code_cnt from dual union all
--select 'system 1' system, 4 err_code, 10 err_code_cnt from dual union all
select 'system 2' system, 2 err_code, 5 err_code_cnt from dual union all
select 'system 2' system, 3 err_code, 15 err_code_cnt from dual union all
--select 'system 2' system, 4 err_code, 10 err_code_cnt from dual union all

select 'system 3' system, 2 err_code, 5 err_code_cnt from dual union all
select 'system 3' system, 3 err_code, 5 err_code_cnt from dual union all
select 'system 3' system, 4 err_code, 10 err_code_cnt from dual union all

--select 'system 4' system, 2 err_code, 6 err_code_cnt from dual union all
select 'system 4' system, 3 err_code, 10 err_code_cnt from dual union all
select 'system 4' system, 4 err_code, 9 err_code_cnt from dual union all

--select 'system 5' system, 2 err_code, 4 err_code_cnt from dual union all
--select 'system 5' system, 3 err_code, 10 err_code_cnt from dual union all
select 'system 5' system, 4 err_code, 11 err_code_cnt from dual union all

select 'system 6' system, 2 err_code, 5 err_code_cnt from dual union all
select 'system 6' system, 3 err_code, 14 err_code_cnt from dual union all
select 'system 6' system, 4 err_code, 11 err_code_cnt from dual
),
error_codes as(
select 2 err_code from dual union all
select 3 from dual union all
select 4 from dual)
SELECT
    sd.system, sd.err_code, nvl(sd.err_code_cnt,0) err_code_cnt
FROM
    system_data sd,
    error_codes ec
where
    ec.err_code = sd.err_code(+)
ORDER BY
    sd.system,
    ec.err_code,
    sd.err_code_cnt

I want each system in my "system_data" to have one and only one type of "err_code" of either 2,3,4.
So each system should have

ex:
SYSTEM          ERR_CODE ERR_CODE_CNT
system 1	2	10
system 1	3	10
system 1	4	null
system 2	2	5
system 2	3	15
system 2	4	null
system 3	2	5
system 3	3	5
system 3	4	10
system 4	2	null
system 4	3	10
system 4	4	9
system 5	2	null
system 5	3	null
system 5	4	11
system 6	2	5
system 6	3	14
system 6	4	11

I want the missing rows to have a null "err_code_cnt" to signify the "missing" row.
Re: Fill in missing data [message #673724 is a reply to message #673721] Thu, 06 December 2018 13:36 Go to previous message
lott42_gmail
Messages: 133
Registered: June 2010
Senior Member
Never mind, I think I got it

with system_data as 
(
select 'system 1' system, 2 err_code, 10 err_code_cnt from dual union all
select 'system 1' system, 3 err_code, 10 err_code_cnt from dual union all
--select 'system 1' system, 4 err_code, 10 err_code_cnt from dual union all
select 'system 2' system, 2 err_code, 5 err_code_cnt from dual union all
select 'system 2' system, 3 err_code, 15 err_code_cnt from dual union all
--select 'system 2' system, 4 err_code, 10 err_code_cnt from dual union all

select 'system 3' system, 2 err_code, 5 err_code_cnt from dual union all
select 'system 3' system, 3 err_code, 5 err_code_cnt from dual union all
select 'system 3' system, 4 err_code, 10 err_code_cnt from dual union all

--select 'system 4' system, 2 err_code, 6 err_code_cnt from dual union all
select 'system 4' system, 3 err_code, 10 err_code_cnt from dual union all
select 'system 4' system, 4 err_code, 9 err_code_cnt from dual union all

--select 'system 5' system, 2 err_code, 4 err_code_cnt from dual union all
--select 'system 5' system, 3 err_code, 10 err_code_cnt from dual union all
select 'system 5' system, 4 err_code, 11 err_code_cnt from dual union all

select 'system 6' system, 2 err_code, 5 err_code_cnt from dual union all
select 'system 6' system, 3 err_code, 14 err_code_cnt from dual union all
select 'system 6' system, 4 err_code, 11 err_code_cnt from dual
),
error_codes as(
select 2 err_code from dual union all
select 3 from dual union all
select 4 from dual)
, all_rows as(
SELECT DISTINCT
    sd.system,
    ec.err_code
FROM
    system_data sd,
    error_codes ec
WHERE
    1 = 1
)
SELECT
    ar.*,
    nvl(sd.err_code_cnt,'') err_code_cnt
FROM
    all_rows ar,
    system_data sd
WHERE
    1 = 1
    AND   ar.system = sd.system (+)
    AND   ar.err_code = sd.err_code (+)
ORDER BY
    1,
    2

Results
system 1	2	10
system 1	3	10
system 1	4	
system 2	2	5
system 2	3	15
system 2	4	
system 3	2	5
system 3	3	5
system 3	4	10
system 4	2	
system 4	3	10
system 4	4	9
system 5	2	
system 5	3	
system 5	4	11
system 6	2	5
system 6	3	14
system 6	4	11
Previous Topic: SQL Help
Next Topic: Column union
Goto Forum:
  


Current Time: Thu Dec 13 04:34:54 CST 2018