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 |
|
lott42_gmail
Messages: 146 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 |
|
lott42_gmail
Messages: 146 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
|
|
|
Goto Forum:
Current Time: Fri Sep 27 13:38:42 CDT 2024
|