UNION causes records to go away? 2

[kenhamady]

I am trying to send a "UNION" SQL statement from Crystal Reports through ODBC to some VFP tables. The statements seem to work fine independently but when I try to UNION them using a Crystal command I lose records. Below is the statement:

SELECT
`sllines`.`item` as item,
`sllines`.`needby` as needby,
`sllines`.`orderqty` as oqty,
`sllines`.`shipqty` as sqty,
`imlocn`.`locid` as locid,
`slheader`.`ordrstat` as orderstat,
`sllines`.`linestat` as linestat

FROM (`slheader` `slheader` INNER JOIN `sllines` `sllines` ON (`slheader`.`docid`=`sllines`.`docid`) AND (`slheader`.`doctype`=`sllines`.`doctype`)) LEFT OUTER JOIN `imlocn``imlocn` ON `sllines`.`item`=`imlocn`.`item`

WHERE (`slheader`.`ordrstat`='A' OR `slheader`.`ordrstat`='P')

UNION ALL

SELECT
`wo`.`partno` as item,
`wo`.`need_date` as needby,
`wo`.`exp_4` as oqty,
`wo`.`exp_5` as sqty,
'wo' as locid,
'wo' as orderstat,
'wo' as linestat

FROM `wo` `wo`


The first SELECT, when run alone returns 181 records. The second SELECT, when run alone returns 3 records. When combined they return 150 recoords from the first select and none from the second. This works as expected in MS Access but not in VFP. Not sure what I am missing here.

Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guides for Formulas, Parameters, Subreports, VB, .NET, Tips and Tricks

http://www.kenhamady.com/

 

[Mike Lewis]

Hi Ken,

I'm not sure if this is the answer, but are you aware that in VFP, a UNION has an implicit DISTINCT clause? Is it possible that your query has eliminated duplicate rows?

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk

 

[kenhamady]

And another odd thing. If I try to reverse the two SELECTs it tells me that they are not UNION compatible, but I don't get that message when they are in the order above. That doesn't make sense to me.

Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guides for Formulas, Parameters, Subreports, VB, .NET, Tips and Tricks

http://www.kenhamady.com/

 

[kenhamady]

Mike,

Thanks. I did not know that. That would explain why some records in the first SELECT go away, but not why there are no records from the second SELECT at all. They have hard coded literals so they can't duplicate the first SELECT. Is there a way to tell it not to apply the distinct?

Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guides for Formulas, Parameters, Subreports, VB, .NET, Tips and Tricks

http://www.kenhamady.com/

 

[Mike Lewis]

Ken,

To avoid the DISTINCT, you can add the keyword ALL immediately after UNION.

However, that won't explain why you are getting an error message when you reverse the order of the SELECTs.

I see that one of your SELECTs involves an outer join. I wonder if the fact it might be returning NULLs has any effect?

I know you are a CR person. Do you have a copy of VFP installed? If so, try running the query in the VFP command window. That would eliminate any problems caused by the ODBC driver or OLE DB provider.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk

 

[Stella740pl]

Mike,

There is UNION ALL in the original post.

 

[Mike Lewis]

Stella,

Your eyes are sharper than mine. Thanks.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk

 

[kenhamady]

No I don't have VFP so I won't be able to do that test but I suspect it is a flaky ODBC driver, even though I just downloaded the latest one.

I changed the JOIN to INNER but now it says the SELECTS aren't UNION compatible. This is weird.

Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guides for Formulas, Parameters, Subreports, VB, .NET, Tips and Tricks

http://www.kenhamady.com/

 

[Mike Lewis]

Ken,

Do you know which version of VFP was used to create the database?

The ODBC driver hasn't been updated since VFP 6, which was several years ago, and it doesn't support some of the newer features of the product.

It might be worth your while trying to use OLE DB rather than ODBC. The VFP OLE DB driver is much more up to date. You should be able to download it from Microsoft.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk

 

[kenhamady]

Mike,

Thanks. I just configured the OLEDB provider and it is more reliable and has more helpful error messages. It noticed, for instance that one my numeric fields is stored as currency which makes the UNION fail. (Crystal sees a number but VFP sees currency). The OLEDB error pointed out the field that was causing the problem which the ODBC driver did not do.

So now all I have to do is figure out the syntax for converting a currency into a number within the SQL statement.

Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guides for Formulas, Parameters, Subreports, VB, .NET, Tips and Tricks

http://www.kenhamady.com/

 

[Mike Lewis]

Ken,

Glad to hear you're making progress. I've also had difficulties accessing VFP tables from CR via ODBC. OLE DB does seem to work better.

To convert a currency to a number, you need the MTON() function.

Thanks for the star.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk

 

[kenhamady]

Thanks, I think that puts it to bed.

Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guides for Formulas, Parameters, Subreports, VB, .NET, Tips and Tricks

http://www.kenhamady.com/