CODEWITHSUNDEEP
oracle-database
user316478
user316478

Reputation: 59

Oracle Join does not return data if 1 column is empty

I have Oracle query to retrieve prices for products per customer, but it only returns row when customer is set up price for every product (1, 4, 6, 189, 191, 7, 80, 235).

How can I make it so it return row even when 1 column is null? I want row for every customer, doesn't matter if they have price for product or not.

I tried also Full Outer Join, Left Outer Join on the price tables but does not make difference to result.

Example what I want:

900001,Some Customer,1.59,2.49,3.39,0,0,0,0,4.92

select
    ltrim(kust.kunr),
    kust_adr.ku_name,
    p1.sp_auf_m2,
    p2.sp_auf_m2,
    p3.sp_auf_m2,
    p4.sp_auf_m2,
    p5.sp_auf_m2,
    p6.sp_auf_m2,
    p7.sp_auf_m2,
    p8.sp_auf_m2
from
    kust
    inner join
        kust_adr
        on kust.kunr = kust_adr.ku_nr
    full outer join
        sp_przu p1
        on kust.kunr = p1.kunr
    full outer join
        sp_przu p2
        on kust.kunr = p2.kunr
    full outer join
        sp_przu p3
        on kust.kunr = p3.kunr
    full outer join
        sp_przu p4
        on kust.kunr = p4.kunr
    full outer join
        sp_przu p5
        on kust.kunr = p5.kunr
    full outer join
        sp_przu p6
        on kust.kunr = p6.kunr
    full outer join
        sp_przu p7
        on kust.kunr = p7.kunr
    full outer join
        sp_przu p8
        on kust.kunr = p8.kunr
where
    kust_adr.ku_adr_art = 0
    and p1.prl_nr = 2
    and p1.spr_nr = 1
    and p2.prl_nr = 2
    and p2.spr_nr = 4
    and p3.prl_nr = 2
    and p3.spr_nr = 6
    and p4.prl_nr = 2
    and p4.spr_nr = 189
    and p5.prl_nr = 2
    and p5.spr_nr = 191
    and p6.prl_nr = 2
    and p6.spr_nr = 7
    and p7.prl_nr = 2
    and p7.spr_nr = 80
    and p8.prl_nr = 2
    and p8.spr_nr = 235
order by
    kust.kunr;

Upvotes: 0

Views: 408

Answers (1)

Alex Poole
Alex Poole

Reputation: 191275

Your and clauses are turning the outer joins back into inner ones - move those into their respective on clauses:

select
    ltrim(kust.kunr),
    kust_adr.ku_name,
    p1.sp_auf_m2,
    p2.sp_auf_m2,
    p3.sp_auf_m2,
    p4.sp_auf_m2,
    p5.sp_auf_m2,
    p6.sp_auf_m2,
    p7.sp_auf_m2,
    p8.sp_auf_m2
from
    kust
    inner join
        kust_adr
        on kust.kunr = kust_adr.ku_nr
    full outer join
        sp_przu p1
        on kust.kunr = p1.kunr
        and p1.prl_nr = 2
        and p1.spr_nr = 1
    full outer join
        sp_przu p2
        on kust.kunr = p2.kunr
        and p2.prl_nr = 2
        and p2.spr_nr = 4
    full outer join
        sp_przu p3
        on kust.kunr = p3.kunr
        and p3.prl_nr = 2
        and p3.spr_nr = 6
    full outer join
        sp_przu p4
        on kust.kunr = p4.kunr
        and p4.prl_nr = 2
        and p4.spr_nr = 189
    full outer join
        sp_przu p5
        on kust.kunr = p5.kunr
        and p5.prl_nr = 2
        and p5.spr_nr = 191
    full outer join
        sp_przu p6
        on kust.kunr = p6.kunr
        and p6.prl_nr = 2
        and p6.spr_nr = 7
    full outer join
        sp_przu p7
        on kust.kunr = p7.kunr
        and p7.prl_nr = 2
        and p7.spr_nr = 80
    full outer join
        sp_przu p8
        on kust.kunr = p8.kunr
        and p8.prl_nr = 2
        and p8.spr_nr = 235
where
    kust_adr.ku_adr_art = 0
order by
    kust.kunr;

I'm not sure if they really need to be full outer joins anyway; and it might be simpler to use a single outer join based on the pairs of values, and then pivoting that result, e.g.:

select *
from (
    select
        ltrim(k.kunr) as kunr,
        ka.ku_name,
        p.spr_nr,
        p.sp_auf_m2
    from
        kust k
        inner join
            kust_adr ka
            on k.kunr = ka.ku_nr
        left outer join
            sp_przu p
            on k.kunr = p.kunr
            and p.prl_nr = 2
            and p.spr_nr in (1, 4, 6, 189, 191, 7, 80, 235)
    where
        ka.ku_adr_art = 0
)
pivot (
    max(sp_auf_m2) for (spr_nr) in (1, 4, 6, 189, 191, 7, 80, 235)
)
order by
    kunr;

though you might want to set aliases instead of having the default quoted-identified column names matching the spr_nr values.

ORA-00933: SQL command not properly ended ... this is 10g

The pivot clause wasn't added until 11g but you can replicate what it does with aggregated case expressions (and it actually does this under the hood anyway):

select
    ltrim(k.kunr) as kunr,
    ka.ku_name,
    max(case when p.spr_nr = 1 then p.sp_auf_m2 end) as sp_auf_m2_1,
    max(case when p.spr_nr = 4 then p.sp_auf_m2 end) as sp_auf_m2_4,
    max(case when p.spr_nr = 6 then p.sp_auf_m2 end) as sp_auf_m2_6,
    max(case when p.spr_nr = 189 then p.sp_auf_m2 end) as sp_auf_m2_189,
    max(case when p.spr_nr = 191 then p.sp_auf_m2 end) as sp_auf_m2_191,
    max(case when p.spr_nr = 7 then p.sp_auf_m2 end) as sp_auf_m2_7,
    max(case when p.spr_nr = 80 then p.sp_auf_m2 end) as sp_auf_m2_80,
    max(case when p.spr_nr = 235 then p.sp_auf_m2 end) as sp_auf_m2_235
from
    kust k
    inner join
        kust_adr ka
        on k.kunr = ka.ku_nr
    left outer join
        sp_przu p
        on k.kunr = p.kunr
        and p.prl_nr = 2
        and p.spr_nr in (1, 4, 6, 189, 191, 7, 80, 235)
where
    ka.ku_adr_art = 0
group by
    ltrim(k.kunr),
    ka.ku_name
order by
    kunr;

Upvotes: 2

Related Questions