Ir para conteúdo

Arquivado

Este tópico foi arquivado e está fechado para novas respostas.

paulo.chagas

Subquery

Por , em Oracle

Recommended Posts

paulo.chagas    1

paulo.chagas
Postado

Bom dia pessoal, meu sql está funcionando, mas preciso talvez desmembrá-lo, talvez uma subquery.

Ele está me retornando duas linhas para o código que estou utilizando como base, mas preciso que ele me retorne 4 linhas!!!

As duas linhas que estão faltando, não listam pq o cliente não possui nota fiscal, e no meu caso, minha tela utiliza de filtro, a data de emissão de nota fiscal!

Preciso trazer as seguintes colunas

 

e085cli.codgre AS Grupo_Empresa,
e085cli.codcli AS Codigo_Empresa,
e085cli.nomcli AS Razao_social,
e085cli.apecli AS Nome_fantasia,
e085cli.usu_siterevofi AS Site,
e085cli.usu_foracap AS Fora_da_Capital,
e085cli.usu_servico Oferece_Servico,
e085cli.usu_fachada AS Tem_Fachada,

 

O código completo é esse abaixo:

SELECT
               e085cli.codgre AS Grupo_Empresa,
               e085cli.codcli AS Codigo_Empresa,
               e085cli.nomcli AS Razao_social,
               e085cli.apecli AS Nome_fantasia,
               e085cli.usu_siterevofi AS Site,
               e085cli.usu_foracap AS Fora_da_Capital,
               e085cli.usu_servico Oferece_Servico,
               e085cli.usu_fachada AS Tem_Fachada,
               e085cli.usu_cartafian AS Carta_de_Fianca,
               Sum(e140ipv.qtdfat * e075pro.usu_ponfidel) AS pontos,
               e085cli.usu_crepon,
               e085hcl.catcli,
               e085cli.usu_pontgru,
               MAX(e085cli.usu_siterevofi) OVER (PARTITION BY e085cli.codgre) AS pontos_tem_site,
               MAX(e085cli.usu_foracap) OVER (PARTITION BY e085cli.codgre) AS pontos_fora_capital,
               MAX(e085cli.usu_servico) OVER (PARTITION BY e085cli.codgre) AS pontos_oferece_servico,
               MAX(e085cli.usu_fachada) OVER (PARTITION BY e085cli.codgre) AS pontos_tem_fachada,
               MAX(e085cli.usu_cartafian) OVER (PARTITION BY e085cli.codgre) AS pontos_carta_fianca,
               LAG (e085cli.codgre, 1, 0) OVER (ORDER BY e085cli.codgre desc) AS grupo_anterior,
               LEAD(e085cli.codgre, 1, 0) OVER (ORDER BY e085cli.codgre desc) AS proximo_grupo,
               e085cli.sigufs,
               e069gre.nomgre
   FROM e085cli e085cli
       JOIN e069gre e069gre ON ( e085cli.codgre = e069gre.codgre )
       JOIN e140nfv e140nfv ON ( e085cli.codcli = e140nfv.codcli )
       JOIN e085hcl e085hcl ON ( e085hcl.codcli = e140nfv.codcli AND e085hcl.codemp = e140nfv.codemp AND e085hcl.codfil = e140nfv.codfil )
       JOIN e140ipv e140ipv ON ( e140nfv.codemp = e140ipv.codemp AND e140nfv.numnfv = e140ipv.numnfv AND e140nfv.codsnf = e140ipv.codsnf )
       JOIN e140ide e140ide ON ( e140ide.codemp = e140ipv.codemp AND e140ide.numnfv = e140ipv.numnfv AND e140ide.codsnf = e140ipv.codsnf )
       JOIN e075pro e075pro ON ( e075pro.codemp = e140ipv.codemp AND e075pro.codpro = e140ipv.codpro )
   WHERE e140nfv.datemi BETWEEN To_date('01/12/2014', 'DD/MM/YYYY') and To_date('31/01/2015', 'DD/MM/YYYY')  AND e085cli.codgre = 71
       GROUP BY e085cli.codgre,
                   e085cli.codcli,
                   e085cli.nomcli,
                   e085cli.apecli,
                   e085cli.usu_siterevofi,
                   e085cli.usu_foracap,
                   e085cli.usu_servico,
                   e085cli.usu_fachada,
                   e085cli.usu_cartafian,
                   e085cli.usu_crepon,
                   e085hcl.catcli,
                   e085cli.usu_pontgru,
                   e085cli.sigufs,
                   e069gre.nomgre
       ORDER BY e085cli.codgre desc,
                   e085cli.usu_siterevofi DESC,
                   e085cli.usu_foracap DESC,
                   e085cli.usu_servico DESC,
                   e085cli.usu_fachada DESC,
                   e085cli.usu_cartafian DESC

 

 

 

 

