Welcome To The Home Of The Visual FoxPro Experts  
home. signup. forum. archives. search. google. articles. downloads. faq. members. weblogs. file info. rss.
 From: Christian Tabligan
  Where is Christian Tabligan?
 Bacolod City, Neg. Occ.
 Philippines
 Christian Tabligan
 To: Samir H.
  Where is Samir H.?
 Yogyakarta
 Indonesia
 Samir H.
 Tags
Subject: RE: Order clause Invalid
Thread ID: 268692 Message ID: 268848 # Views: 24 # Ratings: 0
Version: Visual FoxPro 6 Category: Databases, Tables and SQL Server
Date: Thursday, July 15, 2010 2:15:16 AM         
   


>
> > 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

Maybe, to avoid field name conflicts even if the data came from the same fieldnames. It would be possible that the fields on either of the two queries are not the same.

select clname from temp1;
union all;
select clastname from temp2;
order by 1 


In the example above, the two queries composed of fields which have the same datatype but different in fieldname. The final field name of that UNION will follow the field name of the first query which is "clname", and not "clastname". To avoid conflict, it would be safe if you are using ORDER BY "ordinal" rather that "field name".


Best Regards,

CriZ (,")

"Happiness is real when shared."

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