How to join the select of these 3 tables that have no connection at all?

Question

How to join the select of these 3 tables that have no connection at all?

Asked 3 years, 8 months ago

Viewed 66 times

1

The image below shows 3 tables, united by Excel.

The first is called customer, the second representative and the third is called channel.

But I would like to join them in a single select in sql, and I have no idea how to do that. Could someone show a basic example of at least the first select of each of the tables so I can replicate in the others?

SELECT FROM FIRST TABLE

select
(select 1 from dual) as ordem,
(select '' from dual) as DESCRICAO,
(select 2017 from dual) as CLIENTE,
(select 2018 from dual) as CLIENTE,
(select 2019 from dual) as CLIENTE,
(select 2020 from dual) as CLIENTE
FROM DUAL
UNION

select
(select 2 from dual) as ordem,
(select 'VENDAS ACUMULADA' from dual) as DESCRICAO,
sum((case when to_char(x.dtentsai, 'YYYY') = 2017 then x.vlrtot else 0 end)) AS "2017", 
sum((case when to_char(x.dtentsai, 'YYYY') = 2018 then x.vlrtot else 0 end)) AS "2018",
sum((case when to_char(x.dtentsai, 'YYYY') = 2019 then x.vlrtot else 0 end)) AS "2019",
sum((case when to_char(x.dtentsai, 'YYYY') = 2020 then x.vlrtot else 0 end)) AS "2020"
from ad_dadosmrg_ex x
where
x.tipmov in ('V', 'D') and
x.grupo in ('VENDAS') and
x.codparcmatriz = 3551
GROUP BY 1
union

select
(select 3 from dual) as ordem,
(select 'DESC MEDIO %' from dual) as DESCRICAO,
round((1-(sum(DECODE(TO_CHAR(X.DTENTSAI, 'YYYY'), 2017, X.VLRTOT,0))/sum(DECODE(TO_CHAR(X.DTENTSAI, 'YYYY'), 2017, X.PRTAB,0))))*100,2) AS DESC_2017,
round((1-(sum(DECODE(TO_CHAR(X.DTENTSAI, 'YYYY'), 2018, X.VLRTOT,0))/sum(DECODE(TO_CHAR(X.DTENTSAI, 'YYYY'), 2018, X.PRTAB,0))))*100,2) AS DESC_2018,
round((1-(sum(DECODE(TO_CHAR(X.DTENTSAI, 'YYYY'), 2019, X.VLRTOT,0))/sum(DECODE(TO_CHAR(X.DTENTSAI, 'YYYY'), 2019, X.PRTAB,0))))*100,2) AS DESC_2019,
round((1-(sum(DECODE(TO_CHAR(X.DTENTSAI, 'YYYY'), 2020, X.VLRTOT,0))/sum(DECODE(TO_CHAR(X.DTENTSAI, 'YYYY'), 2020, X.PRTAB,0))))*100,2) AS DESC_2020
from ad_dadosmrg_ex x
where
x.tipmov in ('V', 'D') and
x.grupo in ('VENDAS') and
x.codparcmatriz = 3551
GROUP BY 1
union
select
(select 4 from dual) as ordem,
(select 'DESC ADICIONAL %' from dual) as DESCRICAO,
FC_PERS_BUSCA_DESC_AD(2017,3551,1) AS DESC_2017,
FC_PERS_BUSCA_DESC_AD(2018,3551,1) AS DESC_2018,
FC_PERS_BUSCA_DESC_AD(2019,3551,1) AS DESC_2019,
FC_PERS_BUSCA_DESC_AD(2020,3551,1) AS DESC_2020
from dual

union

