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
>
If it's not broken, fix it until it is.
My Blog