oledb-group by

[iso]

first of all thanks every body for the past answers to my questions.
here is another question.(ASP -FoxPro 8)
I will change foxpro ODBBC.dll to OLEDB. the reason is ODBC dll is not
thread safe and causes IIS down. my point is "Is there any problem with
GROUP BY if I use OLEDB". it seems like it does not work properly. so
should I cchange my code?

thanks a lot!
iso

[Anders]

The GROUP BY clause was enhanced to the SQL standard in VFP8. Before that it
was possible to misuse the GROUP BY clause.
-Anders

Post by iso
first of all thanks every body for the past answers to my questions.
here is another question.(ASP -FoxPro 8)
I will change foxpro ODBBC.dll to OLEDB. the reason is ODBC dll is not
thread safe and causes IIS down. my point is "Is there any problem with
GROUP BY if I use OLEDB". it seems like it does not work properly. so
should I cchange my code?
thanks a lot!
iso

[Cindy Winegarden]

Hi Iso,

It really helps if you post the SQL you are using and an example of the data
in the table and the result you want to retrieve. Otherwise we have no clue
what "does not work properly" means and no way to discover what you've done
to cause the "does not work properly."

[Olaf Doschke]

In general the GROUP BY clause works like it should in
OLEDB. Since the latest ODBC driver only has the SQL
engine of VFP6, you could use it wrongly before, like
Anders pointed out.

eg.

select band, title, ;
count(*) as NumberOfTitles;
from songs;
group by band

is wrong but worked in ODBC/older VFP versions. ODBC
may have given you the first title it found as title. In general you
would either have to group by band AND title, but then would
mostly have a count of 1 or use MIN(title) to have the
alphabetical first title or leave out the title completely.

In general you must group by all simple fields, that you select
and don't use some aggregating function on them, like
MIN, MAX, AVG, SUM, COUNT. If you think about
it, it's logical and necessary.

Another scenario, that doesn't work at all with group by
or distinct is a memo field in the result set.

Bye, Olaf.

[iso]

thanks everybody

Actually we solved the problem using conn1.execute('set engine behavior 70')
than it behave like we use ODBC dll. the problem was when we use OLEDB we
have to mention the field name that come after GROUP BY as in "select
f1,... from ...group by f1"
When we use VFPODBC.dll we don't use like that.Actually above useage is
regular group by usage.

But it was not easy to change all the code.
then I set engine behavior to 70 just after opening connection. now it is
working.
thanks.
ismail

Post by Olaf Doschke
In general the GROUP BY clause works like it should in
OLEDB. Since the latest ODBC driver only has the SQL
engine of VFP6, you could use it wrongly before, like
Anders pointed out.
eg.
select band, title, ;
count(*) as NumberOfTitles;
from songs;
group by band
is wrong but worked in ODBC/older VFP versions. ODBC
may have given you the first title it found as title. In general you
would either have to group by band AND title, but then would
mostly have a count of 1 or use MIN(title) to have the
alphabetical first title or leave out the title completely.
In general you must group by all simple fields, that you select
and don't use some aggregating function on them, like
MIN, MAX, AVG, SUM, COUNT. If you think about
it, it's logical and necessary.
Another scenario, that doesn't work at all with group by
or distinct is a memo field in the result set.
Bye, Olaf.

[Anders]

The question whether your query is good or bad hangs on the ... details that
you're omitting. What exactly do the ... in "select
f1,... from ...group by f1" stand for?
As Olaf demonstrated your query may be returning misleading data. After
years of complaints from the users the Fox team fixed this in VFP8 and now
supports standard SQL rules; you can now by default only use the standard
SQL92 rules in queries with GROUP BY.

VFP9 though actually offers ways of working around this this way:
SELECT id, col1, col2, col3, ;
(SELECT SUM(col4) AS sumcol4 FROM Table2 ;
WHERE Table2.id=Table1.id) ;
FROM Table1
In that case there's no GROUP BY at all but you do get an aggregate by using
an inline correlated subquery. If Table1.col1 is a PK and Table2.id
references it, you're ok.

In earlier version of VFP you could do the same thing in two queries
SELECT id, SUM(col4) As sumcol4 FROM Table2 INTO CURSOR Q1
SELECT *, sumcol4 FROM Table1 JOIN Q1 ON Q1.id=Table1.id

Another way in VFP9 puts those two queries into one statement:
SELECT * FROM Table1 JOIN ;
(SELECT id, SUM(col4) FROM Table2 GROUP BY id) As Q1
ON Q1.id = Table1.id

-Anders

Post by iso
thanks everybody
Actually we solved the problem using conn1.execute('set engine behavior 70')
than it behave like we use ODBC dll. the problem was when we use OLEDB we
have to mention the field name that come after GROUP BY as in "select
f1,... from ...group by f1"
When we use VFPODBC.dll we don't use like that.Actually above useage is
regular group by usage.
But it was not easy to change all the code.
then I set engine behavior to 70 just after opening connection. now it is
working.
thanks.
ismail

Post by Olaf Doschke
In general the GROUP BY clause works like it should in
OLEDB. Since the latest ODBC driver only has the SQL
engine of VFP6, you could use it wrongly before, like
Anders pointed out.
eg.
select band, title, ;
count(*) as NumberOfTitles;
from songs;
group by band
is wrong but worked in ODBC/older VFP versions. ODBC
may have given you the first title it found as title. In general you
would either have to group by band AND title, but then would
mostly have a count of 1 or use MIN(title) to have the
alphabetical first title or leave out the title completely.
In general you must group by all simple fields, that you select
and don't use some aggregating function on them, like
MIN, MAX, AVG, SUM, COUNT. If you think about
it, it's logical and necessary.
Another scenario, that doesn't work at all with group by
or distinct is a memo field in the result set.
Bye, Olaf.