Hi Experts,
i have a little query. When i am executing below query it is providing below result.
select customer_trx_id,line_number,inventory_item_id,description
from ra_customer_trx_lines_all where customer_trx_id='6983469'
and line_type='LINE'
order by line_number asc;
CUSTOMER_TRX_ID LINE_NUMBER INVENTORY_ITEM_ID DESCRIPTION
---------------------- ---------------------- ---------------------- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
6983469 1 47002 eske Liten PK/30
6983469 2 44110 eske Stor PK/20
but when i am executing the below script
set serveroutput on;
DECLARE
cursor c_test(p_cust_trx_id IN NUMBER,p_ord_by IN VARCHAR2)
is
select customer_trx_id,customer_trx_line_id,line_number,inventory_item_id,description
from ra_customer_trx_lines_all where customer_trx_id=p_cust_trx_id
and line_type='LINE'
order by p_ord_by;
v_ord_by VARCHAR2(100);
BEGIN
dbms_output.put_line('Start');
v_ord_by := 'line_number asc';
FOR r_test in c_test('6983469',v_ord_by)
loop
dbms_output.put_line('r_test.customer_trx_id-->'||r_test.customer_trx_id);
dbms_output.put_line('r_test.customer_trx_line_id-->'||r_test.customer_trx_line_id);
dbms_output.put_line('r_test.customer_trx_id-->'||r_test.customer_trx_id);
dbms_output.put_line('r_test.line_number-->'||r_test.line_number);
dbms_output.put_line('r_test.inventory_item_id-->'||r_test.inventory_item_id);
dbms_output.put_line('r_test.description-->'||r_test.description);
END LOOP;
END;
/
anonymous block completed
Start
r_test.customer_trx_id-->6983469
r_test.customer_trx_line_id-->83439746
r_test.customer_trx_id-->6983469
r_test.line_number-->2
r_test.inventory_item_id-->44110
r_test.description--> eske Stor PK/20
r_test.customer_trx_id-->6983469
r_test.customer_trx_line_id-->83439745
r_test.customer_trx_id-->6983469
r_test.line_number-->1
r_test.inventory_item_id-->47002
r_test.description--> eske Liten PK/30
The program is executing but the order by clause is not working as intended. The line number 1 should appear first but here line number 2 is appearing. Could you please let me know why it is appearing like that and how can i rectify it.
Regards
Rajat