Compartilhar este post

Link para o post
Compartilhar em outros sites

rodrigoords    1

rodrigoords
Postado

Olá,

você pode fazer algo como:

select *
from clientes cli
left join notaFiscal nf on (nf.idcli = cli.cod_cli and
nf.data between '21/01/2015' and '23/01/2015')
Acho que seu codigo deve funcionar da seguinte forma :
SELECT
e085cli.codgre AS Grupo_Empresa,
e085cli.codcli AS Codigo_Empresa,
e085cli.nomcli AS Razao_social,
e085cli.apecli AS Nome_fantasia,
e085cli.usu_siterevofi AS Site,
e085cli.usu_foracap AS Fora_da_Capital,
e085cli.usu_servico Oferece_Servico,
e085cli.usu_fachada AS Tem_Fachada,
e085cli.usu_cartafian AS Carta_de_Fianca,
Sum(e140ipv.qtdfat * e075pro.usu_ponfidel) AS pontos,
e085cli.usu_crepon,
e085hcl.catcli,
e085cli.usu_pontgru,
MAX(e085cli.usu_siterevofi) OVER (PARTITION BY e085cli.codgre) AS pontos_tem_site,
MAX(e085cli.usu_foracap) OVER (PARTITION BY e085cli.codgre) AS pontos_fora_capital,
MAX(e085cli.usu_servico) OVER (PARTITION BY e085cli.codgre) AS pontos_oferece_servico,
MAX(e085cli.usu_fachada) OVER (PARTITION BY e085cli.codgre) AS pontos_tem_fachada,
MAX(e085cli.usu_cartafian) OVER (PARTITION BY e085cli.codgre) AS pontos_carta_fianca,
LAG (e085cli.codgre, 1, 0) OVER (ORDER BY e085cli.codgre desc) AS grupo_anterior,
LEAD(e085cli.codgre, 1, 0) OVER (ORDER BY e085cli.codgre desc) AS proximo_grupo,
e085cli.sigufs,
e069gre.nomgre
FROM e085cli e085cli
JOIN e069gre e069gre ON ( e085cli.codgre = e069gre.codgre )
LEFT JOIN e140nfv e140nfv ON ( e085cli.codcli = e140nfv.codcli and
e140nfv.datemi BETWEEN To_date('01/12/2014', 'DD/MM/YYYY') and To_date('31/01/2015', 'DD/MM/YYYY') )
JOIN e085hcl e085hcl ON ( e085hcl.codcli = e140nfv.codcli
AND e085hcl.codemp = e140nfv.codemp
AND e085hcl.codfil = e140nfv.codfil )
JOIN e140ipv e140ipv ON ( e140nfv.codemp = e140ipv.codemp
AND e140nfv.numnfv = e140ipv.numnfv
AND e140nfv.codsnf = e140ipv.codsnf )
JOIN e140ide e140ide ON ( e140ide.codemp = e140ipv.codemp
AND e140ide.numnfv = e140ipv.numnfv
AND e140ide.codsnf = e140ipv.codsnf )
JOIN e075pro e075pro ON ( e075pro.codemp = e140ipv.codemp
AND e075pro.codpro = e140ipv.codpro )
WHERE e085cli.codgre = 71
GROUP BY e085cli.codgre,
e085cli.codcli,
e085cli.nomcli,
e085cli.apecli,
e085cli.usu_siterevofi,
e085cli.usu_foracap,
e085cli.usu_servico,
e085cli.usu_fachada,
e085cli.usu_cartafian,
e085cli.usu_crepon,
e085hcl.catcli,
e085cli.usu_pontgru,
e085cli.sigufs,
e069gre.nomgre
ORDER BY e085cli.codgre desc,
e085cli.usu_siterevofi DESC,
e085cli.usu_foracap DESC,
e085cli.usu_servico DESC,
e085cli.usu_fachada DESC,
e085cli.usu_cartafian DESC

Compartilhar este post

Link para o post
Compartilhar em outros sites

paulo.chagas    1

paulo.chagas
Postado

 

