Crystal Report with LEFT JOIN and WHERE

2

I have a problem making a LEFT JOIN in Crystal Reports. From what I've researched, it's a classic SQL problem when using WHERE criteria by crossing LEFT JOIN . I tried to solve it anyway, but I could not.

There are 4 related tables: [OrcHDR] [Production Order] [ItemNote] [Fiscal Notes]

I am interested in a report with all OP requests, INCLUDING those that have not yet been invoiced, with the exception of some that are specified

Here is the SQL:

SELECT 
"OrcHdr"."SPreco", 
"OrcHdr"."SCustosMat", 
"OrcHdr"."SCustosMO", 
"OrcHdr"."SCustosTerc", 
"OrcHdr"."SCustosImpostos", 
"OrcHdr"."SCustosComissoes", 
"OrcHdr"."SCustosFin", 
"OrcHdr"."SCustosVenOutros", 
"OrcHdr"."NomeAgencia", 
"OrdensProducao"."NumOrdem", 
"OrdensProducao"."Descricao", 
"OrdensProducao"."NomeCliente", 
"OrdensProducao"."TipoProduto", 
"ItemNota"."Fatura", 
"ItemNota"."Devolucao", 
"OrdensProducao"."DtEmissao", 
"ItemNota"."ValorTotal", 
"NotasFiscais"."NaturezaOperacao", 
"NotasFiscais"."Situacao", 
"OrcHdr"."NumOrcamento", 
"ItemNota"."NumOrdem"

 FROM   

(("TABELA"."dbo"."OrcHdr" "OrcHdr" 

INNER JOIN 

"TABELA"."dbo"."OrdensProducao" "OrdensProducao" ON 
"OrcHdr"."NumOrcamento"="OrdensProducao"."NumOrdem") 

LEFT OUTER JOIN 

"TABELA"."dbo"."ItemNota" "ItemNota" ON "OrdensProducao"."NumOrdem"="ItemNota"."NumOrdem") 

INNER JOIN 

"TABELA"."dbo"."NotasFiscais" "NotasFiscais" ON "ItemNota"."ObjID_Nota"="NotasFiscais"."ObjID"

 WHERE  

("ItemNota"."Devolucao" IS  NULL  OR "ItemNota"."Devolucao"<>'D') AND ("ItemNota"."Fatura" IS  NULL  OR "ItemNota"."Fatura"='F') AND 
("NotasFiscais"."Situacao" IS  NULL  OR "NotasFiscais"."Situacao"='N') AND ("NotasFiscais"."NaturezaOperacao" IS  NULL  OR  NOT ("NotasFiscais"."NaturezaOperacao" LIKE 'doa%' OR "NotasFiscais"."NaturezaOperacao" LIKE 'reme%'))

ORDER BY "OrcHdr"."NumOrcamento"

What is really happening is that I only get the OPs that have already been invoiced, that is, they are present in the tables [ItemNote] and [PartyNotes].

What am I doing wrong?

    
asked by anonymous 04.12.2014 / 18:43

1 answer

1

I ended up solving my problem by changing the relationship orders from the tables.

Before they were like this:

[Orçamento]-[OrdemProducao] -> [ItemPedido]-[NotaFiscal]

Modified to:

[Orçamento]-[OrdemProducao] -> [ItemPedido]
                            -> [NotaFiscal]

And it all worked.

Thanks to those who collaborated!

    
05.12.2014 / 14:50