>>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
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