select
(select 5 from dual) as ordem,
(select 'DESC MEDIO TOTAL %' from dual) as DESCRICAO,
round((1-(sum(DECODE(TO_CHAR(X.DTENTSAI, 'YYYY'), 2017, X.VLRTOT,0))/sum(DECODE(TO_CHAR(X.DTENTSAI, 'YYYY'), 2017, X.PRTAB,0))))*100,2)
+ FC_PERS_BUSCA_DESC_AD(2017,3551,1) AS DESC_2017,
round((1-(sum(DECODE(TO_CHAR(X.DTENTSAI, 'YYYY'), 2018, X.VLRTOT,0))/sum(DECODE(TO_CHAR(X.DTENTSAI, 'YYYY'), 2018, X.PRTAB,0))))*100,2)
+ FC_PERS_BUSCA_DESC_AD(2018,3551,1)AS DESC_2018,
round((1-(sum(DECODE(TO_CHAR(X.DTENTSAI, 'YYYY'), 2019, X.VLRTOT,0))/sum(DECODE(TO_CHAR(X.DTENTSAI, 'YYYY'), 2019, X.PRTAB,0))))*100,2)
+ FC_PERS_BUSCA_DESC_AD(2019,3551,1) AS DESC_2019,
round((1-(sum(DECODE(TO_CHAR(X.DTENTSAI, 'YYYY'), 2020, X.VLRTOT,0))/sum(DECODE(TO_CHAR(X.DTENTSAI, 'YYYY'), 2020, X.PRTAB,0))))*100,2)
+ FC_PERS_BUSCA_DESC_AD(2020,3551,1)
AS DESC_2020
from ad_dadosmrg_ex x
where
x.tipmov in ('V', 'D') and
x.grupo in ('VENDAS') and
x.codparcmatriz = 3551
GROUP BY 1

union

SELECT
(select 6 from dual) as ordem,
(select 'INVESTIMENTO R$' from dual) descricao,
NVL(-SUM((CASE WHEN to_char(x.dtentsai,'YYYY') = 2017 THEN X.BONIF + X.TROCAS + X.DESCFIN + X.PROMOTORAS + X.CONTRATOS + X.CAMPANHAS + X.DESCCOMERC + X.AJUDACUSTO + X.INVESTMARKETING ELSE 0 END)),0) as "2017",
NVL(-SUM((CASE WHEN to_char(x.dtentsai,'YYYY') = 2018 THEN X.BONIF + X.TROCAS + X.DESCFIN + X.PROMOTORAS + X.CONTRATOS + X.CAMPANHAS + X.DESCCOMERC + X.AJUDACUSTO + X.INVESTMARKETING ELSE 0 END)),0) as "2018",
NVL(-SUM((CASE WHEN to_char(x.dtentsai,'YYYY') = 2019 THEN X.BONIF + X.TROCAS + X.DESCFIN + X.PROMOTORAS + X.CONTRATOS + X.CAMPANHAS + X.DESCCOMERC + X.AJUDACUSTO + X.INVESTMARKETING ELSE 0 END)),0) as "2019",
NVL(-SUM((CASE WHEN to_char(x.dtentsai,'YYYY') = 2020 THEN X.BONIF + X.TROCAS + X.DESCFIN + X.PROMOTORAS + X.CONTRATOS + X.CAMPANHAS + X.DESCCOMERC + X.AJUDACUSTO + X.INVESTMARKETING ELSE 0 END)),0) as "2020"
FROM AD_INVEST X
WHERE
X.CODVEND = 10304 AND
X.CODPARCMATRIZ = 3551
GROUP BY 1

union

SELECT
(select 7 from dual) as ordem,
(select 'INVESTIMENTO %' from dual) descricao,
ROUND((NVL(-SUM((CASE WHEN TO_CHAR(X.DTENTSAI,'YYYY') = 2017 THEN X.BONIF + X.TROCAS + X.DESCFIN + X.PROMOTORAS + X.CONTRATOS + X.CAMPANHAS + X.DESCCOMERC + X.AJUDACUSTO + X.INVESTMARKETING ELSE 0 END)),0)
/ NVL(SUM((CASE WHEN TO_CHAR(X.DTENTSAI,'YYYY') = 2017 THEN X.VENDAS ELSE 1 END)),0))*100,2) as "2017",
ROUND((NVL(-SUM((CASE WHEN TO_CHAR(X.DTENTSAI,'YYYY') = 2018 THEN X.BONIF + X.TROCAS + X.DESCFIN + X.PROMOTORAS + X.CONTRATOS + X.CAMPANHAS + X.DESCCOMERC + X.AJUDACUSTO + X.INVESTMARKETING ELSE 0 END)),0)
/ NVL(SUM((CASE WHEN TO_CHAR(X.DTENTSAI,'YYYY') = 2018 THEN X.VENDAS ELSE 1 END)),0))*100,2) as "2018",
ROUND((NVL(-SUM((CASE WHEN TO_CHAR(X.DTENTSAI,'YYYY') = 2019 THEN X.BONIF + X.TROCAS + X.DESCFIN + X.PROMOTORAS + X.CONTRATOS + X.CAMPANHAS + X.DESCCOMERC + X.AJUDACUSTO + X.INVESTMARKETING ELSE 0 END)),0)
/ NVL(SUM((CASE WHEN TO_CHAR(X.DTENTSAI,'YYYY') = 2019 THEN X.VENDAS ELSE 1 END)),0))*100,2) as "2019",
ROUND((NVL(-SUM((CASE WHEN TO_CHAR(X.DTENTSAI,'YYYY') = 2020 THEN X.BONIF + X.TROCAS + X.DESCFIN + X.PROMOTORAS + X.CONTRATOS + X.CAMPANHAS + X.DESCCOMERC + X.AJUDACUSTO + X.INVESTMARKETING ELSE 0 END)),0)
/ NVL(SUM((CASE WHEN TO_CHAR(X.DTENTSAI,'YYYY') = 2020 THEN X.VENDAS ELSE 1 END)),0))*100,2) as "2020"
FROM AD_INVEST X
WHERE X.CODVEND = 10304
AND X.CODPARCMATRIZ = 3551
GROUP BY 1

