Melhor Esse Sql
Enviado: Ter, 22 Nov 2016 9:43 am
Bom dia a Todos .
Estou tentado entender uma coisa, ao executar essa query abaixo , o sql demora para para retornar ,e se rodar pela aplicação a tabela temporária lota usando 33 giga , já revisei e não consigo entender por carga daguas ela faz isso.
Estou tentado entender uma coisa, ao executar essa query abaixo , o sql demora para para retornar ,e se rodar pela aplicação a tabela temporária lota usando 33 giga , já revisei e não consigo entender por carga daguas ela faz isso.
select
(select
to_char( NVL(SUM(A.AN+A.AG),0),'999G999G990D00')MONOFASICO_PIS_VENDA
from rodox_c100_c170_sped a, rodox_0200_sped2 b, rodox_ncm c
where a.a= 'C170'
and a.c=b.b
and b.h=c.ncm
and a.an> 0
AND A.L NOT IN (select CFOP from rodox_cfop
union
select CFOP from rodox_cfop2)
and trunc(a.ah) between '01' and '40')MONOFASICO_PIS_VENDA,
--cupom -monofasico com pis na venda
(select
to_char(NVL(sum(a.h),0),'999G999G990D00')MONOFASICO_CF_PIS_COFINS
from rodox_c100_c170_sped a, rodox_0200_sped2 b, rodox_ncm c
where a.a in ('C481','C485')
and a.i=b.b
and b.h=c.ncm
and a.h >0)MONOFASICO_CF_PIS_COFINS,
--cupom -tributavel sem pis
(select to_char (NVL(sum (((a.c*0.65)/100)+((a.c*3)/100)),0),'999G999G990D00')TRIBUTAVEL_CF_PIS_COFINS
from rodox_c100_c170_sped a, rodox_0200_sped2 b
where a.a in ('C481','C485')
and a.i=b.b
and b.h not in (select ncm from rodox_ncm )
and a.h =0)TRIBUTAVEL_CF_PIS_COFINS ,
--MONOFASICO_PIS_COMPRA
(select
TO_CHAR(NVL(SUM(A.AN+A.AG),0),'999G999G990D00')MONOFASICO_PIS_COMPRA
from rodox_c100_c170_sped a, rodox_0200_sped2 b, rodox_ncm c
where a.a= 'C170'
and a.c=b.b
and b.h=c.ncm
and a.an> 0
and trunc(a.ah) between '50' and '90')MONOFASICO_PIS_COMPRA,
--TRIBUTAVEL SEM /COFINS vendas
(select
to_char(NVL(SUM(((A.G*0.65)/100)+(A.G*3)/100),0),'999G999G990D00') PIS_COFINS
from rodox_c100_c170_sped a, rodox_0200_sped2 b
where a.a= 'C170'
and a.c=b.b
and b.h not in (select ncm from rodox_ncm )
and a.an = 0
and a.l not in (select CFOP from rodox_cfop
union
select CFOP from rodox_cfop2)
and trunc(a.ah) between '01' and '40')TRIBUTAVEL_PIS_VENDA,
--TRIBUTAVEL_sem_COFINS_COMPRA
(select
to_char(NVL(SUM(((A.G*0.65)/100)+(A.G*3)/100),0),'999G999G990D00') PIS_COFINS
from rodox_c100_c170_sped a, rodox_0200_sped2 b
where a.a= 'C170'
and a.c=b.b
and b.h not in (select ncm from rodox_ncm )
and a.an = 0
and a.l not in (select CFOP from rodox_cfop
union
select CFOP from rodox_cfop2)
and trunc(a.ah) between '50' and '90')TRIBUTAVEL_SEM_COFINS_COMPRA,
--065
(select TO_CHAR(NVL(SUM(A.AG+A.an),0),'999G999G990D00') AS PIS_COFINS
from rodox_c100_c170_sped a
where a.a= 'C170'
and a.ad <>'1,65'
and an >'0')VALOR_DIFERENTE,
--TRIBUTAVEL_na_venda
(select
to_char(NVL(SUM(A.AG+A.an),0),'999G999G990D00') AS PIS_COFINS
from rodox_c100_c170_sped a
where a.a= 'C170'
and trunc(a.ah) between '01' and '40')TRIBUTAVEL_VENDA,
--TRIBUTAVEL na compra
(select
NVL(SUM(A.AG+A.an),0) AS PIS_COFINS
from rodox_c100_c170_sped a
where a.a= 'C170'
and trunc(a.ah) between '50' and '90')TRIBUTAVEL_COMPRA,
--cupom monofasico + monofasico vendas
to_char((select
NVL(sum(a.h),0)MONOFASICO_CF_PIS_COFINS
from rodox_c100_c170_sped a, rodox_0200_sped2 b, rodox_ncm c
where a.a in ('C481','C485')
and a.i=b.b
and b.h=c.ncm
and a.h >0)+
(select
NVL(SUM(A.AN+A.AG),0)MONOFASICO_PIS_VENDA
from rodox_c100_c170_sped a, rodox_0200_sped2 b, rodox_ncm c
where a.a= 'C170'
and a.c=b.b
and b.h=c.ncm
and a.l not in (SELECT CFOP FROM RODOX_CFOP)
and a.an> 0
and trunc(a.ah) between '01' and '40'),'999G999G990D00' )TOTAL_MONOFASICO_PIS_VENDA,
---total tributavel sem pis venda
TO_CHAR((select
NVL(SUM(((A.G*0.65)/100)+(A.G*3)/100),0) PIS_COFINS
from rodox_c100_c170_sped a, rodox_0200_sped2 b
where a.a= 'C170'
and a.c=b.b
and b.h not in (select ncm from rodox_ncm )
and a.an = 0
and a.l not in (select CFOP from rodox_cfop
union
select CFOP from rodox_cfop2)
and trunc(a.ah) between '01' and '40')
+
(select NVL(sum (((a.c*0.65)/100)+((a.c*3)/100)),0)TRIBUTAVEL_CF_PIS_COFINS
from rodox_c100_c170_sped a, rodox_0200_sped2 b
where a.a in ('C481','C485')
and a.i=b.b
and b.h not in (select ncm from rodox_ncm )
and a.h =0),'999G999G990D00')TOTAL_TRIBUTAVEL_SEM_PIS
FROM DUAL