Is VFP SQL ansi compliant ?

[glengarth]

I'm doing data analysis of some customer data in a bunch of VFP 5.0 free tables (no actual database container); they're used in an accounting package I won't mention (but's it's initials are SBT). I'm doing an inner join which should produce some very straight-forward results. Here's the query...

select temp.custno, SUM(temp.invamt), arycsh.custno, sum(arycsh.paidamt), sum(temp.invamt - arycsh.paidamt) as diff ;
from temp full outer join arycsh on temp.custno = arycsh.custno ;
where not temp.current = 'X';
and temp.custno = 'A' ;
group by temp.custno ;
into cursor reportA

...but when I look at my results for the aggregates, the numbers are astronomical. I remove the sum aggregates and grouping, run the query again, and find that each record in the temp table is being joined by every field in the arycsh table that has the matching custno field; a sort of one to many join. What the....?

 

[Mike Lewis]

The short answer to your question is No -- VFP 5.0 is not ANSI-compliant. This shows up in your SELECT, as you have listed arycsh.custno in your epxression list, but that field is not included in your grouping. The contents of that field will therefore be indeterminate. If you omit the field from the expression list, you might get a different result.

Also, are you sure if you want a full outer join here? If my understanding is correct, an inner join would be appropriate.

The latest version of VFP (8.0) is more ANSI-compliant, and would flag your code with an error.

Mike


Mike Lewis
Edinburgh, Scotland

http://www.ml-consult.demon.co.uk

 

[glengarth]

...as to the grouping, I have found what appears to be the same results when I include the field arycsh.custno in the group clause. And I need that field to join the 2 tables.

Yes, the inner join would be better; I've tried it since the original post. No better, or so it seems.

 

[Mike Lewis]

OK, my point about outer vs inner join might not have been relevant. Let's forget that.

You say that, when you remove the aggregates and grouping, "each record in the temp table is being joined by every field in the arycsh table that has the matching custno field".

That's exactly what I would expect your code to do. It's the result of the join. Isn't that what you want?

Mike


Mike Lewis
Edinburgh, Scotland

http://www.ml-consult.demon.co.uk

 

[glengarth]

Well....the arycsh table should have in some cases more than one matching record per record in the temp table. So record 1, 2 and 3 in arycsh might match record 1 in temp. But when I remove the aggregate functions to see the results of the query that for records 2 and 3 in the arycsh table there is a duplicate of record one in the temp table. Am I making sense?

 

[Mike Lewis]

Hmm, not sure I can see the reason for that. If you have a query with grouping and aggregation, and you remove the aggregation, you should still see the same records in the result set. If that's not the case here, I can't see what's going on.

Anyone else got any ideas?

Mike


Mike Lewis
Edinburgh, Scotland

http://www.ml-consult.demon.co.uk