SQL bombs when adding GROUP BY

[msc0tt]

The following command:
SELECT gltype FROM glacnt30;
works great.

The following command:
SELECT gltype FROM glacnt30 GROUP BY gltype;
bombs with the following error
Command contains unrecognized phrase/keyword.

I'm using VFP6 SP5, and entering the SELECT commands directly in the Command window. gltype is a character field with a width of 24 chars.
-thanks for any assistance.

 

[jimstarr]

Are you actually entering the semicolon in the command window?

Jim

 

[MarciaAkins]

>> SELECT gltype FROM glacnt30 GROUP BY gltype; <<

If you are using a GROUP BY clause, you need an aggregate function in the SQL SELECT like:

SELECT glType, SUM( gl_amt ) AS gl_amt FROM glacnt30 GROUP BY gltype

You statement has nothing to group by ;-)


Marcia G. Akins

http://www.TightlineComputers.com

 

[jimstarr]

The statement:

SELECT gltype FROM glacnt30 GROUP BY gltype

works fine for me in VFP5. I don't think Marcia's comment applies to VFP6.

The only way I can reproduce the error

"Command contains unrecognized phrase/keyword."

is to include the semicolon at the end of the statement and follow it with some non-blank character(s) before hitting ENTER.

Jim

 

[msc0tt]

Thanks Jim/Marcia,
Yes, the ; is really there. My only previous SQL (which was very basic) was on Oracle - we needed the ;.

Here is what I'm *really* trying to do:

I have a single table which contains all past sales. It has many fields, but I only care about the following:
custno character
invdte date
extprice numeric

I am trying to build a SELECT statement that will give me a report of monthly total sales by customer. I wish is to look something like:
ACME 3 3533
ACME 7 565
ACME 12 663
BOBCAT 1 3463
BOBCAT 2 551
etc...
Note that I wish the month to be represented by the number. i.e. Customer ACME only had sales in Mar, Jul, and Dec.

Here is my attempt (that doesn't work) :-{(
SELECT custno, month(invdte) as mon, SUM(extprice) as tot
FROM glacnt30
GROUP BY custno, mon
ORDER BY custno

Perhaps the errors in my way are obvious to more experienced SQLers.

Again, thanks.
-Mike

 

[baltman]

That might indeed be the line pointed to on the error, but the real problem is occuring in the subsequent code (connected by the ';') which you did not post. Why don't you share the whole SQL statement if you haven't already solved your problem?

Brian

 

[jimstarr]

Hi Mike,

Your code looks good to me. But multiple-line statements DO need the semicolon.

Jim

 

[baltman]

My guess is you're missing the space to seperate each line before the semicolon. Try:

SELECT custno, month(invdte) as mon, SUM(extprice) as tot ;
FROM glacnt30 ;
GROUP BY custno, mon ;
ORDER BY custno

 

[Mike Lewis]

Marcia,

If you are using a GROUP BY clause, you need an aggregate function in the SQL SELECT .... You statement has nothing to group by

Are you sure about that? I would've thought a grouping statement without an aggregrate function would work OK, even in 8.0. It would be equivalent to SELECT ... DISTINCT.

Mike


Mike Lewis
Edinburgh, Scotland

My Visual Foxpro web site:

http://www.ml-consult.demon.co.uk

My Crystal Reports web site:

http://www.ml-crystal.com

 

[MarciaAkins]

Hi Mike.

>> Are you sure about that? I would've thought a grouping statement without an aggregrate function would work OK, even in 8.0. It would be equivalent to SELECT ... DISTINCT. <<

No, I am not sure about this ;-)

I am just so used to using GROUP BY only when I have something to aggregate that this was the first thing that popped into my head.

FWIW, when I want to SELECT DISTINCT that's what I do




Marcia G. Akins

http://www.TightlineComputers.com

 

[Mike Lewis]

Marcia,

when I want to SELECT DISTINCT that's what I do

Seems reasonable.

I agree that you wouldn't normally do a grouping without an aggregate function. I was just saying that that wouldn't explain msc0tt's syntax error.

Mike


Mike Lewis
Edinburgh, Scotland

My Visual Foxpro web site:

http://www.ml-consult.demon.co.uk

My Crystal Reports web site:

http://www.ml-crystal.com

 

[Stella740pl]

Marcia, Mike,

I am not sure the tests I made are representative enough, but I noticed and checked by series of tests, that SELECT DISTINCT works slower than SELECT...GROUP BY used for the same purpose. So, personally, when I want SELECT DISTINCT from a large table, I do SELECT...GROUP BY.

Stella

 

[Mike Lewis]

Stella,

That doesn't surprise me. But thanks for confirming it.

Let me stress .... I am not advocating the use of grouping as an alternative to SELECT DISTINCT. I'm merely saying that is is syntactically correct.

By the way (this is not addressed to you, Stella), I wish msc0tt would come back and tell us if any of the many suggestions he received has solved the problem. It's irratating when we suggest solutions and never know if they worked on not.

Mike


Mike Lewis
Edinburgh, Scotland

My Visual Foxpro web site:

http://www.ml-consult.demon.co.uk

My Crystal Reports web site:

http://www.ml-crystal.com

 

[msc0tt]

Haha...
Yes, I'm back. It's only 8:58am on the west coast of Canada, and I've been here just long enough to check my emails (including the handful from TekTips.) Due to a busy family life, I'm not able to check my work emails on the weekend -sorry.
Yes, I resolved my problem at the end of business Friday- it was both syntax and the lack of an aggregate function (in early tests). All is well now, and I sincerely thank your cumulative contributions.
Happy coding everyone!
-Mike Scott

 

[Stella740pl]

msc0tt,

it was both syntax and the lack of an aggregate function (in early tests).

As it was already stated here, lack of an aggregate function alone wouldn't result to "Command contains unrecognized phrase/keyword" error when syntax is correct. At least in VFP6. Depending on what you are trying to do, it might give you not the result you want, but not an error. Of course it was syntax.

 

[Mike Lewis]

msc0tt,

Thanks for the feedback, but I have to agree with Stella. I'm surprised it wasn't the semi-colon issue that gave you that error.

Mike


Mike Lewis
Edinburgh, Scotland

My Visual Foxpro web site:

http://www.ml-consult.demon.co.uk

My Crystal Reports web site:

http://www.ml-crystal.com

 

[medic]

msc0tt,

Yes, the ; is really there. My only previous SQL (which was very basic) was on Oracle - we needed the ;.

To avoid similar problems in the future, keep in mind that the purpose of semicolon in Oracle is totally different from that in VFP. Semicolon is used as a SQL command terminator in Oracle, while it indicates line continuation in VFP.