On Jun 18, 2008, at 10:38 AM, Gil Hale wrote:
> I am surmising you mean I am "to lazy" (sic) in a good way. Well,
> perhaps
> in the estimation of others I am "too" lazy to write "Good SQL" as
> in VFP9.
> My VFP7 code runs just fine as it is and there is no need for me to
> go back
> and recompile in VFP9, and use "Good SQL" just for the sake of doing
> it. My
> time is better spent in ProFox than making needless retro-
> corrections <g>.
You're just returning unneeded fields then. If you have a non-
aggregated field that isn't in the GROUP BY, you get a column with a
useless, undefined value in VFP with 70 behavior. That's an artifact
of the way SQL is implemented under the hood in VFP. Example:
SELECT city, state, sum(sales) as TotalSales
FROM SalesData
GROUP BY state
This will give you the sales total by state, but what's in the 'city'
column? In VFP, it would probably be the city with the highest RECNO()
for that state, but there are exceptions; in other words, it's
undefined and meaningless.
NBD, right? Well, there are two downsides: one, someone may see the
results and think that the line that reads 'Penfield - NY - 1,000,000'
and think that a million dollars was sold in Penfield, NY, when the
million was the total for all of NY State. The other downside is
performance: let's imagine that the SalesData table had hundreds of
columns, and you wrote the same query, but this time was even lazier:
SELECT *, sum(sales) as TotalSales
FROM SalesData
GROUP BY state
Now you have hundreds of useless columns being carried through. The
idea behind SQL is to ask for what you need, not ask for everything
you can possibly get.
-- Ed Leafe
_______________________________________________
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.
