Re: oledb-group by
- From: "Anders" <anders@anders>
- Date: Thu, 17 Nov 2005 12:25:04 +0100
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
"iso" <ismailozyigit@xxxxxxxxxxx> skrev i meddelandet
news:O80xQOt6FHA.1420@xxxxxxxxxxxxxxxxxxxxxxx
> 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
>
>
> "Olaf Doschke" <T2xhZi5Eb3NjaGtlQFNldG1pY3MuZGU@xxxxxxxxxx> wrote in
> message news:ePXEsBq6FHA.2600@xxxxxxxxxxxxxxxxxxxxxxx
>> 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.
>>
>>
>
>
.
- References:
- oledb-group by
- From: iso
- Re: oledb-group by
- From: iso
- oledb-group by
- Prev by Date: Re: Index Sequence
- Next by Date: Re: Index Sequence
- Previous by thread: Re: oledb-group by
- Next by thread: Moving records
- Index(es):
Relevant Pages
|