union
SELECT
(select 8 from dual) as ordem,
(select 'MARGEM $' from dual) descricao,
FC_PERS_BUSCA_MGVLR(2017, 3551, 1) as "2017",
FC_PERS_BUSCA_MGVLR(2018, 3551, 1) as "2018",
FC_PERS_BUSCA_MGVLR(2019, 3551, 1) as "2019",
FC_PERS_BUSCA_MGVLR(2020, 3551, 1) as "2020"
FROM DUAL

union

select
(select 9 from dual) as ordem,
(select 'MARGEM %' from dual) as DESCRICAO,
ROUND(FC_PERS_BUSCA_MGVLR(2017, 3551, 1)/
sum((case when to_char(x.dtentsai, 'YYYY') = 2017 then x.vlrtot else 0 end))*100,2) AS "2017", 
ROUND(FC_PERS_BUSCA_MGVLR(2018, 3551, 1)/
sum((case when to_char(x.dtentsai, 'YYYY') = 2018 then x.vlrtot else 0 end))*100,2) AS "2018",
ROUND(FC_PERS_BUSCA_MGVLR(2019, 3551, 1)/
sum((case when to_char(x.dtentsai, 'YYYY') = 2019 then x.vlrtot else 0 end))*100,2) AS "2019",
ROUND(FC_PERS_BUSCA_MGVLR(2020, 3551, 1)/
sum((case when to_char(x.dtentsai, 'YYYY') = 2020 then x.vlrtot else 0 end))*100,2) AS "2020"
from ad_dadosmrg_ex x
where
x.tipmov in ('V', 'D') and
x.grupo in ('VENDAS') and
x.codparcmatriz = 3551
GROUP BY 1

SELECT FROM THE SECOND TABLE

