Union Query Error

[jschodde]

All,

I'm trying to make the query (below) work in my CR8.0 report (via Database/Show SQL Query). Unfortunately I get the following error message when I preview the report:

"ODBC Error: [Microsoft][ODBC Microsoft Access Driver] The number of columns in the two selected tables or queries of a union query do not match."

If I take out these two statements the query runs fine:
"opr" as type
"olr" as type

I can make this query work in SQL Designer, but the fields come back to the report as memo fields which I cannot use in my case.

Thanks,
Jeff


SELECT
JobTicket."LvYdTime",
JobTicket."ArriveTime",
JobTicket."LvJobTime",
JobTicket."ReturnTime",
Schedule."Sched_ID",
Schedule."PumpReq",
Schedule."PumpSent",
Schedule."Operator",
Schedule."Oiler",
Schedule."JobDate",
"opr" as type
FROM
JobTicket,Schedule
WHERE
JobTicket."JT_ID" = Schedule."Sched_ID"
UNION ALL
SELECT
JobTicket."LvYdTime",
JobTicket."ArriveTime",
JobTicket."LvJobTime",
JobTicket."ReturnTime",
Schedule."Sched_ID",
Schedule."PumpReq",
Schedule."PumpSent",
Schedule."Oiler" as operator,
Schedule."Oiler",
Schedule."JobDate",
"olr" as type
FROM
JobTicket,Schedule
WHERE
JobTicket.&quot;JT_ID&quot; = Schedule.&quot;Sched_ID&quot; AND Schedule.&quot;Oiler&quot;<>' '

 

[kenhamady]

You can't write a query directly into the SQL query window. You can modify this query, but not the Select statement.

You have three options. You can use this query in the SQL designer, and run the report against the QRY file.

Or you can use the instructions in the following article to try to force a union query in the Show SQL window. The article demonstrates how to shut off the first query to do a select distinct, but it shows that you can union, if you don't change the first select.

http://support.crystaldecisions.net/kbase/c2007732.asp

Or you can create a view/query/stored Procedure in the database and run the report against that. Ken Hamady

http://www.kenhamady.com/

Crystal Reports Training/Consulting and a
Quick Reference Guide to VB/Crystal (including ADO)

 

[jschodde]

Ken,
I was hoping I would hear from you. I see your postings all over the place.

I can get the query to work just fine in the SQL designer but the resulting data set is all memo fields :-(

Do you know how I can avoid memo fields or how to convert them for the report? This article explains why I get memo fields:

http://support.crystaldecisions.net/library/kbase/articles/c2006832.asp

-Jeff

 

[kenhamady]

Sorry, it doesn't sound like there is a workaround on that. Ken Hamady

http://www.kenhamady.com/

Crystal Reports Training/Consulting and a
Quick Reference Guide to VB/Crystal (including ADO)