sql syntax error

>having Customer.GroupCode in ('TDIS', 'TDS1', 'TRES', 'TRS1', 'TRAF', 'TRDS')
This should be in a WHERE clause, as WHERE is for filtering single values, whereas HAVING is for filtering based on aggregated values like SUM(Salary), COUNT(the_money), etc.

Fixed,

I connected customer table different way.

thank you

Let us know what the fix was, so we can accept that as the answer so others can learn.   Otherwise, the only syntax I see is the HAVING clause should be in the WHERE.

The last inner join has  AND instead of ON.

hehe, subtle problems are often the hardest to spot

However Jim is correct about the having clause, the WHERE clause is for stuff you can do BEFORE the group by, the having clause is for stuff you ca only do AFTER the group by

for example you can use a having caluse to evaluate something like this

having SUM(amount) > 1000

you cannot do that in a where clause

=============

BUT ALSO

a. Why are you using a CROSS JOIN on customers?
b. you have used this twice INNER JOIN Erp.OrderHed AS OrderHed ON I'm not sure why. You can use an inner join to customer to this table and remove the cross join. See line 19

SELECT
      [ShipDtl].[OrderNum]   AS [ShipDtl_OrderNum]
    , [ShipHead].[ShipDate]  AS [ShipHead_ShipDate]
    , [Customer].[GroupCode] AS [Customer_GroupCode]
    , [ShipTo].[State]       AS [ShipTo_State]
    , [ShipTo].[Name]        AS [ShipTo_Name]
    , [OrderHed].[UseOTS]    AS [OrderHed_UseOTS]
    , [OrderHed].[OTSName]   AS [OrderHed_OTSName]
    , [OrderHed].[OTSState]  AS [OrderHed_OTSState]
FROM Erp.ShipHead AS ShipHead
      INNER JOIN Erp.ShipDtl AS ShipDtl ON ShipHead.CustNum = ShipDtl.CustNum
                  AND ShipHead.PackNum = ShipDtl.PackNum

      INNER JOIN Erp.ShipTo AS ShipTo ON ShipDtl.Company = ShipTo.Company
                  AND ShipDtl.CustNum = ShipTo.CustNum
                  AND ShipDtl.ShipToNum = ShipTo.ShipToNum

      INNER JOIN Erp.OrderHed AS OrderHed ON ShipDtl.OrderNum = OrderHed.OrderNum
      INNER JOIN Erp.Customer AS Customer ON OrderHed.CustNum = Customer.CustNum

WHERE ShipTo.State = 'PA'
      OR OrderHed.OTSState = 'PA'
      AND Customer.GroupCode IN ('TDIS', 'TDS1', 'TRES', 'TRS1', 'TRAF', 'TRDS')
GROUP BY
      [ShipDtl].[OrderNum]
    , [ShipHead].[ShipDate]
    , [Customer].[GroupCode]
    , [ShipTo].[State]
    , [ShipTo].[Name]
    , [OrderHed].[UseOTS]
    , [OrderHed].[OTSName]
    , [OrderHed].[OTSState]

Select allOpen in new window

Working syntax:

select 
	[ShipDtl].[OrderNum] as [ShipDtl_OrderNum],
	[ShipHead].[ShipDate] as [ShipHead_ShipDate],
	[Customer].[GroupCode] as [Customer_GroupCode],
	[ShipTo].[State] as [ShipTo_State],
	[ShipTo].[Name] as [ShipTo_Name],
	[OrderHed].[UseOTS] as [OrderHed_UseOTS],
	[OrderHed].[OTSName] as [OrderHed_OTSName],
	[OrderHed].[OTSState] as [OrderHed_OTSState]
from Erp.ShipHead as ShipHead
inner join Erp.ShipDtl as ShipDtl on 
	ShipHead.CustNum = ShipDtl.CustNum
And
	ShipHead.PackNum = ShipDtl.PackNum

inner join Erp.Customer as Customer on 
	ShipDtl.Company = Customer.Company
And
	ShipDtl.CustNum = Customer.CustNum

inner join Erp.ShipTo as ShipTo on 
	ShipDtl.Company = ShipTo.Company
And
	ShipDtl.CustNum = ShipTo.CustNum
And
	ShipDtl.ShipToNum = ShipTo.ShipToNum

inner join Erp.OrderHed as OrderHed on 
	ShipDtl.OrderNum = OrderHed.OrderNum

 where  ShipTo.State = 'PA'  or OrderHed.OTSState = 'PA' 

group by [ShipDtl].[OrderNum],
	[ShipHead].[ShipDate],
	[Customer].[GroupCode],
	[ShipTo].[State],
	[ShipTo].[Name],
	[OrderHed].[UseOTS],
	[OrderHed].[OTSName],
	[OrderHed].[OTSState]
having Customer.GroupCode in ('TDIS', 'TDS1', 'TRES', 'TRS1', 'TRAF', 'TRDS') and (ShipHead.ShipDate >= @StartDate and ShipHead.ShipDate <= @EndDate) 

Select allOpen in new window

I'm using Epicor ERP. That software has module called BAQ where yo can build query.Usually I'm building query over there, copy SQL syntax to report builder and finish report.
Sometimes sql syntax working in BAQ but not working in report builder.

Thank you for all comments.  

henry