select
(select 1 from dual) as ordem,
(select '' from dual) as DESCRICAO,
(select 2017 from dual) as REPRESENTANTE,
(select 2018 from dual) as REPRESENTANTE,
(select 2019 from dual) as REPRESENTANTE,
(select 2020 from dual) as REPRESENTANTE
FROM DUAL
union
SELECT
(select 2 from dual) as ordem,
(select 'VENDAS ACUMULADA' from dual) as DESCRICAO,
sum((case when to_char(inv.dtentsai, 'YYYY') = 2017 then inv.vendas else 0 end)) as "2017",
sum((case when to_char(inv.dtentsai, 'YYYY') = 2018 then inv.vendas else 0 end)) as "2018",
sum((case when to_char(inv.dtentsai, 'YYYY') = 2019 then inv.vendas else 0 end)) as "2019",
sum((case when to_char(inv.dtentsai, 'YYYY') = 2020 then inv.vendas else 0 end)) as "2020"
FROM AD_INVEST INV
WHERE INV.CODVEND = '10304'
group by 1
union
select
(select 3 from dual) as ordem,
(select 'DESC MEDIO %' from dual) as DESCRICAO,
round((1-(sum(DECODE(TO_CHAR(X.DTENTSAI, 'YYYY'), 2017, X.VLRTOT,0))/sum(DECODE(TO_CHAR(X.DTENTSAI, 'YYYY'), 2017, X.PRTAB,0))))*100,2) AS DESC_2017,
round((1-(sum(DECODE(TO_CHAR(X.DTENTSAI, 'YYYY'), 2018, X.VLRTOT,0))/sum(DECODE(TO_CHAR(X.DTENTSAI, 'YYYY'), 2018, X.PRTAB,0))))*100,2) AS DESC_2018,
round((1-(sum(DECODE(TO_CHAR(X.DTENTSAI, 'YYYY'), 2019, X.VLRTOT,0))/sum(DECODE(TO_CHAR(X.DTENTSAI, 'YYYY'), 2019, X.PRTAB,0))))*100,2) AS DESC_2019,
round((1-(sum(DECODE(TO_CHAR(X.DTENTSAI, 'YYYY'), 2020, X.VLRTOT,0))/sum(DECODE(TO_CHAR(X.DTENTSAI, 'YYYY'), 2020, X.PRTAB,0))))*100,2) AS DESC_2020
from ad_dadosmrg_ex x
where
x.tipmov in ('V', 'D') and
x.grupo in ('VENDAS') and
x.codvend = 10304
group by 1
UNION
select
(select 4 from dual) as ordem,
(select 'DESC ADICIONAL %' from dual) as DESCRICAO,
FC_PERS_BUSCA_DESC_AD(2017,10304,2) AS DESC_2017,
FC_PERS_BUSCA_DESC_AD(2018,10304,2) AS DESC_2018,
FC_PERS_BUSCA_DESC_AD(2019,10304,2) AS DESC_2019,
FC_PERS_BUSCA_DESC_AD(2020,10304,2) AS DESC_2020
from dual
union
select
(select 5 from dual) as ordem,
(select 'DESC MEDIO TOTAL %' from dual) as DESCRICAO,
FC_PERS_BUSCA_DESC_AD(2017,10304,2) +
round((1-(sum(DECODE(TO_CHAR(X.DTENTSAI, 'YYYY'), 2017, X.VLRTOT,0))/sum(DECODE(TO_CHAR(X.DTENTSAI, 'YYYY'), 2017, X.PRTAB,0))))*100,2) AS DESC_2017,
FC_PERS_BUSCA_DESC_AD(2018,10304,2) +
round((1-(sum(DECODE(TO_CHAR(X.DTENTSAI, 'YYYY'), 2018, X.VLRTOT,0))/sum(DECODE(TO_CHAR(X.DTENTSAI, 'YYYY'), 2018, X.PRTAB,0))))*100,2) AS DESC_2018,
FC_PERS_BUSCA_DESC_AD(2019,10304,2) +
round((1-(sum(DECODE(TO_CHAR(X.DTENTSAI, 'YYYY'), 2019, X.VLRTOT,0))/sum(DECODE(TO_CHAR(X.DTENTSAI, 'YYYY'), 2019, X.PRTAB,0))))*100,2) AS DESC_2019,
FC_PERS_BUSCA_DESC_AD(2020,10304,2) +
round((1-(sum(DECODE(TO_CHAR(X.DTENTSAI, 'YYYY'), 2020, X.VLRTOT,0))/sum(DECODE(TO_CHAR(X.DTENTSAI, 'YYYY'), 2020, X.PRTAB,0))))*100,2) AS DESC_2020
from ad_dadosmrg_ex x
where
x.tipmov in ('V', 'D') and
x.grupo in ('VENDAS') and
x.codvend = 10304
group by 1
union
SELECT
(select 6 from dual) as ordem,
(select 'INVESTIMENTO R$' from dual) descricao,
NVL(-SUM((CASE WHEN TO_CHAR(INV.DTENTSAI, 'YYYY') = 2017 THEN (INV.BONIF + INV.TROCAS + INV.DESCFIN + INV.PROMOTORAS + INV.CONTRATOS + INV.CAMPANHAS + INV.DESCCOMERC + INV.AJUDACUSTO + INV.INVESTMARKETING) ELSE 0 END)),0) AS "2017",
NVL(-SUM((CASE WHEN TO_CHAR(INV.DTENTSAI, 'YYYY') = 2018 THEN (INV.BONIF + INV.TROCAS + INV.DESCFIN + INV.PROMOTORAS + INV.CONTRATOS + INV.CAMPANHAS + INV.DESCCOMERC + INV.AJUDACUSTO + INV.INVESTMARKETING) ELSE 0 END)),0) AS "2018",
NVL(-SUM((CASE WHEN TO_CHAR(INV.DTENTSAI, 'YYYY') = 2019 THEN (INV.BONIF + INV.TROCAS + INV.DESCFIN + INV.PROMOTORAS + INV.CONTRATOS + INV.CAMPANHAS + INV.DESCCOMERC + INV.AJUDACUSTO + INV.INVESTMARKETING) ELSE 0 END)),0) AS "2019",
NVL(-SUM((CASE WHEN TO_CHAR(INV.DTENTSAI, 'YYYY') = 2020 THEN (INV.BONIF + INV.TROCAS + INV.DESCFIN + INV.PROMOTORAS + INV.CONTRATOS + INV.CAMPANHAS + INV.DESCCOMERC + INV.AJUDACUSTO + INV.INVESTMARKETING) ELSE 0 END)),0) AS "2020"
FROM AD_INVEST INV
WHERE INV.CODVEND = '10304'
GROUP BY 1
UNION
SELECT
(select 7 from dual) as ordem,
(select 'INVESTIMENTO %' from dual) descricao,
ROUND((NVL(-SUM((CASE WHEN TO_CHAR(INV.DTENTSAI, 'YYYY') = 2017 THEN (INV.BONIF + INV.TROCAS + INV.DESCFIN + INV.PROMOTORAS + INV.CONTRATOS + INV.CAMPANHAS + INV.DESCCOMERC + INV.AJUDACUSTO + INV.INVESTMARKETING) ELSE 0 END)),0)
/ SUM((CASE WHEN TO_CHAR(INV.DTENTSAI, 'YYYY') = 2017 THEN INV.VENDAS ELSE 0 END)))*100,2) AS "2017",
ROUND((NVL(-SUM((CASE WHEN TO_CHAR(INV.DTENTSAI, 'YYYY') = 2018 THEN (INV.BONIF + INV.TROCAS + INV.DESCFIN + INV.PROMOTORAS + INV.CONTRATOS + INV.CAMPANHAS + INV.DESCCOMERC + INV.AJUDACUSTO + INV.INVESTMARKETING) ELSE 0 END)),0)
/ SUM((CASE WHEN TO_CHAR(INV.DTENTSAI, 'YYYY') = 2018 THEN INV.VENDAS ELSE 0 END)))*100,2) AS "2018",
ROUND((NVL(-SUM((CASE WHEN TO_CHAR(INV.DTENTSAI, 'YYYY') = 2019 THEN (INV.BONIF + INV.TROCAS + INV.DESCFIN + INV.PROMOTORAS + INV.CONTRATOS + INV.CAMPANHAS + INV.DESCCOMERC + INV.AJUDACUSTO + INV.INVESTMARKETING) ELSE 0 END)),0)
/ SUM((CASE WHEN TO_CHAR(INV.DTENTSAI, 'YYYY') = 2019 THEN INV.VENDAS ELSE 0 END)))*100,2) AS "2019",
ROUND((NVL(-SUM((CASE WHEN TO_CHAR(INV.DTENTSAI, 'YYYY') = 2020 THEN (INV.BONIF + INV.TROCAS + INV.DESCFIN + INV.PROMOTORAS + INV.CONTRATOS + INV.CAMPANHAS + INV.DESCCOMERC + INV.AJUDACUSTO + INV.INVESTMARKETING) ELSE 0 END)),0)
/ SUM((CASE WHEN TO_CHAR(INV.DTENTSAI, 'YYYY') = 2020 THEN INV.VENDAS ELSE 0 END)))*100,2) AS "2020"
FROM AD_INVEST INV
WHERE INV.CODVEND = '10304'
GROUP BY 1
union
SELECT
(select 8 from dual) as ordem,
(select 'MARGEM $' from dual) descricao,
FC_PERS_BUSCA_MGVLR (2017, 10304, 2) as "2017",
FC_PERS_BUSCA_MGVLR (2018, 10304, 2) as "2018",
FC_PERS_BUSCA_MGVLR (2019, 10304, 2) as "2019",
FC_PERS_BUSCA_MGVLR (2020, 10304, 2) as "2020"
FROM DUAL
union
SELECT
(select 9 from dual) as ordem,
(select 'MARGEM %' from dual) as DESCRICAO,
ROUND(FC_PERS_BUSCA_MGVLR (2017, 10304, 2) /
sum((case when to_char(inv.dtentsai, 'YYYY') = 2017 then inv.vendas else 0 end))*100,2) as "2017",
ROUND(FC_PERS_BUSCA_MGVLR (2018, 10304, 2) /
sum((case when to_char(inv.dtentsai, 'YYYY') = 2018 then inv.vendas else 0 end))*100,2) as "2018",
ROUND(FC_PERS_BUSCA_MGVLR (2019, 10304, 2) /
sum((case when to_char(inv.dtentsai, 'YYYY') = 2019 then inv.vendas else 0 end))*100,2) as "2019",
ROUND(FC_PERS_BUSCA_MGVLR (2020, 10304, 2) /
sum((case when to_char(inv.dtentsai, 'YYYY') = 2020 then inv.vendas else 0 end))*100,2) as "2020"
FROM AD_INVEST INV
WHERE INV.CODVEND = '10304'
group by 1

