Joined: 06 Oct 2013
Location: United Kingdom
Online Status: Offline
Posts: 66
Topic: Combining 2 reports using UNION equivalent Posted: 09 May 2014 at 12:08am
Hi,
Ive got 2 crystal reports which I would like to combine as one and remove duplicates. I designed my queries within Access and then just replicated this within Crystal reports but I cant seem to find out how you "merge" the two single Crystal reports into one. I looked in the help but it mentioned about sets - haven't a clue what they are.
Joined: 06 Oct 2013
Location: United Kingdom
Online Status: Offline
Posts: 66
Posted: 11 May 2014 at 9:50pm
Originally posted by Sastry
Hi
Is these two reports fields names and data types are same ? If you want to use UNION both queries should return same number of fields and field types.
If so, for each report go in Database Menu--Show SQL query-- copy the query into note pad and use UNION to join both queries.
Select a,b,c,d from xyz where ...
...
...
UNION
Select a,b,c,d from zyx where...
Thanks but once I have the complete code as per copy from notepad how do I then create a new report which only contains the total "Union" code? As within the show sql you cant edit it only view it?
Joined: 06 Oct 2013
Location: United Kingdom
Online Status: Offline
Posts: 66
Posted: 11 May 2014 at 9:57pm
Am I correct in thinking you do it at the point of creation and add the SQL to a command?
If so I have tried this as per the code below:
SELECT "PO_Header"."PO", "PO_Header"."Issued_By", "User_Values"."Text1", "Source"."Material", "Source"."Description", "PO_Detail"."Order_Quantity", "PO_Detail"."Unit_Cost", "PO_Header"."Order_Date", "Vendor"."Name"
FROM "PRODUCTION"."dbo"."Source" "Source" INNER JOIN ((("PRODUCTION"."dbo"."PO_Header" "PO_Header" INNER JOIN "PRODUCTION"."dbo"."Vendor" "Vendor" ON "PO_Header"."Vendor"="Vendor"."Vendor") INNER JOIN "PRODUCTION"."dbo"."PO_Detail" "PO_Detail" ON "PO_Header"."PO"="PO_Detail"."PO") INNER JOIN "PRODUCTION"."dbo"."User_Values" "User_Values" ON "PO_Header"."User_Values"="User_Values"."User_Values") ON "Source"."PO_Detail"="PO_Detail"."PO_Detail"
UNION
SELECT "PO_Header"."PO", "Vendor"."Name", "PO_Header"."Order_Date", "PO_Header"."Issued_By", "Job"."Job", "Material_Req"."Material", "Material_Req"."Description", "PO_Detail"."Order_Quantity", "PO_Detail"."Unit_Cost"
FROM (((("PRODUCTION"."dbo"."Job" "Job" INNER JOIN "PRODUCTION"."dbo"."Material_Req" "Material_Req" ON "Job"."Job"="Material_Req"."Job") INNER JOIN "PRODUCTION"."dbo"."Source" "Source" ON "Material_Req"."Material_Req"="Source"."Material_Req") INNER JOIN "PRODUCTION"."dbo"."PO_Detail" "PO_Detail" ON "Source"."PO_Detail"="PO_Detail"."PO_Detail") INNER JOIN "PRODUCTION"."dbo"."PO_Header" "PO_Header" ON "PO_Detail"."PO"="PO_Header"."PO") LEFT OUTER JOIN "PRODUCTION"."dbo"."Vendor" "Vendor" ON "PO_Header"."Vendor"="Vendor"."Vendor"
And I get an error Failed to retrieve data from database. Details 42000 Microsoft ODBC SQL SERVER DRIVER SQL Server Error converting data type varchar to float.
You cannot post new topics in this forum You cannot reply to topics in this forum You cannot delete your posts in this forum You cannot edit your posts in this forum You cannot create polls in this forum You cannot vote in polls in this forum
IP Logged
