| 1 |
merge into xxt_baikal_sync_req sync_req using ( select order_num, trackingnumber, to_date(order_date, 'yyyy-mm-dd"t"hh24:mi:ss') as order_date, round(to_number(order_sum, '99999999d999999999999999', 'nls_numeric_characters = ''. '''), 2) as order_sum, order_from, order_to, round(to_number(order_weight, '99999999d999999999999999', 'nls_numeric_characters = ''. '''), 2) as order_weight, round(to_number(order_volume, '99999999d999999999999999', 'nls_numeric_characters = ''. '''), 2) as order_volume, order_status_id, order_status_name, cargo_ref, cargo_num, cargo_reciept from json_table( ( select resp_json from xxt_baikal_json where method_name = 'v2/order/request' ), '$.requestlist[*]' columns ( order_num varchar2(50) path '$.number', trackingnumber varchar2(100) path '$.trackingnumber', order_date varchar2(30) path '$.date', order_sum varchar2(30) path '$.total', order_from varchar2(100) path '$.from', order_to varchar2(100) path '$.to', order_weight varchar2(30) path '$.weight', order_volume varchar2(30) path '$.volume', order_status_id number path '$.status.id', order_status_name varchar2(100) path '$.status.name', nested path '$.cargolist[*]' columns ( cargo_ref varchar2(100) path '$.referencenumber', cargo_num varchar2(50) path '$.numbercargo', cargo_reciept varchar2(50) path '$.numberorder' ) ) ) ) api_data on ( api_data.order_num = sync_req.order_num and api_data.cargo_num = sync_req.cargo_num and api_data.cargo_reciept = sync_req.cargo_reciept ) when not matched then insert ( order_num, trackingnumber, order_date, order_sum, order_from, order_to, order_weight, order_volume, order_status_id, order_status_name, cargo_ref, cargo_num, cargo_reciept ) values ( api_data.order_num, api_data.trackingnumber, api_data.order_date, api_data.order_sum, api_data.order_from, api_data.order_to, api_data.order_weight, api_data.order_volume, api_data.order_status_id, api_data.order_status_name, api_data.cargo_ref, api_data.cargo_num, api_data.cargo_reciept ); commit; |
Комментарии