[firebase-br] RES: RES: SQL Join com três tabelas

[Eduardo Pombo]

isso ja deve melhorar pelo menos.


Select
 PRO.CDPRO,
 LAN.GRADE,
 L.QUANT as QNTULTCOM,
 PRO.CDGRD,
 PRO.NMPRO,
 PRO.CDUND,
 PROC.SALDO,
 PRO.PONTO,
 PRO.QTMAX,
 PRO.LOCAL,
 sum(lan.quant * pro.vldia) vlcompras,
 Sum(LAN.VALOR * LAN.QUANT * (1 - (FAT.VALDE / FAT.VALNO))) VALOR,
 Sum(LAN.QUANT) QUANT,
 Sum(LAN.QUANT * PRO.QTMAX) PESOBRUTO, MAX(FAT.DTEMI) ULTVENDA,
 Sum(LAN.QUANT *
 LAN.PRECU) VLPRECU
>From IN01LAN LAN
  Left Join IN01PRO PRO on PRO.CDPRO = LAN.CDPRO
  Left Join IN01FAT FAT on FAT.FATUR = LAN.NOTFI
  Left Join IN01CLI CLI on CLI.CDCLI = FAT.CDCLI
  Left join IN01LAN L   on L.CDPRO   = LAN.CDPRO and l.notfi = (select
first 1 la.notfi from in01lan la where not la.cdfrn is null and
la.cdpro = lan.cdpro order by la.dtemi desc),
SP_GETSALDOGRADE(PRO.CDPRO, LAN.GRADE) PROC
Where (FAT.VALNO > 0)
  and (lan.venda in ('V','P'))
  and (lan.dtpro >= '10.09.2010')
  and (lan.dtpro <= '10.09.2010')
Group by
  PRO.CDPRO,
  LAN.GRADE,
  L.QUANT,
  PRO.CDGRD,
  PRO.NMPRO,
  PRO.CDUND,
  SALDO,
  PRO.PONTO,
  PRO.QTMAX,
  PRO.LOCAL
order by pro.nmpro


2010/9/13 Levy Moreira <levymoreira.ce em gmail.com>:
> O select completo é esse:
>
> Select PRO.CDPRO, LAN.GRADE, L.QUANT as QNTULTCOM, PRO.CDGRD, PRO.NMPRO,
> PRO.CDUND,
>  PROC.SALDO, PRO.PONTO, PRO.QTMAX, PRO.LOCAL,
> sum(lan.quant * pro.vldia) vlcompras,
> Sum(LAN.VALOR * LAN.QUANT * (1 - (FAT.VALDE / FAT.VALNO))) VALOR,
> Sum(LAN.QUANT) QUANT,
>  Sum(LAN.QUANT * PRO.QTMAX) PESOBRUTO, MAX(FAT.DTEMI) ULTVENDA,
> Sum(LAN.QUANT *
>  LAN.PRECU) VLPRECU
> From IN01LAN LAN  Left Join IN01PRO PRO on PRO.CDPRO = LAN.CDPRO     Left
> Join IN01FAT FAT on
> FAT.FATUR = LAN.NOTFI     Left Join IN01CLI CLI on CLI.CDCLI =
> FAT.CDCLI      Left join IN01LAN L   on
>  L.CDPRO   = LAN.CDPRO,     SP_GETSALDOGRADE(PRO.CDPRO, LAN.GRADE) PROC
> Where (FAT.VALNO > 0)
> and l.notfi = (select first 1 la.notfi from in01lan la where not la.cdfrn is
> null and la.cdpro = lan.cdpro order
> by la.dtemi desc)
> and (lan.venda in ('V','P')) and (lan.dtpro >= '10.09.2010') and (lan.dtpro
> <= '10.09.2010')
> Group by PRO.CDPRO, LAN.GRADE, L.QUANT, PRO.CDGRD, PRO.NMPRO, PRO.CDUND,
> SALDO, PRO.PONTO, PRO.QTMAX, PRO.LOCAL
> order by pro.nmpro
>
> se eu comentar esta linha percebi que fica rapido
> and l.notfi = (select first 1 la.notfi from in01lan la where not la.cdfrn is
> null and la.cdpro = lan.cdpro order
> by la.dtemi desc)
>
>
> e agora?
> ______________________________________________
> FireBase-BR (www.firebase.com.br) - Hospedado em www.locador.com.br
> Para saber como gerenciar/excluir seu cadastro na lista, use: http://www.firebase.com.br/fb/artigo.php?id=1107
> Para consultar mensagens antigas: http://firebase.com.br/pesquisa
>



-- 

Atenciosamente

José Eduardo Pombo de Barros
Gerente de Manutenção de Sistemas