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?
select
[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)
union
select
[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
