Having the 1 come up for Cartons in the second select doesn't tell you much, since it'll use an arbitrary value from the the original DBF. What happens if you run the second query with "ORDER BY" instead of "GROUP BY"? Does the offending record come up twice?
HTH. -Jerry Wolper [email protected] > One of our clients is having a strange problem when running this > Manifest report, based mainly upon Bill-of-Lading DBF datafile lines. I > can't seem to see what the problem is - and I was hoping another set of > eyes might spot the issue. This is a report that someone else created, > but, I need to help support it. The SQL-Select statement in question is > as follows: > > > > SELE bolline.BOLNO,bolline.STORE,bolline.account,invoice, ; > > MAX(PIKTKT) as piktkt, ; > > SUM(CARTONS) AS Cartons,sum(weight) as weight,; > > custpo, ; > > SUM(shpqty) AS SHPQTY, MAX(DEPT) as dept, ; > > style,customer.city,customer.state ; > > from bolline,customer ; > > where bolno in (sele bolno from &qwd.&USEFILE) and ; > > bolline.account=customer.account and ; > > bolline.store = customer.store ; > > group by bolline.bolno, bolline.store, custpo ; > > into cursor templine1 READW > > > > The problem in particular is that a count of 2 would appear for Cartons > for a particular BOLno & Store & Custpo combination. But, in the BolLine > DBF file - there is only 1 record for that combo - and it has a count of > 1 for the Cartons. > > > > So - I did a test, and inserted another SQL-Select into the program - > right above the last SQL-Select, and simply disabled the SUMming & the > MAX function. That Select looks as follows: > > > > SELE bolline.BOLNO,bolline.STORE,bolline.account,invoice, ; > > piktkt,Cartons,weight, custpo, SHPQTY, dept, ; > > style,customer.city,customer.state ; > > from bolline,customer ; > > where bolno in (sele bolno from &qwd.&USEFILE) and ; > > bolline.account=customer.account and ; > > bolline.store = customer.store ; > > group by bolline.bolno, bolline.store, custpo ; > > into cursor temp_Tmp READW > > Upon running the program - the results by this Select looked correct - > as there was only a count of 1 for the Cartons - instead of 2 as > reported by the 1st SQL-Select above. > > Also - just so you know - I can't actually step thru the code to check > what's happening - as this particular problem is happening on the > clients system. > > Thanks in Advance! > > Kurt > > > > Developer > > Mr. Software of East Coast, Inc. > > Division of Waitex Information System, Inc. > > > > > > --- StripMime Report -- processed MIME parts --- > multipart/alternative > text/plain (text body -- kept) > text/html > --- > [excessive quoting removed by server] _______________________________________________ Post Messages to: [email protected] Subscription Maintenance: http://leafe.com/mailman/listinfo/profox OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech Searchable Archive: http://leafe.com/archives/search/profox This message: http://leafe.com/archives/byMID/profox/[email protected] ** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
