Welcome To The Home Of The Visual FoxPro Experts  
home. signup. forum. archives. search. google. articles. downloads. faq. members. weblogs. file info. rss.
 From: Tamar Granor
  Where is Tamar Granor?
 Elkins Park
 Pennsylvania - United States
 Tamar Granor
 To: Samir H.
  Where is Samir H.?
 Yogyakarta
 Indonesia
 Samir H.
 Tags
Subject: RE: Order clause Invalid
Thread ID: 268692 Message ID: 268833 # Views: 26 # Ratings: 0
Version: Visual FoxPro 6 Category: Databases, Tables and SQL Server
Date: Wednesday, July 14, 2010 10:22:25 PM         
   


>
> > select clname from temp1; 
> > union all; 
> > select clname from temp2;
> > order by clname && order clause is invalid. 
> > 
> > select clname from temp1;
> > union all;
> > select clname from temp2;
> > order by 1 && try this 
> > 

> >
>
> Can you (or somebody else) explain why this works?
>
> Cetin said:
> "...In a "union" the order by applies to final set, not to individual SQLs unioned. For this reason I think, you are not allowed to refer to it by a column name. Instead use the ordinal of column in the resulting cursor to order by..."
>
> Why "For this reason"? Why should the ORDER BY clause care whether the query was composed of two subqueries, since it applies to the FINAL result. The final result is a cursor with field names, and there shouldn't be any name conflicts then, isn't it?
> Thanks for explanations, from a newbee in SQL questions.

That's simply the rule. When you have ORDER BY in a UNIONed query, you have to use column position rather than name to specify the order. It's been that way in VFP since SQL SELECT was added.

My suspicion is that it makes parsing the query easier and that no one in the Fox community every complained loud enough to get the team to do the harder parsing.

Tamar

ENTIRE THREAD

Order clause Invalid Posted by Alan Tuscano @ 7/14/2010 3:25:54 AM
RE: Order clause Invalid Posted by Jun Tangunan @ 7/14/2010 3:33:10 AM
RE: Order clause Invalid Posted by Alan Tuscano @ 7/14/2010 4:51:20 AM
RE: Order clause Invalid Posted by Mike Yearwood @ 7/14/2010 5:49:45 AM
RE: Order clause Invalid Posted by Cetin Basoz @ 7/14/2010 11:46:05 AM
RE: Order clause Invalid Posted by Anders Altberg @ 7/14/2010 1:24:47 PM
RE: Order clause Invalid Posted by Christian Tabligan @ 7/14/2010 2:34:37 PM
RE: Order clause Invalid Posted by Samir H. @ 7/14/2010 3:21:51 PM
RE: Order clause Invalid Posted by Tamar Granor @ 7/14/2010 10:22:25 PM
RE: Order clause Invalid Posted by Christian Tabligan @ 7/15/2010 2:15:16 AM