>>Having problems figuring this out:
>>
>>This gives an error, complaining about the Order by clause. Must be caused by the nvl() function?
>>
>>

>>Select vendor From CopyTemplateMtl
>> Union All
>>  Select Nvl(vendor,'') From c:\testdata
>>   Order By vendor
>>

>>
>>I also tried:
>>
>>

>>Select vendor From CopyTemplateMtl
>> Union All
>>  Select Nvl(vendor,'') as vendor From c:\testdata
>>   Order By vendor
>>

>>
>>
>>If I leave out the Order By clause, it works, but the results are not ordered like I want.
>
>In this case you have to use numbers instead of the field name, e.g.
>
>ORDER BY 1
>
>(It's VFP limitation).

it is a bug.

the origin of this bug is into this simple example:

CREATE CURSOR bb (x i)
INSERT INTO bb VALUES (3)
INSERT INTO bb VALUES (1)
SELECT -X AS X FROM bb ORDER BY X

* return
* -1
* -3

on MSSQL and others engines the correct result it is:

CREATE TABLE #bb (x iNT)
INSERT INTO #bb VALUES (3)
INSERT INTO #bb VALUES (1)
SELECT -X AS X FROM #bb ORDER BY X
DROP TABLE #bb

-- -3
-- -1