Wiki Home

VFP Error 1807


Namespace: WIN_COM_API
Answer: Engine Behavior - thanks to the person who put this in here, unattributed. I didn't see it at first, but I see it now. Thanks, whoever you are. Michael Wagner

I keep getting error 1807 SQL: GROUP BY clause is missing or invalid. This is Foxpro 9.

I built the SQL using the view designer. The SQL statement is cut and pasted into here:

SELECT Ordhead.cust, Ordship.shipdate, SUM(Ordship.quant) AS quant,;
Products.custpartno, Products.custdescr, Products.custrevisn,;
Ordship.status, Ordhead.invnum, Products.majmachine, Products.heattreat,;
Products.plate, Products.vibrafin, Products.tumble;
FROM ;
ordhead ;
INNER JOIN ordship ;
ON Ordhead.hamordnum = Ordship.hamordnum ;
LEFT OUTER JOIN products ;
ON Ordhead.invnum = Products.invnumfin;
WHERE Ordhead.cancel <> 0;
AND Ordship.shipped <> 0;
AND Ordhead.allshipped <> 0;
AND Ordship.approxdate <> 0;
AND Ordship.hold <> 0;
AND Ordship.status >= 3;
GROUP BY Ordship.shipdate, Products.custpartno ;
ORDER BY Ordship.shipdate, Ordhead.cust, Products.custpartno

As you can see, it's not missing, and it sure looks valid to me. Perhaps I'm tired but I just don't see what's wrong.

What am I missing?
Michael Wagner

Sigh. I found it, an hour later.
SET ENGINEBEHAVIOR 70
I'm not sure what the point of this is, but I spent a good few hours tracking this one down. Hopefully it'll prevent someone else from falling into the rabbit hole I fell into.
Michael Wagner

I'm very surprised how often this topic comes up (e. g. in newsgroups). Btw. there is a chapter "What's New in Visual FoxPro" in the help.

Sigh. SET ENGINEBEHAVIOR does NOT default to 70 for a good reason. Suppose you have two Ordship records with Status 3 and 4. That may not normally be possible, but humor me. Your results would only show one of these Status values. Which one is correct? The following query may work. -- Mike Yearwood

