I am also having a UNION issue with CR 6 and a report. I get the same error message as 1st post (nevets72). I removed the text after the UNION statement and the report ran. However, when I put that portion back - it stopped.
My original SQL stmt produced summary values that did not match another report (same data sought from tables.) I ran stmts in Query Analyzer and copied to Excel. The troubled report gave duplicate data in Excel.
Goal: tried to use the following stmt to give me distinct data to resolve the problem:
Here is the SQL:
SELECT
ARTran."CustId", ARTran."DrCr", ARTran."InvtId", ARTran."PerPost", ARTran."Qty", ARTran."RecordID", ARTran."SiteId", ARTran."UnitDesc",
Customer."Name", Customer."State",
Inventory."Descr",
RptCompany."CpnyName"
FROM
{ oj ((("TFCAPP"."dbo"."ARTran" ARTran INNER JOIN "TFCAPP"."dbo"."ARDoc" ARDoc ON
ARTran."RefNbr" = ARDoc."RefNbr"

INNER JOIN "TFCAPP"."dbo"."Inventory" Inventory ON
ARTran."InvtId" = Inventory."InvtId"

INNER JOIN "TFCAPP"."dbo"."Customer" Customer ON
ARTran."CustId" = Customer."CustId"

INNER JOIN "TFCAPP"."dbo"."RptCompany" RptCompany ON
ARDoc."CpnyID" = RptCompany."CpnyID"}
WHERE
1=0
union
SELECT distinct
ARTran."CustId", ARTran."DrCr", ARTran."InvtId", ARTran."PerPost", ARTran."Qty", ARTran."RecordID", ARTran."SiteId", ARTran."TranAmt", ARTran."TranType", ARTran."UnitDesc",
Customer."Name", Customer."State",
Inventory."ClassId", Inventory."Descr",
RptCompany."CpnyName"
FROM
{ oj ((("TFCAPP"."dbo"."ARTran" ARTran INNER JOIN "TFCAPP"."dbo"."ARDoc" ARDoc ON
ARTran."RefNbr" = ARDoc."RefNbr"

INNER JOIN "TFCAPP"."dbo"."Inventory" Inventory ON
ARTran."InvtId" = Inventory."InvtId"

INNER JOIN "TFCAPP"."dbo"."Customer" Customer ON
ARTran."CustId" = Customer."CustId"

INNER JOIN "TFCAPP"."dbo"."RptCompany" RptCompany ON
ARDoc."CpnyID" = RptCompany."CpnyID"}
WHERE
(ARTran."TranType" = 'IN' OR
ARTran."TranType" = 'CM') AND
(Inventory."ClassId" <> 'PKG ' AND
Inventory."ClassId" <> 'MISC ' AND
Inventory."ClassId" <> 'FRT ' AND
Inventory."ClassId" <> 'DESCR ' AND
Inventory."ClassId" <> 'DESC' AND
Inventory."ClassId" <> 'BOX ' AND
Inventory."ClassId" <> 'BAG ' AND
Inventory."ClassId" <> 'FRGT ')
ORDER BY
ARTran."InvtId" ASC,
ARTran."CustId" ASC,
ARTran."RecordID" ASC
Any advice would be appreciated!!