A request for help in solving problem, example below on image:

Example:

CodePudding user response：

The data densification technique using "partition by" claude after an "outer join" clause comes very handy for that purpose.

select s.sales_man, nvl(s.name, d.name) name, nvl(s.sales, 0)sales, d.sort_order
from dict d
left join sales s
    partition by (s.sales_man)
on d.name = s.name
order by s.sales_man, d.sort_order
;

demo on db<>fiddle

CodePudding user response：

Here's one option; read comments within code.

SQL> with
  2  -- sample data
  3  dict (name, sort_order) as
  4    (select 'brasil' , 1 from dual union all
  5     select 'italy'  , 2 from dual union all
  6     select 'germany', 3 from dual
  7    ),
  8  sales (sales_man, name, sales) as
  9    (select 100, 'brasil' , 100 from dual union all
 10     select 100, 'italy'  ,  50 from dual union all
 11     select 200, 'germany',  20 from dual union all
 12     select 200, 'brasil' ,  20 from dual
 13    ),
 14  --
 15  temp as
 16    -- all combinations of DICT X SALES
 17    (select distinct d.name, s.sales_man, d.sort_order
 18     from dict d cross join sales s
 19    )
 20  -- finally:
 21  select t.sales_man, t.name, nvl(s.sales, 0) sales, t.sort_order
 22  from temp t join dict d  on t.name = d.name
 23         left join sales s on s.name = t.name and s.sales_man = t.sales_man
 24  order by t.sales_man, t.sort_order;

 SALES_MAN NAME         SALES SORT_ORDER
---------- ------- ---------- ----------
       100 brasil         100          1
       100 italy           50          2
       100 germany          0          3
       200 brasil          20          1
       200 italy            0          2
       200 germany         20          3

6 rows selected.

SQL>