Create Unions in Crystal 1

[kwalters110]

I am trying to create a report and I need to UNION my Open Orders with my Order History but sadly I have discovered in another forum that Pervasive 8.6 (my sql server) doesn't allow me to create views that uion 2 different tables (You can only union from the same table). Is there anyway using Crystal 8.5 to create a union of those tables that would be local to the report? I would really like to be able to use this UNION in my report and avoid using an Access Database as an in between solution if at all possible.

Any help would be greatly appreciated.

 

[lbass]

Yes, you can do this by first adding one table to the report, adding the fields that you want to display to the report canvas, and adding any record selection criteria. Then go to database->show SQL query and copy the SQL query, add "Union" or "Union All" and paste SQL after this. Then change the table name in the second part of the query to the history table name. Finally, you can add any "Order By" criteria at the end, using a number to indicate the sequential position of the field in the query that you want to sort by.

In 8.0 and 8.5, you cannot change anything in the Select and From parts of the first query, so if you need to have a field that distinguishes the current table from the history table, when you first set up the report, add a string field to the report canvas that you wouldn't otherwise use, and then in the second half of the query, add something like 'History' in the corresponding position. Then you can use a formula like the following to distinguish records:

if {current.field} <> 'History' then "Current" else "History"

After changing the SQL, avoid entering the record selection formula area again, as it will change the query and disrupt the report.

-LB

 

[kwalters110]

Oh Wow, thats exactly what I wanted. Now I want to see if we can take it a step further.

I selected several tables and linked them, and put the fields I wanted onto the report canvas. Then I copied that whole SQL statement and made a giant union. What I want to accomplish is a collection of Open orders and Order history that connects to the part number of our inventory master. so my sql looks like this now:

SELECT
V_ORDER_LINES."QTY_ORDERED", V_ORDER_LINES."PRICE", V_ORDER_LINES."COST", V_ORDER_LINES."MARGIN",
V_INVENTORY_MSTR."PART", V_INVENTORY_MSTR."PRODUCT_LINE", V_INVENTORY_MSTR."QTY_ORDER",
V_INVENTORY_MST2."QTY_MAXIMUM", V_INVENTORY_MST2."NAME_VENDOR"
FROM
"GlobalCMC"."V_ORDER_LINES" V_ORDER_LINES INNER JOIN "GlobalCMC"."V_INVENTORY_MSTR" V_INVENTORY_MSTR ON
V_ORDER_LINES."PART" = V_INVENTORY_MSTR."PART" INNER JOIN "GlobalCMC"."V_INVENTORY_MST2" V_INVENTORY_MST2 ON
V_INVENTORY_MSTR."PART" = V_INVENTORY_MST2."PART"
WHERE
V_INVENTORY_MSTR."PRODUCT_LINE" >= 'A1' AND
V_INVENTORY_MSTR."PRODUCT_LINE" <= 'N1' AND
V_INVENTORY_MST2."NAME_VENDOR" <> ''
UNION
SELECT
V_ORDER_HIST_LINE."QTY_ORDERED", V_ORDER_HIST_LINE."PRICE", V_ORDER_HIST_LINE."COST",
V_ORDER_HIST_LINE."MARGIN", V_INVENTORY_MSTR."PART", V_INVENTORY_MSTR."PRODUCT_LINE",
V_INVENTORY_MSTR."QTY_ORDER", V_INVENTORY_MST2."QTY_MAXIMUM", V_INVENTORY_MST2."NAME_VENDOR"
FROM
"GlobalCMC"."V_ORDER_HIST_LINE" V_ORDER_HIST_LINE
INNER JOIN "GlobalCMC"."V_INVENTORY_MSTR" V_INVENTORY_MSTR
ON V_ORDER_HIST_LINE."PART" = V_INVENTORY_MSTR."PART"
INNER JOIN "GlobalCMC"."V_INVENTORY_MST2" V_INVENTORY_MST2
ON V_INVENTORY_MSTR."PART" = V_INVENTORY_MST2."PART"
WHERE
V_INVENTORY_MSTR."PRODUCT_LINE" >= 'A1' AND
V_INVENTORY_MSTR."PRODUCT_LINE" <= 'N1' AND
V_INVENTORY_MST2."NAME_VENDOR" <> ''


The report is being grouped by V_INVENTORY_MSTR."NAME_VENDOR" and then by V_INVENTORY_MSTR."PART" so then the individual order lines make up the detail. But when I go to run the report I'm seeing duplicate groups. I have perform grouping on server enabled, and I tried running with and without selecting distinct records and I got the same results. Any thoughts as to why?

 

[kwalters110]

whoops, nevermind.

I added the ORDER BY and that seems to have taken care of it. Thanks for your help