SELECT FROM THE THIRD TABLE

select
(select 1 from dual) as ordem,
(select '' from dual) as DESCRICAO,
(select 2017 from dual) as CANAL,
(select 2018 from dual) as CANAL,
(select 2019 from dual) as CANAL,
(select 2020 from dual) as CANAL
FROM DUAL
UNION

select
(select 2 from dual) as ordem,
(select 'VENDAS ACUMULADA' from dual) as DESCRICAO,
sum((case when to_char(y.dtentsai,'YYYY')=2017 then y.vendas else 0 end)) as v2017,
sum((case when to_char(y.dtentsai,'YYYY')=2018 then y.vendas else 0 end)) as v2018,
sum((case when to_char(y.dtentsai,'YYYY')=2019 then y.vendas else 0 end)) as v2019,
sum((case when to_char(y.dtentsai,'YYYY')=2020 then y.vendas else 0 end)) as v2020
from ad_invest y
where y.codvend = 10304
and y.codtipparc = 10101
group by 1
union
SELECT
(select 3 from dual) as ordem,
(select 'DESC MEDIO %' from dual) as DESCRICAO,
round((1-(coalesce(sum(case when to_char(x.dtentsai, 'YYYY') = 2017 then x.vlrtot end),1)/coalesce(sum(case when to_char(x.dtentsai, 'YYYY') = 2017 then x.prtab end),1)))*100,2) d2017,
round((1-(coalesce(sum(case when to_char(x.dtentsai, 'YYYY') = 2018 then x.vlrtot end),1)/coalesce(sum(case when to_char(x.dtentsai, 'YYYY') = 2018 then x.prtab end),1)))*100,2) d2018,
round((1-(coalesce(sum(case when to_char(x.dtentsai, 'YYYY') = 2019 then x.vlrtot end),1)/coalesce(sum(case when to_char(x.dtentsai, 'YYYY') = 2019 then x.prtab end),1)))*100,2) d2019,
round((1-(coalesce(sum(case when to_char(x.dtentsai, 'YYYY') = 2020 then x.vlrtot end),1)/coalesce(sum(case when to_char(x.dtentsai, 'YYYY') = 2020 then x.prtab end),1)))*100,2) d2020
FROM AD_DADOSMRG_EX X
WHERE
X.TIPMOV IN ('V','D') AND X.GRUPO IN ('VENDAS') AND
X.CODVEND = 10304 AND
X.CODTIPPARC = 10101
group by 1
UNION
select
(select 4 from dual) as ordem,
(select 'DESC ADICIONAL %' from dual) as DESCRICAO,
FC_PERS_BUSCA_DESC_AD(2017,3551,3) AS DESC_2017,
FC_PERS_BUSCA_DESC_AD(2018,3551,3) AS DESC_2018,
FC_PERS_BUSCA_DESC_AD(2019,3551,3) AS DESC_2019,
FC_PERS_BUSCA_DESC_AD(2020,3551,3) AS DESC_2020
from dual
union
SELECT
(select 5 from dual) as ordem,
(select 'DESC MEDIO TOTAL %' from dual) as DESCRICAO,
round((1-(coalesce(sum(case when to_char(x.dtentsai, 'YYYY') = 2017 then x.vlrtot end),1)/coalesce(sum(case when to_char(x.dtentsai, 'YYYY') = 2017 then x.prtab end),1)))*100,2)
+ FC_PERS_BUSCA_DESC_AD(2017,3551,3) d2017,
round((1-(coalesce(sum(case when to_char(x.dtentsai, 'YYYY') = 2018 then x.vlrtot end),1)/coalesce(sum(case when to_char(x.dtentsai, 'YYYY') = 2018 then x.prtab end),1)))*100,2)
+ FC_PERS_BUSCA_DESC_AD(2018,3551,3) d2018,
round((1-(coalesce(sum(case when to_char(x.dtentsai, 'YYYY') = 2019 then x.vlrtot end),1)/coalesce(sum(case when to_char(x.dtentsai, 'YYYY') = 2019 then x.prtab end),1)))*100,2)
+ FC_PERS_BUSCA_DESC_AD(2019,3551,3) d2019,
round((1-(coalesce(sum(case when to_char(x.dtentsai, 'YYYY') = 2020 then x.vlrtot end),1)/coalesce(sum(case when to_char(x.dtentsai, 'YYYY') = 2020 then x.prtab end),1)))*100,2)
+ FC_PERS_BUSCA_DESC_AD(2020,3551,3) d2020
FROM AD_DADOSMRG_EX X
WHERE
X.TIPMOV IN ('V','D') AND X.GRUPO IN ('VENDAS') AND
X.CODVEND = 10304 AND
X.CODTIPPARC = 10101
group by 1
union
SELECT
(select 6 from dual) as ordem,
(select 'INVESTIMENTO R$' from dual) descricao,
NVL(-SUM(CASE WHEN TO_CHAR(INV.DTENTSAI,'YYYY') = 2017 THEN INV.BONIF + INV.TROCAS + INV.DESCFIN + INV.PROMOTORAS + INV.CONTRATOS + INV.CAMPANHAS + INV.DESCCOMERC + INV.AJUDACUSTO + INV.INVESTMARKETING ELSE 0 END),0) I2017,
NVL(-SUM(CASE WHEN TO_CHAR(INV.DTENTSAI,'YYYY') = 2018 THEN INV.BONIF + INV.TROCAS + INV.DESCFIN + INV.PROMOTORAS + INV.CONTRATOS + INV.CAMPANHAS + INV.DESCCOMERC + INV.AJUDACUSTO + INV.INVESTMARKETING ELSE 0 END),0) I2018,
NVL(-SUM(CASE WHEN TO_CHAR(INV.DTENTSAI,'YYYY') = 2019 THEN INV.BONIF + INV.TROCAS + INV.DESCFIN + INV.PROMOTORAS + INV.CONTRATOS + INV.CAMPANHAS + INV.DESCCOMERC + INV.AJUDACUSTO + INV.INVESTMARKETING ELSE 0 END),0) I2019,
NVL(-SUM(CASE WHEN TO_CHAR(INV.DTENTSAI,'YYYY') = 2020 THEN INV.BONIF + INV.TROCAS + INV.DESCFIN + INV.PROMOTORAS + INV.CONTRATOS + INV.CAMPANHAS + INV.DESCCOMERC + INV.AJUDACUSTO + INV.INVESTMARKETING ELSE 0 END),0) I2020
FROM
AD_INVEST INV
WHERE
inv.codvend=10304 and
inv.codtipparc = 10101
GROUP BY 1
union
select
(select 7 from dual) as ordem,
(select 'INVESTIMENTO %' from dual) as DESCRICAO,
round(NVL(-SUM(CASE WHEN TO_CHAR(INV.DTENTSAI,'YYYY') = 2017 THEN INV.BONIF + INV.TROCAS + INV.DESCFIN + INV.PROMOTORAS + INV.CONTRATOS + INV.CAMPANHAS + INV.DESCCOMERC + INV.AJUDACUSTO + INV.INVESTMARKETING ELSE 0 END),0)
/ COALESCE(sum((case when to_char(inv.dtentsai,'YYYY')=2017 then inv.vendas end)),1)*100,2) as v2017,
round(NVL(-SUM(CASE WHEN TO_CHAR(INV.DTENTSAI,'YYYY') = 2018 THEN INV.BONIF + INV.TROCAS + INV.DESCFIN + INV.PROMOTORAS + INV.CONTRATOS + INV.CAMPANHAS + INV.DESCCOMERC + INV.AJUDACUSTO + INV.INVESTMARKETING ELSE 0 END),0)
/ COALESCE(sum((case when to_char(inv.dtentsai,'YYYY')=2018 then inv.vendas end)),1)*100,2) as v2018,
round(NVL(-SUM(CASE WHEN TO_CHAR(INV.DTENTSAI,'YYYY') = 2019 THEN INV.BONIF + INV.TROCAS + INV.DESCFIN + INV.PROMOTORAS + INV.CONTRATOS + INV.CAMPANHAS + INV.DESCCOMERC + INV.AJUDACUSTO + INV.INVESTMARKETING ELSE 0 END),0)
/ COALESCE(sum((case when to_char(inv.dtentsai,'YYYY')=2019 then inv.vendas end)),1)*100,2) as v2019,
round(NVL(-SUM(CASE WHEN TO_CHAR(INV.DTENTSAI,'YYYY') = 2020 THEN INV.BONIF + INV.TROCAS + INV.DESCFIN + INV.PROMOTORAS + INV.CONTRATOS + INV.CAMPANHAS + INV.DESCCOMERC + INV.AJUDACUSTO + INV.INVESTMARKETING ELSE 0 END),0)
/ COALESCE(sum((case when to_char(inv.dtentsai,'YYYY')=2020 then inv.vendas end)),1)*100,2) as v2020
from ad_invest inv
where inv.codvend = 10304
and inv.codtipparc = 10101
group by 1
union
SELECT
(select 8 from dual) as ordem,
(select 'MARGEM R$' from dual) descricao,
FC_PERS_BUSCA_MGVLR(2017, 3551, 3) as "2017",
FC_PERS_BUSCA_MGVLR(2018, 3551, 3) as "2018",
FC_PERS_BUSCA_MGVLR(2019, 3551, 3) as "2019",
FC_PERS_BUSCA_MGVLR(2020, 3551, 3) as "2020"
FROM DUAL
union
select
(select 9 from dual) as ordem,
(select 'MARGEM %' from dual) as DESCRICAO,
round(FC_PERS_BUSCA_MGVLR(2017, 3551, 3)
/ COALESCE(sum((case when to_char(inv.dtentsai,'YYYY')=2017 then inv.vendas end)),1)*100,2) as v2017,
round(FC_PERS_BUSCA_MGVLR(2018, 3551, 3)
/ COALESCE(sum((case when to_char(inv.dtentsai,'YYYY')=2018 then inv.vendas end)),1)*100,2) as v2018,
round(FC_PERS_BUSCA_MGVLR(2019, 3551, 3)
/ COALESCE(sum((case when to_char(inv.dtentsai,'YYYY')=2019 then inv.vendas end)),1)*100,2) as v2019,
round(FC_PERS_BUSCA_MGVLR(2020, 3551, 3)
/ COALESCE(sum((case when to_char(inv.dtentsai,'YYYY')=2020 then inv.vendas end)),1)*100,2) as v2020
from ad_invest inv
where inv.codvend = 10304
and inv.codtipparc = 10101
group by 1