Olá,

você pode fazer algo como:

select *
from clientes cli
left join notaFiscal nf on (nf.idcli = cli.cod_cli and
nf.data between '21/01/2015' and '23/01/2015')
Acho que seu codigo deve funcionar da seguinte forma :
SELECT
e085cli.codgre AS Grupo_Empresa,
e085cli.codcli AS Codigo_Empresa,
e085cli.nomcli AS Razao_social,
e085cli.apecli AS Nome_fantasia,
e085cli.usu_siterevofi AS Site,
e085cli.usu_foracap AS Fora_da_Capital,
e085cli.usu_servico Oferece_Servico,
e085cli.usu_fachada AS Tem_Fachada,
e085cli.usu_cartafian AS Carta_de_Fianca,
Sum(e140ipv.qtdfat * e075pro.usu_ponfidel) AS pontos,
e085cli.usu_crepon,
e085hcl.catcli,
e085cli.usu_pontgru,
MAX(e085cli.usu_siterevofi) OVER (PARTITION BY e085cli.codgre) AS pontos_tem_site,
MAX(e085cli.usu_foracap) OVER (PARTITION BY e085cli.codgre) AS pontos_fora_capital,
MAX(e085cli.usu_servico) OVER (PARTITION BY e085cli.codgre) AS pontos_oferece_servico,
MAX(e085cli.usu_fachada) OVER (PARTITION BY e085cli.codgre) AS pontos_tem_fachada,
MAX(e085cli.usu_cartafian) OVER (PARTITION BY e085cli.codgre) AS pontos_carta_fianca,
LAG (e085cli.codgre, 1, 0) OVER (ORDER BY e085cli.codgre desc) AS grupo_anterior,
LEAD(e085cli.codgre, 1, 0) OVER (ORDER BY e085cli.codgre desc) AS proximo_grupo,
e085cli.sigufs,
e069gre.nomgre
FROM e085cli e085cli
JOIN e069gre e069gre ON ( e085cli.codgre = e069gre.codgre )
LEFT JOIN e140nfv e140nfv ON ( e085cli.codcli = e140nfv.codcli and
e140nfv.datemi BETWEEN To_date('01/12/2014', 'DD/MM/YYYY') and To_date('31/01/2015', 'DD/MM/YYYY') )
JOIN e085hcl e085hcl ON ( e085hcl.codcli = e140nfv.codcli
AND e085hcl.codemp = e140nfv.codemp
AND e085hcl.codfil = e140nfv.codfil )
JOIN e140ipv e140ipv ON ( e140nfv.codemp = e140ipv.codemp
AND e140nfv.numnfv = e140ipv.numnfv
AND e140nfv.codsnf = e140ipv.codsnf )
JOIN e140ide e140ide ON ( e140ide.codemp = e140ipv.codemp
AND e140ide.numnfv = e140ipv.numnfv
AND e140ide.codsnf = e140ipv.codsnf )
JOIN e075pro e075pro ON ( e075pro.codemp = e140ipv.codemp
AND e075pro.codpro = e140ipv.codpro )
WHERE e085cli.codgre = 71
GROUP BY e085cli.codgre,
e085cli.codcli,
e085cli.nomcli,
e085cli.apecli,
e085cli.usu_siterevofi,
e085cli.usu_foracap,
e085cli.usu_servico,
e085cli.usu_fachada,
e085cli.usu_cartafian,
e085cli.usu_crepon,
e085hcl.catcli,
e085cli.usu_pontgru,
e085cli.sigufs,
e069gre.nomgre
ORDER BY e085cli.codgre desc,
e085cli.usu_siterevofi DESC,
e085cli.usu_foracap DESC,
e085cli.usu_servico DESC,
e085cli.usu_fachada DESC,
e085cli.usu_cartafian DESC

 

 

 

 

 

 

Eu já havia tentado com left join, mas não traz as outras linhas =\

Compartilhar este post

Link para o post
Compartilhar em outros sites

paulo.chagas    1

paulo.chagas
Postado

Você pode tentar criar um subselect que traga somente as notas filtradas pela data, e fazer o left com o restante da query.

 

 

 

Fiz os outros JOINS com LEFT e resolveu!!!! Valeu Rodrigo! Abraço

Compartilhar este post

Link para o post
Compartilhar em outros sites
Ir para a lista de tópicos Oracle
×

Informação importante

Ao usar o fórum, você concorda com nossos Termos e condições.

  Aceito