BAQ Orders not invoiced or invoiced before a date

I am trying to do a BAQ returning Sales Orders:

  • invoiced after a certain date (Top Level SubQuery 1) or
  • not invoiced (union SubQuery 2) (test : Invoicenum IsNull)
    Each subquery functions well when ran by themselves, alone.
    When I put them together, results are funky. Invoiced orders show a blank invoice number, invoices invoiced before the cut off are included.

I do not know what I am doing wrong. Can someone help?

[Customer1].[Name] as [Customer1_Name],
[OrderHed1].[WardsOrder_c] as [OrderHed1_WardsOrder_c],
[InvcHead1].[OrderNum] as [InvcHead1_OrderNum],
[InvcHead1].[InvoiceDate] as [InvcHead1_InvoiceDate],
[InvcHead1].[InvoiceNum] as [InvcHead1_InvoiceNum]
from Erp.InvcHead as InvcHead1
inner join Erp.OrderHed as OrderHed1 on
InvcHead1.Company = OrderHed1.Company
and InvcHead1.OrderNum = OrderHed1.OrderNum
and ( OrderHed1.OpenOrder = False )

inner join Erp.Customer as Customer1 on
OrderHed1.Company = Customer1.Company
and OrderHed1.BTCustNum = Customer1.CustNum
where (InvcHead1.InvoiceDate > @MonthEndDate)
[Customer].[Name] as [Customer_Name],
[OrderHed].[WardsOrder_c] as [OrderHed_WardsOrder_c],
[InvcHead].[OrderNum] as [InvcHead_OrderNum],
[InvcHead].[InvoiceDate] as [InvcHead_InvoiceDate],
[InvcHead].[InvoiceNum] as [InvcHead_InvoiceNum]
from Erp.InvcHead as InvcHead
right outer join Erp.OrderHed as OrderHed on
InvcHead.Company = OrderHed.Company
and InvcHead.OrderNum = OrderHed.OrderNum
and ( OrderHed.OpenOrder = True )

inner join Erp.Customer as Customer on
OrderHed.Company = Customer.Company
and OrderHed.CustNum = Customer.CustNum
where (InvcHead.InvoiceNum is null)
order by InvcHead1.OrderNum

Eventually found the way to do it. Used a subquery listing all the orders that had been invoiced. The top level query getting its orders numbers Not “IN” field from SubQuery…

In case this is useful to someone.