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: Alan Tuscano
  Where is Alan Tuscano?
 Manila
 Philippines
 Alan Tuscano
 Tags
Subject: RE: Order clause Invalid
Thread ID: 268692 Message ID: 268753 # Views: 40 # Ratings: 3
Version: Visual FoxPro 6 Category: Databases, Tables and SQL Server
Date: Wednesday, July 14, 2010 11:46:05 AM         
   


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

Alan,
You should be delighted if that was the only error in this SQL. Although I wouldn't know how you could pass that error within this SQL it prevents you from the worse. Now, as a human I cannot see what you are trying to do with such an SQL then you should never expect a computer to understand you. Let's dissect the SQL and see why I can't understand. First part of the SQL:

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 ;
looks OK (Although it is better to use more meaningful local aliases it is OK to use single letter aliases). Here you are choosing multiple columns.

Let's check the second part:

 	Union All ;
 	Select C.Code as InstCode from Bank ;
 	    Where C.Id = A.BankCode ;	    


Oooppss. Union in there and trying to select a single column when you needed multiple columns??? Remember when you use "union" the column count and column data types must match in both sides of a union (first piece of SQL and second piece). There by itself is a reason this SQL wouldn't work whatever you do to rest of SQL (group, order ...). Assuming you have corrected the column count and type part, what is C? Probably you meant "from bank C". That is OK, you could correct. But then again what is that :

Where C.Id = A.BankCode ;
What is A???? If C is bank then what is A???? If bank is A then what is C???? Again, remember you are using a "union" you should think this part of SQL independent from the other part SQL. VFP should give an error here saying it cannot find Id or BankCode (if bank is A then Id else BankCode). Rework this part of SQL independently from the first one and get a working one first.

Then comes group by and order by (oh my). Let's start with the easier Order By. 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. If you still want to use column name, then wrap the entire SQL within parentheses as a subquery and use it as an alias to "from". Something like this:

select * from ;
(select myColumn, ... union ...) tmp ;
order by tmp.MyColumn
(but pity you can't do this in VFP6).

Assuming we are over that obstacle (man playing prince of persia is easier) then comes the group by. With group by again you would need to use the ordinals. But furthermore you should have all the columns that are not aggregate columns in your group by list. If you don't it would still work because you are using the buggy VFP6. It would work but the results may be correct only by chance.

OK after having said all of these why are you trying to do all these complex SQL in a single SQL? It is a myth that a single SQL works faster. It might even be slower especially in older versions. IMHO command uh divide and conquer (looks like I need a little vacation:)

Cetin Basoz

.Net has got better.Think about moving - check my blog:
Blog (main)
Blog (mirror)

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