Making Union of 3 is not good ?

– Isac

2021/02/09 at 13:59
That’s the question, how to do?

– Rodrigo Macaúbas

2021/02/09 at 14:30
How do you plan to implement this ? some front-end , report generator ? The cheeses may have confused me but I think it could be simpler , the basic question is "pivotear" per year and type (cleinte etc) but the solution depends a little on how it will be implemented.

– Motta

2021/02/09 at 16:07
It is because this report was done through an Excel, and then now it will be generated directly on the system and then exported to Excel. So I had to put abstract columns there so that they made sense to "print"

– Rodrigo Macaúbas

2021/02/09 at 16:30
"directly generated in the system" is half vacant , but a temporary table could already be loaded in the requested format, with Oracle searching for WITH and GTT https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:524736300346304074

– Motta

2021/02/09 at 17:17

1 answer

Browser other questions tagged mysql sql database oracle union

You are not signed in. Login or sign up in order to post.

by Eduardo Bissi • **424** points · Answer 1 · 2021-02-09T18:21:19+00:00

I suggest doing something like this:

First, make each of the 3 SELECT contains all result columns (columns that are not relevant, complete with zero) and columns of the same type and name. Example:

Table Client

SELECT
    descricao, 
    sum(..) AS cli2017, sum(..) AS cli2018 ...
    sum(0) AS rep2017, sum(0) AS rep2018 ...
    sum(0) AS can2017, sum(0) AS can2018 ...
FROM
    tabelaCliente...

Representative Table

SELECT
    descricao, 
    sum(0) AS cli2017, sum(0) AS cli2018 ...
    sum(..) AS rep2017, sum(..) AS rep2018 ...
    sum(0) AS can2017, sum(0) AS can2018 ...
FROM
    tabelaRepresentante...

Table Canal

SELECT
    descricao, 
    sum(0) AS cli2017, sum(0) AS cli2018 ...
    sum(0) AS rep2017, sum(0) AS rep2018 ...
    sum(..) AS can2017, sum(..) AS can2018 ...
FROM
    tabelaCanal...

Then merge them, and use in an aggregate query by grouping the description:

SELECT
    descricao, 
    sum(cli2017) AS cli2017, sum(cli2018) AS cli2018 ...
    sum(rep2017) AS rep2017, sum(rep2018) AS rep2018 ...
    sum(can2017) AS can2017, sum(can2018) AS can2018 ...
FROM
    uniãoGigante
GROUP BY
    descricao