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: Christian Tabligan
  Where is Christian Tabligan?
 Bacolod City, Neg. Occ.
 Philippines
 Christian Tabligan
 Tags
Subject: RE: Order clause Invalid
Thread ID: 268692 Message ID: 268778 # Views: 28 # Ratings: 0
Version: Visual FoxPro 6 Category: Databases, Tables and SQL Server
Date: Wednesday, July 14, 2010 3:21:51 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.

Regards
Samir

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