OraFAQ Forum: SQL & PL/SQL » Fill in missing data

Home » SQL & PL/SQL » SQL & PL/SQL » Fill in missing data (12.2)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

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.

Report message to a moderator

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

Report message to a moderator

Previous Topic:	SQL Help
Next Topic:	Column union

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Apr 19 09:14:17 CDT 2024