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


> > ...I don't understand why would you expect it to error in order by. Grouping order makes a difference on the result, right but that wasn't what he asked for.
> 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)

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