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: Alan Tuscano
  Where is Alan Tuscano?
 Manila
 Philippines
 Alan Tuscano
 Tags
Subject: RE: Order clause Invalid
Thread ID: 268692 Message ID: 268772 # Views: 31 # Ratings: 0
Version: Visual FoxPro 6 Category: Databases, Tables and SQL Server
Date: Wednesday, July 14, 2010 2:34:37 PM         
   


> Hi,
>
>
> SELECT Datasrce, ;
> 		iNetwork, ;
> 		A.Source, ;
> 		BankName, ;
> 		BankCode, ;
> 		A.Service, ;
> 		MDR, ;
> 		iCount, ;
> 		AmtFee, ;
> 		Net, ;
> 		MLFee, ;
> 		Fee ;
> 	From curIssuerBn A ;
> 	Left Outer Join mchprof B ;
> 		On A.iNetwork = B.Network ;
> 		And A.Source = B.Source ;
> 		And A.Service = B.Service ;
> 		And A.Datasrce = B.DataScrc ;
> 	Union All ;
> 	Select C.Code as InstCode from Bank ;
> 	    Where C.Id = A.BankCode ;	    
> 	Group by A.iNetwork, A.Source, A.Service, A.Datasrce ;	
> 	Order by A.iNetwork, A.Bankcode ;	    
> 	Into Table curIssuer
> 

>
> Error : Order by Clause Invalid ??
>
> Where's the Error or is the Select/Union Statement wrong?
>
> Thanks.
>
> Alan
> Ad Majorem Dei Gloriam.

try this following example:

set safety off
create table temp1 (clname c(4), cfname c(4))
insert into temp1 values ("A", "B")
insert into temp1 values ("B", "B")
insert into temp1 values ("C", "B")

create table temp2 (clname c(4), cfname c(4))
insert into temp2 values ("A", "B")
insert into temp2 values ("B", "B")
insert into temp2 values ("C", "B")

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 


hope this help.

by the way, read and study the article about UNION and UNION ALL. :-)

The purpose of the SQL UNION ALL command is also to combine the results of two queries together. The difference between UNION ALL and UNION is that, while UNION only selects distinct values, UNION ALL selects all values.

The syntax for UNION ALL is as follows:

[SQL Statement 1]
UNION ALL
[SQL Statement 2]

reference:

http://www.1keydata.com/sql/sqlunionall.html
http://www.w3schools.com/sql/sql_union.asp

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