CREATE TABLE ALL_srv (
    COD_ORG  SMALLINT NOT NULL,
    COD_POSL SMALLINT,
    COD_RIZN SMALLINT,
    data_e   DATE
);
CREATE TABLE all_TAR (
    MARK       varchar(15),
    COD_POSL   SMALLINT NOT NULL,
    COD_RIZN   SMALLINT NOT NULL,
    COST_UNIT  NUMERIC(10,5),
    COST_UN1   NUMERIC(10,5),
    NORM       NUMERIC(10,5)
);
commit;

INSERT into ALL_srv SELECT DISTINCT cod_org, 4, posl4, max(data_e) from SERVICE1 WHERE posl4<>0 group BY cod_org, posl4;
INSERT into ALL_srv SELECT DISTINCT cod_org, 8, posl8, max(data_e) from SERVICE1 WHERE posl8<>0 group BY cod_org, posl8;
commit;

INSERT INTO all_tar select case tr.MARK WHEN 0 then 'без ліч.' WHEN 1 then 'з ліч.' WHEN 2 then 'будинковий' end as lich, tr.COD_POSL, tr.COD_RIZN, tr.COST_UNIT, tr.COST_UN1, tr.NORM
    from TARIF1 tr where tr.data_e is NULL and tr.tarn=0 and tr.cod_posl=4 and tr.numb=1;
INSERT INTO all_tar select case tr.MARK WHEN 0 then 'без ліч.' WHEN 1 then 'з ліч.' WHEN 2 then 'будинковий' end as lich, tr.COD_POSL, tr.COD_RIZN, tr.COST_UNIT, tr.COST_UN1, tr.NORM
    from TARIF1 tr where tr.data_e is NULL and tr.tarn=0 and tr.cod_posl=8 and tr.numb=1;

CREATE INDEX IDX_ALL_srv ON ALL_srv (COD_ORG,COD_POSL,COD_rizn);
commit;

select
  tr.MARK,
  tr.COD_POSL,
  tr.COD_RIZN,
  tr.COST_UNIT,
  tr.COST_UN1,
  tr.NORM,
  list(distinct sr.COD_ORG,'; '),
  max(sr.DATA_E)
from ALL_TAR tr
LEFT JOIN all_srv sr on tr.COD_POSL=sr.COD_POSL and tr.COD_RIZN=sr.COD_RIZN
group BY tr.MARK,
  tr.COD_POSL,
  tr.COD_RIZN,
  tr.COST_UNIT,
  tr.COST_UN1,
  tr.NORM

WITH ALL_TAR as (
  select
    case MARK
      WHEN 0 then 'без ліч.'
      WHEN 1 then 'з ліч.'
      WHEN 2 then 'будинковий'
    end as lich,
    COD_POSL, COD_RIZN, COST_UNIT, COST_UN1, NORM
  from
    TARIF1
  where
    data_e is NULL and
    tarn=0 and
    cod_posl=4 and
    numb=1
  union all
  select
    case MARK
      WHEN 0 then 'без ліч.'
      WHEN 1 then 'з ліч.'
      WHEN 2 then 'будинковий'
    end as lich,
    COD_POSL, COD_RIZN, COST_UNIT, COST_UN1, NORM
  from
    TARIF1
  where
    data_e is NULL
    and tarn=0
    and cod_posl=8
    and numb=1)
with all_srv as (
  SELECT DISTINCT cod_org, 4, posl4, max(data_e)
  from SERVICE1
  WHERE posl4<>0
  group BY cod_org, posl4
  union all
  SELECT DISTINCT cod_org, 8, posl8, max(data_e)
  from SERVICE1
  WHERE posl8<>0
  group BY cod_org, posl8);

select
  tr.MARK,
  tr.COD_POSL,
  tr.COD_RIZN,
  tr.COST_UNIT,
  tr.COST_UN1,
  tr.NORM,
  list(distinct sr.COD_ORG,'; '),
  max(sr.DATA_E)
from ALL_TAR tr
LEFT JOIN all_srv sr on tr.COD_POSL=sr.COD_POSL and tr.COD_RIZN=sr.COD_RIZN
group BY tr.MARK,
  tr.COD_POSL,
  tr.COD_RIZN,
  tr.COST_UNIT,
  tr.COST_UN1,
  tr.NORM

merge into таблица
using (
 select ... from источник1 ...
 union all
 select ... from источник2 ...
) on условие
when not matched
 insert ...

insert into таблица(колонки)
 select ... from источник1 ...
 union all
 select ... from источник2 ...

WITH ALL_TAR(MARK,COD_POSL,COD_RIZN,COST_UNIT,COST_UN1,NORM) as (
  select case MARK WHEN 0 then 'без ліч.' WHEN 1 then 'з ліч.' WHEN 2 then 'будинковий' end as lich, COD_POSL, COD_RIZN, COST_UNIT, COST_UN1, NORM
  from TARIF1 where data_e is NULL and tarn=0 and cod_posl=4 and numb=1
  union all
  select case MARK WHEN 0 then 'без ліч.' WHEN 1 then 'з ліч.' WHEN 2 then 'будинковий' end as lich, COD_POSL, COD_RIZN, COST_UNIT, COST_UN1, NORM
  from TARIF1 where data_e is NULL and tarn=0 and cod_posl=8 and numb=1),
 all_srv (COD_ORG, COD_POSL, COD_RIZN, data_e) as (
  SELECT DISTINCT cod_org, 4, posl4, max(data_e)
  from SERVICE1 WHERE posl4<>0 group BY cod_org, posl4
  union all
  SELECT DISTINCT cod_org, 8, posl8, max(data_e)
  from SERVICE1 WHERE posl8<>0 group BY cod_org, posl8)

select
  tr.MARK, tr.COD_POSL, tr.COD_RIZN, tr.COST_UNIT, tr.COST_UN1, tr.NORM, list(distinct sr.COD_ORG,'; '), max(sr.DATA_E)
from ALL_TAR tr
LEFT JOIN all_srv sr on tr.COD_POSL=sr.COD_POSL and tr.COD_RIZN=sr.COD_RIZN
group BY tr.MARK, tr.COD_POSL, tr.COD_RIZN, tr.COST_UNIT, tr.COST_UN1, tr.NORM

with stat1 as(....)
, stat2 as ( .... )
select * from stat.....

with recursive
  dept_year_budget as (
    select fiscal_year,
           dept_no,
           sum(projected_budget) as budget
    from proj_dept_budget
    group by fiscal_year, dept_no
  ),
  dept_tree as (
    select dept_no,
           head_dept,
           department,
           cast('' as varchar(255)) as indent
    from department
    where head_dept is null
    union all
    select d.dept_no,
           d.head_dept,
           d.department,
           h.indent || '  '
    from department d
         join dept_tree h on d.head_dept = h.dept_no
  )
select d.dept_no,
       d.indent || d.department as department,
       dyb_2008.budget as budget_08,
       dyb_2009.budget as budget_09
from dept_tree d
     left join dept_year_budget dyb_2008
       on d.dept_no = dyb_2008.dept_no
       and dyb_2008.fiscal_year = 2008
     left join dept_year_budget dyb_2009
       on d.dept_no = dyb_2009.dept_no
       and dyb_2009.fiscal_year = 2009