UNION issue within Crystal Reports 2

[nevets72]

Hi.....not sure if someone can help me or not....I made some changes to an SQL Query within Crystal Reports, specifically performing a UNION.....however, I get the following message:

(SQL Server)All queries in an SQL statement containing a UNION operator must have an equal number of expressions in their target lists.

Simple, right? Well, I've done other UNIONS for other reports, and they all work fine. I've checked, double checked, and had other people check my SQL for the report that I'm having problems with, and it's syntatically correct.....I have the same number of expressions in each SELECT statement......we believe that somewhere within Crystal Reports, something is defined in such a way that it's preventing me from performing a UNION within this one report....anyone have any ideas? Thanks!!!!

 

[balves]

Whatever you see in the Show SQL Query window should be the SQL going to the database. Hopefully, anyway.

I would try copying the Crystal SQL and pasting it into SQL Server Query Analyzer to see if it works. You might have to fix up the joins with respect to the { brackets that Crystal uses.

 

[nevets72]

Well, I did cut and paste it into a SQL query window, and it worked! That's why this is confusing.....something within Crystal Reports is preventing me from doing a UNION.....some property or condition has been set to prevent it as far as I can tell.....what that is, I have no idea, or no idea where to look. We searched through the different options on the menu bar and couldn't find anything!

 

[rogar]

Did you change the "Select" part of the query when you edited the SQL in the CR Show SQL Query window? That can cause problems. Go back and re-create the query in the Visual Linking Expert (without the UNION) and then add the UNION without changing the Select part of the original query. Or consider putting your entire query into an SQL Server stored procedure and rebuild your report on that.

 

[Ngolem]

Paste the query here and let us see it Jim

JimBroadbent@Hotmail.com

 

[walterk123]

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&quot
INNER JOIN "TFCAPP"."dbo"."Inventory" Inventory ON
ARTran."InvtId" = Inventory."InvtId&quot
INNER JOIN "TFCAPP"."dbo"."Customer" Customer ON
ARTran."CustId" = Customer."CustId&quot
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&quot
INNER JOIN "TFCAPP"."dbo"."Inventory" Inventory ON
ARTran."InvtId" = Inventory."InvtId&quot
INNER JOIN "TFCAPP"."dbo"."Customer" Customer ON
ARTran."CustId" = Customer."CustId&quot
INNER JOIN "TFCAPP"."dbo"."RptCompany" RptCompany ON
ARDoc."CpnyID" = RptCompany."CpnyID"}
WHERE
(ARTran."TranType" = 'IN' OR
ARTran."TranType" = 'CM') AND
(Inventory.&quot;ClassId&quot; <> 'PKG ' AND
Inventory.&quot;ClassId&quot; <> 'MISC ' AND
Inventory.&quot;ClassId&quot; <> 'FRT ' AND
Inventory.&quot;ClassId&quot; <> 'DESCR ' AND
Inventory.&quot;ClassId&quot; <> 'DESC' AND
Inventory.&quot;ClassId&quot; <> 'BOX ' AND
Inventory.&quot;ClassId&quot; <> 'BAG ' AND
Inventory.&quot;ClassId&quot; <> 'FRGT ')
ORDER BY
ARTran.&quot;InvtId&quot; ASC,
ARTran.&quot;CustId&quot; ASC,
ARTran.&quot;RecordID&quot; ASC

Any advice would be appreciated!!

 

[synapsevampire]

You have different fields in your selects, right off the bat I saw Inventory.&quot;ClassId&quot; where state is in the previous select.

-k

 

[DonErnesto]

We had a similar issue with the Union Statment from MS SQL.
Since we had a very tight time constraind, I wound up using the Query with the union statement to create a view, and have Crystal Reports treat the view as just another table.
A bit kludgy, I know, but it got us through.