Crystal Report with LEFT JOIN and WHERE

Asked

Viewed 257 times

2

I’m having a problem making one LEFT JOIN in Crystal Reports. From what I’ve researched, it’s a classic SQL problem when using criteria WHERE crossing with the LEFT JOIN. I tried to solve every way, but I couldn’t.

There are 4 related tables: [Orchdr] [Ordemproduction] [Itemnota] [Notasfiscais]

I am interested in a report with all orders with OP, INCLUDING those that have not yet been invoiced, except for 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 actually happening is that I only get the Ops that have already been invoiced, that is, that are present in the tables [Itemnota] and [Notasficais].

What am I doing wrong?

c# sql crystal-reports join

  • Are you sure it’s AND even, not OR ? (I’m guessing, details are missing on your question of what you need to happen).

  • It is AND even, all conditions must be met at the same time. What I need is a report, in a given time interval, with information from all "Ordensproducao"." Numordem", including those not present in Itemnota.

  • Could be the type of Join chosen, no? See if this helps: What is the difference between Ner Join and Uter Join?

  • The Crystal Report entered history with Pilate in the Creed as the ancient say ...

1 answer

1

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

Before they were like this:

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

I modified it to:

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

And it all worked out.

Thanks to those who cooperated!

  • Cassiano, as you discovered yourself, the order does matter. The rule is simple: first the joins and then the joins.

Browser other questions tagged c# sql crystal-reports join

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