Hi Mike. I take your point and yes, it is remotely possible that there be 2 shipments on the same day, one with status 3 (we have enough material for this shipment) and one with status 4 (we don't have enough material for this shipment). And I suppose back in Fox 5 when I wrote this, I didn't consider that possibility. And it's good that it makes me think about it. But I have 2 problems with this.
(a) it means old code doesn't work

Actually, your old code may not have worked because you shouldn't have been allowed to use such a GROUP BY clause, so the default is to prevent this type of problem, which is a good thing. By giving this error, VFP is alerting you to the fact that you may have bad results rather than leaving you in the dark. I think that was a good decision from the VFP team. -- Doug Hennig

(b) it looks and reports like a syntax error, gets you into an IDE loop you can't get out of, and causes all sorts of confusion.

I know I didn't pick up on it when I read the what's new section, and that's certainly my own fault. But I think breaking backwards compatability could be handled a little better. Michael Wagner

P.S. I may have another problem with this too, having to do with the things that are trivially and provably unique, but I need to do some more experiments before I can speak with any authority.

SELECT Ordhead.cust, Ordship.shipdate, SUM(Ordship.quant) AS quant,;
  Products.custpartno, Products.custdescr, Products.custrevisn,;
  Ordship.status, Ordhead.invnum, Products.majmachine, Products.heattreat,;
  Products.plate, Products.vibrafin, Products.tumble;
 FROM ;
     ordhead ;
    INNER JOIN ordship ;
   ON  Ordhead.hamordnum = Ordship.hamordnum ;
    LEFT OUTER JOIN products ;
   ON  Ordhead.invnum = Products.invnumfin;
 WHERE  Ordhead.cancel <> 0;
   AND  Ordship.shipped <> 0;
   AND  Ordhead.allshipped <> 0;
   AND  Ordship.approxdate <> 0;
   AND  Ordship.hold <> 0;
   AND  Ordship.status >= 3;
 GROUP BY Ordship.shipdate,  Products.custpartno, Ordhead.cust, ;
  Products.custdescr, Products.custrevisn,;
  Ordship.status, Ordhead.invnum, Products.majmachine, Products.heattreat,;
  Products.plate, Products.vibrafin, Products.tumble ;
 ORDER BY Ordship.shipdate, Ordhead.cust, Products.custpartno


There are several ways to work around this limitation, depending on your situation. For the cases where things are really unique, the easiest is to either add all the non-aggregate fields to the GROUP BY clause or wrap them with either MAX() or MIN(). -- Tamar Granor

Not to mention this CAN'T work this way if you move to SQL Server, Oracle, etc. It's not ANSI SQL compliant - therefore, the old enginebevior is flawed and you would be wise to go ahead and re-factor your query. Tamar's suggestions are where you should start. I'm guessing that adding all the non-aggregates (everything but the SUM() column) to the group by in this case would work. -- Randy Jean

I understand the bit about not being ANSI SQL compliant (although for my app this hardly matters, but let's leave that aside).

Duplicating all the fields again in the GROUP BY seems a bit extreme and awkward, at least to my jaundiced eye. For example, all the products fields are trivially unique, because the index on products (invnumfin) is unique.

In order to keep this query meaningful and somewhat self-documenting, I guess what I really need to do is break it into 2 queries:

SELECT Ordship.shipdate, Ordhead.invnum, SUM(Ordship.quant) AS quant;
FROM ordhead INNER JOIN ordship ;
ON Ordhead.hamordnum = Ordship.hamordnum ;
WHERE Ordhead.cancel <> 0;
AND Ordship.shipped <> 0;
AND Ordhead.allshipped <> 0;
AND Ordship.approxdate <> 0;
AND Ordship.hold <> 0;
AND Ordship.status >= 3;
GROUP BY Ordship.shipdate, Ordhead.invnum

this sums all shipments for the same inventory number due on the same day

and then join it with the products table, now without any GROUPs BY, so that I can identify the product characteristics to the shop floor people (this is for a display of what work needs to be done on the manufacturing floor).

Does that make sense?

Actually, in VFP 9, you can do it in one command, using a derived table. The query that does the summing does in the FROM clause and its result is joined as needed to retrieve the other data.--TamarGranor

I built that view back in fox 5 or so and never questioned it again until it failed now in 9.

BTW, I'm glad I mentioned this ... I've learned a lot in this discussion ... thanks, all.

Oh, in terms of Oracle back ends ... this is for a factory status reporting system in a 10 person manufacturing facility. I know it's good to think big, but we'd have to grow a lot before that was a significant issue .... Michael Wagner
I think you're exactly correct to break it into two queries. That's (oddly enough) one of the reasons I stopped using views (especially in data environments). If I had a bunch of view in the data environment and then wanted to break one into two pieces, it would be awkward to resequence the views. -- Mike Yearwood
The more I look into this view thing, the more I realize how little I really understand about what's going on, and how much I took for granted. Which always worries me.

For example ... well, let me postulate a little database. It contains my ordhead and ordship tables used in the example at the start of this article. It also contains a view based on ordhead and ordship (perhaps the one in the example). For simplicity, let's ignore writing data back (since in my code I never do). I use the view to populate a listbox, to scroll through my orders.

Suppose I open this form. The SQL is run. Where do the results go?

Do they live in the memory of the workstation where the form is being presented? Some articles here imply this, some imply the opposite. Do they live in a cursor in the workstation?

Do they live in the database itself somewhere? If so, where?

Now suppose someone else on another workstation opens the same form. They'll run the same query. Is any of the work of performing the query shared between the two forms?

Same question except the second form is on the same workstation, in a form with private datasession.

Going back to the only one form case, suppose I close the form but the foxpro application is still going, and then realize I forgot something and go back to the form. Is there any data cached by foxpro from the first execution of the form that speeds up the second time (I'm not counting operating system caching)? If so, where does it reside physically?

If this is all documented somewhere, please point me in the right direction. But I can't seem to recall reading it anywhere.

Thanks, all, for your assembled wisdom. Michael Wagner
When a VFP SQL is run with a database and tables on a file server, the workstation itself extracts the data from the database and tables on the server. The results are kept in a cursor (which may be in RAM) unless you use the readwrite keyword to force the results into a physical file (which you probably should always do).

When you use SQL Server, the SQL Server machine extracts the data. The workstation receives this data and puts it in a cursor in the workstation's RAM or hard drive.

FoxPro and the OS cache some data and indexes to speed up subsequent queries. SQL server does its own caching. -- Mike Yearwood

Mike: I know if it's an explicit Select SQL query, I can specify a destination, name the cursor, make it read/write, etc.
My question is, when it's a view (which I agree is SQL under the covers), where does it go? I can name the output but I have no option to make it a cursor or not (or I can't find it). In that case, where does it go? Is it still a cursor on my workstation?
Yep! :)
( Topic last updated: 2005.09.20 09:04:12 AM )