Missing records from sql with union clause 2

[spiego]

I am using the command object that has a sql statement with a union clause against a FoxPro database. When I run the report it doesn not return all of the valid records. However, when I run the exact same sql in FoxPro, I get the desired results (the missing records appear). Does anyone have any comments on why this is or suggestions on how to resolve this? I am using CR9.2.

Here is the statement:

 SELECT `icytrn30`.`item`,`icitem30`.`itmdesc`, `icitem30`.`stkumid`, `icunms30`.`cnvf`, `icytrn30`.`tqty`, `icytrn30`.`applid`, `icytrn30`.`tdate`, `icitem30`.`itmclss`, `icytrn30`.`tcost`, `icytrn30`.`trantyp`
 FROM   (`icunms30` `icunms30` INNER JOIN `icitem30` `icitem30` ON `icunms30`.`umsconv`=`icitem30`.`stkumid`) INNER JOIN `icytrn30` `icytrn30` ON `icitem30`.`item`=`icytrn30`.`item`
 WHERE  `icytrn30`.`applid`='PP' AND (`icytrn30`.`tdate`>={d '2004-09-28'} AND `icytrn30`.`tdate`<={d '2004-10-23'}) AND `icytrn30`.`item`='405100' 
 AND `icytrn30`.`trantyp`=' R'
Union 
 SELECT `ictran30`.`item`,`icitem30`.`itmdesc`, `icitem30`.`stkumid`, `icunms30`.`cnvf`,  `ictran30`.`tqty`, `ictran30`.`applid`, `ictran30`.`tdate`, `icitem30`.`itmclss`, `ictran30`.`tcost`, `ictran30`.`trantyp`
 FROM   (`icunms30` `icunms30` INNER JOIN `icitem30` `icitem30` ON `icunms30`.`umsconv`=`icitem30`.`stkumid`) INNER JOIN `ictran30` `ictran30` ON `icitem30`.`item`=`ictran30`.`item`
 WHERE  `ictran30`.`applid`='PP' AND (`ictran30`.`tdate`>={d '2004-09-28'} AND `ictran30`.`tdate`<={d '2004-10-23'}) AND `ictran30`.`item`='405100' 
 AND `ictran30`.`trantyp`=' R'

 

[synapsevampire]

Try using UNION ALL instead of just UNION.

-k

 

[spiego]

I've tried that already without any luck.

 

[fredp1]

Have a look at File, Report Options
Make sure that "select distict records" is not checked.

If that doesn't work, do you know which records are missing and any thoughts on why?

Cheers
Fred

 

[synapsevampire]

Interesting, sounds like a driver issue.

Are you connecting via xbase fiels, or ODBC? If so, which driver?

Try running the Query from Access, do you get the wrong or right results.

I would suggets trying a different type of connectivity, and if that fails, demonstrate what connecitvity you're using.

-k

 

[spiego]

fredp1,

That option is not checked. As for which records are missing, I found out something very interesting. There were 7 items that did not balance and when I did some digging into each of them, I discovered that I was getting the same result on 3 of them in FoxPro and Crystal. So, I then changed my union and put back the union all and now those 3 balance. However, that still leaves 4 in question. Well, I happened to notice that those 4 all had transactions on 9/27 that did not show up in the report, but do display in FoxPro. Out of all of the items on the report, those 4 are the only items to have transactions on that date.

 

[spiego]

synapsevampire,

Please see my reply to fredp1.

 

[synapsevampire]

You didn't mention to Fred how you're accessing the data either, too bad, this is critical. To most, anyway.

You may have rows that are marked as deleted in the fox database, and/or the ODBC driver or whatever connectivity isn't set to return deleted rows, or the driver may be the wrong one, none of which interests you.

Note that the MS VFP driver has the deleted option listed under options.

-k

 

[fredp1]

There may be crossed posting here...
Check to make sure that you do not have and suppression logic on the detail section.

Check the driver that synapsevampire mentioned.
Foxpro may be accessing the data natively, while Crystal may be ODBC. (you'll ned to specify what your using)
Changing from DAO/RDO/ADO can make a difference.

You are also doing some inner joins to 3 tables.
If the Item number is missing from one of the tables, there will be no result. Double check the data.
The deleted option could also be a problem.

Have you tried synapsevampire suggestion to put it into Access or maybe even SQL2k. If it works in those db, its sounds like a data/engine difference.

To also eliminate Crystal, run the query in another query tool like WINSQL Lite.


Cheers
Fred

 

[spiego]

Boy, I really dropped the ball on this one. The reason why it wasn't picking up 9/27 is because, if you look at my query, it starts at 9/28. However, the date range should not have been part of the command object to begin with because I am using a date parameter in the report. Once I took the date range out of the SQL, it worked fine.

Thanks to everyone for your help on this.

BTW - Fred, I downloaded WinSql light and it is a handy tool. Thanks for the tip.

 

[fredp1]

I had a look at your date range too but I thought I saw a date of 9/29... oh well at least you got a solution.



Cheers
Fred

 

[davidwendelken]

You shouldn't just "casually" switch from a union all to a union or vice-versa and hope things work out.

Both commands have a very precise meaning, and when you write your query, you should have a clear business understanding of which is appropriate.

Here is what makes them different:

Union removes duplicate records. This requires a sort.

Union All does not remove duplicate records.

So, let's say you have a current and historical sales table.

You want to know what each customer ordered on what day.
So:

Select cust_id, part_id, order_date, order_qty
from current_sales
union
select cust_id, part_id, order_date, order_qty
from historical_sales
order by 1,2,3

Now, let's say I ordered a Nordic Rock Cd on 04/01/04 to be shipped to me. And, right after that on the same day, I ordered another one to be shipped to my son at another address, which, like most order entry systems, will require two orders.

With a union all, you would see both CDs.

With a union, you will not see that I ordered 2 CDs, just 1.
If your query included shipping address, you would see both, because then the records would no longer be duplicates.



David Wendelken

http://www.casetech.net