Join Sales Header & Sales Invoice Header

jwilder

How can I join the Sales Header and Sales Invoice Header in a single query and only return a distinct selection of order no's? I started with an Union query but it is not getting me a distinct list of all orders. Here is what I have so far:
Select SIH.[Order Date],
Case SIH.[Order Type]
When 0 Then 'Catalog'
When 1 Then 'Wholesale'
End,
SIH.[Order No_],
'SIH'
From [SWKTestWeb$Sales Invoice Header] As SIH
Where SIH.[Order Date] between '09/01/09' and '09/30/09' and SIH.[Order No_] <> ''
Union
select SH.[Order Date],
Case SH.[Order Type]
When 0 Then 'Catalog'
When 1 Then 'Wholesale'
End,
SH.No_,
'SH'
From [SWKTestWeb$Sales Header] as SH
Where SH.[Document Type] = 1

Any thoughts?

Cem_Karaer

Hello,

Doesn't the following code run correctly?

Select DISTINCT
SIH.[Order No_],
From [SWKTestWeb$Sales Invoice Header] As SIH
Where SIH.[Order Date] between '09/01/09' and '09/30/09' and SIH.[Order No_] <> ''

jwilder

That would only select order no's from posted invoices. If an order hasn't shipped yet this would miss those.

davmac1

add a where not exists (in the sales invoice header)to your second select statement fdrom the sales header