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
>>
Go raibh maith agat
~M