For all I remember:
always put ORDER BY infront of first UNION.

ORDER BY will run through the result and order the whole result set after any other operation. IOW you can not order separate parts of the UNION different (with such a simple construct).


>I had the order by just before the into cursor line, I took it out as it was causing the issue. I do normally include table.field name but as I was testing I didn't bother, I was actually building up the select code from a simple 2 fields to try to get to the bottom of it.
>

>order by BTR_link
>into cursor temp2
>

>~M
>>I don't see ORDER BY in your statement.
>>
>>Also, I suggest to add aliases to all columns - it is a good practice to use aliases when you use more than 1 table in your select statement.
>>
>>>>>Hi all,
>>>>>the code below works but I need to add in an order by field1, field2 but I can't get it to work, I get an order by error. I've tried a simple order by command but no joy, also tried moving between engine behaviour 70/80/90 with no luck. Any help appreciated.
>>>>>~M
>>>>>
>>>>>

>>>>>sele FIELD_LIST ;
>>>>>	from TBALE1 ;
>>>>>		join TABLE2 ;
>>>>>		join TABLE3 ;
>>>>>	where something ;
>>>>>		AND something else ;
>>>>>	UNION ;
>>>>>sele FIELD_LIST
>>>>>	from TBALE1 ;
>>>>>		join TABLE2 ;
>>>>>		join TABLE3 ;
>>>>>	where something ;
>>>>>		AND something else ;
>>>>>	into cursor temp2 nofilter
>>>>>

>>>>
>>>>Adding ORDER BY before into cursor should work. What is the exact error you're getting?
>>>>
>>>>In the worse case scenario you can
>>>>
>>>>select * from (...
>>>>UNION
>>>>...) X
>>>>ORDER BY ...
>>>I am toying with the idea of a secondary select on the result, but I'd rather do the job in 1 query as I have 5 such queries to do and they are looking at reasonably large data sets, so better off not running commands that I don't have to.
>>>The error I get back is "SQL: Order by clasue is invalid", this is for engine behaviour set to 70 or 80. If I set it to 90 I get a syntax error and then this error, however I think that the engine behaviour 90 syntax error is not related to the order by.
>>>I've included one of the selects in full below.
>>>~M
>>>
>>>

>>>set engine 80
>>>
>>>* Get the cash lines
>>>sele BTR_link ;
>>>			, BTR_per ;
>>>			, BTR_supref ;
>>>			, BMA_name ;
>>>			, BTR_short ;
>>>			, BTR_netval as xNet ;
>>>			, BTR_vatval as xVat ;
>>>			, BTR_exchg ;
>>>			, BTR_curr ;
>>>			, round(BTR_netval/BTR_exchg,2) as xValue ;
>>>			, BTR_ordref ;
>>>			, BTR_ourref ;
>>>			, BTR_invref ;
>>>			, BTR_invdat ;
>>>			, BTR_bnkref ;
>>>			, BTR_bnkval ;
>>>			, space(25) as BDE_stkref ;
>>>			, space(10) as BDE_nom ;
>>>			, space(10) as BDE_cc ;
>>>			, A_rectyp ;
>>>			, A_tratyp ;
>>>			, A_nomctrl ;
>>>			, A_nccreve ;
>>>			, A_nomreve ;
>>>		from kfbtr ;
>>>			join kflli on BTR_short = a_short and a_rectyp = "10" ;
>>>			join kfbma on BTR_supref = BMA_ref ;
>>>		where BTR_per >= 201601 AND BTR_per <= 201612 ;
>>>			AND BTR_stage = "70" ;
>>>			AND (a_tratyp = "C" OR a_tratyp = "J") ;
>>>	UNION ;
>>>sele BTR_link ;
>>>			, BTR_per ;
>>>			, BTR_supref ;
>>>			, BMA_name ;
>>>			, BTR_short ;
>>>			, BDE_linval as xNet ;
>>>			, BDE_linvat as xVat ;
>>>			, BTR_exchg ;
>>>			, BTR_curr ;
>>>			, round(BDE_linval/BTR_exchg,2) as xValue ;
>>>			, BTR_ordref ;
>>>			, BTR_ourref ;
>>>			, BTR_invref ;
>>>			, BTR_invdat ;
>>>			, BTR_bnkref ;
>>>			, BTR_bnkval ;
>>>			, BDE_stkref ;
>>>			, BDE_nom ;
>>>			, BDE_cc ;
>>>			, A_rectyp ;
>>>			, A_tratyp ;
>>>			, A_nomctrl ;
>>>			, A_nccreve ;
>>>			, A_nomreve ;
>>>		from kfbtr ;
>>>			outer join kfbde on btr_link = bde_link ;
>>>			join kflli on BTR_short = a_short and a_rectyp = "10" ;
>>>			join kfbma on BTR_supref = BMA_ref ;
>>>		where BTR_per >= 201601 AND BTR_per <= 201612 ;
>>>			AND BTR_stage = "70" ;
>>>			AND a_tratyp = "I" ;
>>>		into cursor temp2
>>>