>Hi Mark,
>
>>In Oracle, you have to GROUP BY
every column that is not selected as an aggregate. In your example above you have to GROUP BY all 5 of the first columns.
>>
>>Did you see my reply to this in the VFP forum?
>
>Yes, but I've received conflicting replies from the VFP forum and this forum.
>
>When I execute:
>
>Select course,subject,held,course_date,idtraining from training group by course,subject,held,course_date,idtraining;
>
>all of the records are returned from Oracle, VFP or SQL Server.
>
>I'm confused, because in my opinion this makes GROUP BY redundant in Oracle, as you are saying that all of the fields MUST be included in the GROUP BY clause.
>
>To illustrate my problem, my sample data is as follows:
>
>
>Course Subject Held Course_Date IdTraining
>Matt1 Test1 01/02/2000 01/02/2000 2
>Matt2 Test2 15/02/2000 17/02/2000 3
>Matt1 Test1 01/02/2000 01/02/2000 4
>
>
>There are other fields in the table, but these are the ones I will be displaying to the user.
>
>When I execute my query, I want to return the following:
>
>
>Course Subject Held Course_Date IdTraining
>Matt1 Test1 01/02/2000 01/02/2000 4
>Matt2 Test2 15/02/2000 17/02/2000 3
>
>
>
>I can do this easily in SQL Server or VFP, simply by grouping on Course.
>
>Are we really saying that it is impossible to get this result from an Oracle back-end ?
The way you want it, you can not do in Oracle because the IdTraining values are unique. How do you know what IDTraining value is correct, 2 or 4? Liek I said before, Oracle is very strict on aggregating fields and group by clauses. Looking at what you are trying to do logically, Oracle is correct.
Now a possible work-around. Create a VFP remote view of the table that selects all the records. Run a local select against this view. Since the VFP syntax is much looser, this should work.
Mark McCasland
Midlothian, TX USA