Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

8.5 Union report not working in X Viewer

Status
Not open for further replies.
May 12, 2005
39
US
I have made a couple of reports in Crystal Reports 8.5 Professional that use either 2 or 3 unions depending on the report. Our software vendor just upgraded the crystal viewer for all of our users to Crystal X.

All of our regular reports work just fine, however when I go to run a report that uses a UNION ALL in the end user viewer program, I get an error saying that the Union Select lists don't match. After inspection of my sql I haven't noticed anything wrong with it (If you guys want I can post it here, but I'll warn you, its long). The weird part is the report opens fine in Crystal 8.5. There is no error message and everything looks as it should.

Is there something special in the way you do UNIONS in Crystal X?

 
Here is the SQL:

SELECT
INVENTORY_MSTR."PART", INVENTORY_MSTR."PRODUCT_LINE", INVENTORY_MSTR."QTY_ORDER", INVENTORY_MSTR."QTY_SAFETY", INVENTORY_MSTR."QTY_ONHAND",
INVENTORY_MST2."QTY_MAXIMUM", INVENTORY_MST2."NAME_VENDOR",
JOB_HEADER."QTY_ORDER",
'20' + {fn RIGHT(JOB_HEADER."DATE_CLOSED", 2)} + {fn LEFT(JOB_HEADER."DATE_CLOSED", 4)},
JOB_HEADER."JOB" + '-' + JOB_HEADER."SUFFIX",
'JOB',
(IF(JOB_HEADER."QTY_COMPLETED"-JOB_HEADER."QTY_ORDER" = 0, 'F', IF(JOB_HEADER."AMT_MATERIAL" = 0, 'P', 'W')))
FROM
"JOB_HEADER" JOB_HEADER INNER JOIN "INVENTORY_MSTR" INVENTORY_MSTR ON
JOB_HEADER."PART" = INVENTORY_MSTR."PART" INNER JOIN "INVENTORY_MST2" INVENTORY_MST2 ON
INVENTORY_MSTR."PART" = INVENTORY_MST2."PART"
WHERE
INVENTORY_MSTR."PRODUCT_LINE" >= 'A1' AND
INVENTORY_MSTR."PRODUCT_LINE" <= 'N1' AND
INVENTORY_MSTR."QTY_ORDER" <> 0 AND
INVENTORY_MST2."NAME_VENDOR" <> ' ' AND
INVENTORY_MST2."NAME_VENDOR" LIKE '%' AND
INVENTORY_MSTR."PART" LIKE '%'
UNION ALL
SELECT
INVENTORY_MSTR."PART", INVENTORY_MSTR."PRODUCT_LINE", INVENTORY_MSTR."QTY_ORDER", INVENTORY_MSTR."QTY_SAFETY", INVENTORY_MSTR."QTY_ONHAND",
INVENTORY_MST2."QTY_MAXIMUM", INVENTORY_MST2."NAME_VENDOR",
{fn IFNULL(OE_SHIP_SCHED."QTY_OPEN", ORDER_LINES."QTY_ORDERED")},
{fn IFNULL(OE_SHIP_SCHED."DATE_DUE", ORDER_LINES."DATE_ITEM_PROM")},
ORDER_LINES."ORDER_NO" + '-' + {fn LEFT(ORDER_LINES."RECORD_NO", 3)},
'ORDER',
{fn IFNULL(OE_SHIP_SCHED."FLAG_CLOSED", 'O')}
FROM
"ORDER_LINES" ORDER_LINES INNER JOIN "INVENTORY_MSTR" INVENTORY_MSTR ON
ORDER_LINES."PART" = INVENTORY_MSTR."PART" LEFT JOIN "OE_SHIP_SCHED" OE_SHIP_SCHED ON
ORDER_LINES."ORDER_NO" = OE_SHIP_SCHED."ORDER_NO" INNER JOIN "INVENTORY_MST2" INVENTORY_MST2 ON
INVENTORY_MSTR."PART" = INVENTORY_MST2."PART"
WHERE
INVENTORY_MSTR."PRODUCT_LINE" >= 'A1' AND
INVENTORY_MSTR."PRODUCT_LINE" <= 'N1' AND
INVENTORY_MSTR."QTY_ORDER" <> 0 AND
INVENTORY_MST2."NAME_VENDOR" <> ' ' AND
{fn Convert({fn LEFT({fn IFNULL(OE_SHIP_SCHED."DATE_DUE", ORDER_LINES."DATE_ITEM_PROM")}, 4)}, SQL_INTEGER)} = {fn YEAR({fn NOW()})}
UNION ALL
SELECT
INVENTORY_MSTR."PART", INVENTORY_MSTR."PRODUCT_LINE", INVENTORY_MSTR."QTY_ORDER", INVENTORY_MSTR."QTY_SAFETY", INVENTORY_MSTR."QTY_ONHAND",
INVENTORY_MST2."QTY_MAXIMUM", INVENTORY_MST2."NAME_VENDOR",
ORDER_HIST_LINE."QTY_SHIPPED",
ORDER_HIST_LINE."DATE_SHIPPED",
ORDER_HIST_LINE."ORDER_NO" + '-' + {fn RIGHT(ORDER_HIST_LINE."ORDER_LINE", 3)},
'ORDER',
'S'
FROM
"ORDER_HIST_LINE" ORDER_HIST_LINE INNER JOIN "INVENTORY_MSTR" INVENTORY_MSTR ON
{fn LEFT(ORDER_HIST_LINE."PART", 17)} = {fn LEFT(INVENTORY_MSTR."PART", 17)} INNER JOIN "INVENTORY_MST2" INVENTORY_MST2 ON
INVENTORY_MSTR."PART" = INVENTORY_MST2."PART"
WHERE
INVENTORY_MSTR."PRODUCT_LINE" >= 'A1' AND
INVENTORY_MSTR."PRODUCT_LINE" <= 'N1' AND
INVENTORY_MSTR."QTY_ORDER" <> 0 AND
INVENTORY_MST2."NAME_VENDOR" <> ' ' AND
{fn Convert({fn LEFT(ORDER_HIST_LINE."DATE_SHIPPED", 4)}, SQL_INTEGER)} >= {fn YEAR({fn NOW()})} - 1
ORDER BY
7 ASC,
1 ASC

Something to note:
I tried running this with a demo version of Ido Millet's DataLink Viewer that I'm currently trying out and got an error with that program too. That is why I think this is a Crystal 8.5/X issue instead of a problem with my software vendor.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top