Welcome To The Home Of The Visual FoxPro Experts  
home. signup. forum. archives. search. google. articles. downloads. faq. members. weblogs. file info. rss.
 From: Samir H.
  Where is Samir H.?
 Yogyakarta
 Indonesia
 Samir H.
 To: Cetin Basoz
  Where is Cetin Basoz?
 Izmir
 Turkey
 Cetin Basoz
 Tags
Subject: RE: Calcultions
Thread ID: 268787 Message ID: 268923 # Views: 23 # Ratings: 0
Version: Visual FoxPro 6 Category: Databases, Tables and SQL Server
Date: Thursday, July 15, 2010 1:04:35 PM         
   


Hi Cetin,
It was not my intention to "trigger" a lesson, but what you said here brings me a BIG step forward in understanding ORDER BY, and why I often get errors when using it.
This said, the query I pasted here was NOT mine: it was Yehuda's. I tried it and it raised an error. I didn't understand why. So I just exchanged order positions, removed some, added others etc... getting GROUP BY errors in between, and finally got a query that worked. I looked at the results and thought they match more to what the OP wanted.

So I didn't win the competition, but still got a reward. :)

> > I didn't expect it to error, since the ORDER BY INVALID errors are still a mystery to me, it just happened. Or is the something wrong with the test cursor I created?
> > This brings an error, I tested again today:
> >
> >
CREATE CURSOR mytable (Product c(10),Amount i, Quality c(4), Location c(10))
> > FOR i=1 TO 20
> > 	INSERT INTO mytable VALUES ('Product '+CHR(65+MOD(i,5)),RAND()*i*10,'Good','Location '+CHR(65+MOD(i,3)*MOD(5,i)))
> > NEXT 
> > BROWSE
> > select product, location, sum(amount) as amount ;
> > from myTable ;
> > group by product, location ;
> > order by product, amount desc ;
> > into cursor crsIntermediate1 
> > 
> > select product, location, max(amount) as amount ;
> > from crsIntermediate1 ;
> > group by product ;
> > into cursor crsFinal1

> >
>
> Samir,
> Of course you would get an error if you try to have one:) You made typos here or they are not typos?
> First you used "amount" in order by listing. It is an aggregate column that doesn't exist before the query. Use ordinal instead if you need it (order by product, 3 desc)
> Second you use a group by that doesn't have all non aggregate columns listed in second query (group by product, location).
> (VFP got closer to MS SQL but it is still far different and even in MS SQL it is not done like that).
>
> PS: In VFP6 this should work as I remember (but as I said before result would be correct due to buggy behavior of VFP6). Compete but do not test me please:) We have a Turkish saying "Horn passes ear (in length)" something meaning a newcomer may get ahead of the teacher in time. You are good and one day I am sure you will be better, just not today (and another Turkish saying completes it "But the ear hears":)
>
> Cetin Basoz
>
> .Net has got better.Think about moving - check my blog:
> Blog (main)
> Blog (mirror)



Regards
Samir

ENTIRE THREAD

Calcultions Posted by Yehuda Epstein @ 7/14/2010 5:01:46 PM
RE: Calcultions Posted by Cetin Basoz @ 7/14/2010 5:08:38 PM
RE: Calcultions Posted by Yehuda Epstein @ 7/14/2010 5:48:45 PM
RE: Calcultions Posted by tushar @ 7/14/2010 5:59:14 PM
RE: Calcultions Posted by Yehuda Epstein @ 7/14/2010 6:10:43 PM
RE: Calcultions Posted by tushar @ 7/14/2010 7:19:10 PM
RE: Calcultions Posted by Cetin Basoz @ 7/14/2010 6:24:09 PM
RE: Calcultions Posted by Samir H. @ 7/14/2010 6:48:57 PM
RE: Calcultions Posted by Yehuda Epstein @ 7/14/2010 7:11:38 PM
RE: Calcultions Posted by Cetin Basoz @ 7/14/2010 9:50:36 PM
RE: Calcultions Posted by Samir H. @ 7/15/2010 3:54:29 AM
RE: Calcultions Posted by Cetin Basoz @ 7/15/2010 12:03:52 PM
RE: Calcultions Posted by Samir H. @ 7/15/2010 1:04:35 PM
RE: Calcultions Posted by Yehuda Epstein @ 7/14/2010 6:49:47 PM
RE: Calcultions Posted by Samir H. @ 7/14/2010 6:58:04 PM