>To unduplicate the list change UNION ALL to UNION.Right on the mark, Sergey, that did it.
Also remove GROUP BY because it's unnecessary and only applies to the select statement it included in.So when UNIONing, SELECT DISTINCT is implicit? Or can I put the 5 SELECTs in parentheses and the GROUP BY would then apply to all?
>>For a client with very denormalized data structures (in an old application), I first wrote a simple SQL-Select to get a list of all the route numbers from the first of 5 route number columns:
>>>>SELECT route_no FROM (ClientDataPath+'customer') ;
>> GROUP BY route_no ORDER BY route_no ;
>>INTO CURSOR RouteList
>>
>>All works well there. However, this does not:
>>>>SELECT route_no AS routenum ;
>> FROM (ClientDataPath+'customer') ;
>> WHERE .NOT.EMPTY(route_no) ;
>>UNION ALL ;
>>SELECT route_no2 AS routenum ;
>> FROM (ClientDataPath+'customer') ;
>> WHERE .NOT.EMPTY(route_no2) ;
>>UNION ALL ;
>>SELECT route_no3 AS routenum ;
>> FROM (ClientDataPath+'customer') ;
>> WHERE .NOT.EMPTY(route_no3) ;
>>UNION ALL ;
>>SELECT rr_1 AS routenum ;
>> FROM (ClientDataPath+'customer') ;
>> WHERE .NOT.EMPTY(rr_1) ;
>>UNION ALL ;
>>SELECT rr_2 AS routenum ;
>> FROM (ClientDataPath+'customer') ;
>> WHERE .NOT.EMPTY(rr_2) ;
>>GROUP BY routenum ORDER BY routenum ;
>>INTO CURSOR RouteList
>>
>>Assistance gratefully accepted - apparently I've done something wrong while UNIONing.
>>
>>Thanx!
Randy Bosma
VFP - Because life is too short to code in something else...
