1.ORDERS_SHIPMENT_RELATION_VIEW is a DB view.
2 Below query is invoked through JDBC.executeQuery() and having binding variable
View :
CREATE OR REPLACE FORCE EDITIONABLE VIEW "TESTDB"."ORDERS_SHIPMENT_RELATION_VIEW" ("ORDER_ID", "TC_ORDER_ID", "DO_STATUS", "SHIPMENT_ID") AS
SELECT L.ORDER_ID,
L.TC_ORDER_ID,
O.DO_STATUS,
L.SHIPMENT_ID
FROM LPN L, ORDERS O
WHERE L.ORDER_ID = O.ORDER_ID AND L.SHIPMENT_ID > -1
UNION ALL
SELECT L.ORDER_ID,
L.TC_ORDER_ID,
O.DO_STATUS,
L.PLAN_LOAD_ID
FROM LPN L, ORDERS O
WHERE L.ORDER_ID = O.ORDER_ID AND L.PLAN_LOAD_ID > -1
UNION ALL
SELECT SAO.ORDER_ID,
O.TC_ORDER_ID,
O.DO_STATUS,
SAO.SHIPMENT_ID
FROM STOP_ACTION_ORDER SAO, ORDERS O
WHERE O.ORDER_ID = SAO.ORDER_ID AND O.DO_STATUS < 150
UNION ALL
SELECT O.ORDER_ID,
O.TC_ORDER_ID,
O.DO_STATUS,
O.SHIPMENT_ID
FROM ORDERS O
WHERE O.SHIPMENT_ID > -1 AND O.DO_STATUS < 150
UNION ALL
SELECT OM.ORDER_ID,
O.TC_ORDER_ID,
O.DO_STATUS,
OM.SHIPMENT_ID
FROM ORDER_MOVEMENT OM, ORDERS O
WHERE O.ORDER_ID = OM.ORDER_ID AND O.DO_STATUS < 150;
Query:
SELECT DISTINCT ORDERS.TC_ORDER_ID TCDOId, ORDERS.ORDER_ID distributionOrderId, ORDERS_SHIPMENT_RELATION_VIEW.SHIPMENT_ID
ordrMovShipmentId, ORDERS.TC_ORDER_ID TCDistributionOrderId, ORDERS.TC_COMPANY_ID companyId,
ORDERS.O_FACILITY_ALIAS_ID originFacilityName, ORDERS.O_FACILITY_NAME origFacility, ORDERS.O_ADDRESS_1
originAddress1, ORDERS.O_ADDRESS_2 originAddress2, ORDERS.O_ADDRESS_3 originAddress3, ORDERS.D_FACILITY_ALIAS_ID
destnFacilityName, ORDERS.D_FACILITY_NAME destFacility, ORDERS.D_ADDRESS_1 destnAddress1, ORDERS.D_ADDRESS_2
destnAddress2, ORDERS.D_ADDRESS_3 destnAddress3, ORDERS.PICKUP_START_DTTM pickupStartDttm,
ORDERS.PICKUP_TZ origTZ, ORDERS.DELIVERY_END_DTTM deliveryEndDttm, ORDERS.DELIVERY_TZ destTZ
FROM ORDERS INNER JOIN ORDERS_SHIPMENT_RELATION_VIEW ON ORDERS.ORDER_ID = ORDERS_SHIPMENT_RELATION_VIEW.ORDER_ID
WHERE ((ORDERS.TC_ORDER_ID ='201142354' and ORDERS.ORDER_ID =20492675 and ORDERS_SHIPMENT_RELATION_VIEW.SHIPMENT_ID
=24438 ) or (ORDERS.TC_ORDER_ID ='201142358' and ORDERS.ORDER_ID =20492690 and ORDERS_SHIPMENT_RELATION_VIEW.SHIPMENT_ID
=24438 ) or (ORDERS.TC_ORDER_ID ='201142359' and ORDERS.ORDER_ID =20492704 and ORDERS_SHIPMENT_RELATION_VIEW.SHIPMENT_ID
=24438 ) or (ORDERS.TC_ORDER_ID ='201142379' and ORDERS.ORDER_ID =20492708 and ORDERS_SHIPMENT_RELATION_VIEW.SHIPMENT_ID
=24438 ) or (ORDERS.TC_ORDER_ID ='201142387' and ORDERS.ORDER_ID =20492677 and ORDERS_SHIPMENT_RELATION_VIEW.SHIPMENT_ID
=24438 ) or (ORDERS.TC_ORDER_ID ='201142396' and ORDERS.ORDER_ID =20492680 and ORDERS_SHIPMENT_RELATION_VIEW.SHIPMENT_ID
=24438 ) or (ORDERS.TC_ORDER_ID ='201142397' and ORDERS.ORDER_ID =20492750 and ORDERS_SHIPMENT_RELATION_VIEW.SHIPMENT_ID
=24438 ) or (ORDERS.TC_ORDER_ID ='201142403' and ORDERS.ORDER_ID =20492694 and ORDERS_SHIPMENT_RELATION_VIEW.SHIPMENT_ID
=24438 ) or (ORDERS.TC_ORDER_ID ='201142407' and ORDERS.ORDER_ID =20492734 and ORDERS_SHIPMENT_RELATION_VIEW.SHIPMENT_ID
=24438 ) or (ORDERS.TC_ORDER_ID ='201142429' and ORDERS.ORDER_ID =20492781 and ORDERS_SHIPMENT_RELATION_VIEW.SHIPMENT_ID
=24438 ) or (ORDERS.TC_ORDER_ID ='201142440' and ORDERS.ORDER_ID =20492719 and ORDERS_SHIPMENT_RELATION_VIEW.SHIPMENT_ID
=24438 ) or (ORDERS.TC_ORDER_ID ='201142443' and ORDERS.ORDER_ID =20492686 and ORDERS_SHIPMENT_RELATION_VIEW.SHIPMENT_ID
=24438 ) or (ORDERS.TC_ORDER_ID ='201142452' and ORDERS.ORDER_ID =20492702 and ORDERS_SHIPMENT_RELATION_VIEW.SHIPMENT_ID
=24438 ) or (ORDERS.TC_ORDER_ID ='201142465' and ORDERS.ORDER_ID =20492768 and ORDERS_SHIPMENT_RELATION_VIEW.SHIPMENT_ID
=24438 ) or (ORDERS.TC_ORDER_ID ='201142525' and ORDERS.ORDER_ID =20493944 and ORDERS_SHIPMENT_RELATION_VIEW.SHIPMENT_ID
=24438 ) or (ORDERS.TC_ORDER_ID ='201142532' and ORDERS.ORDER_ID =20493913 and ORDERS_SHIPMENT_RELATION_VIEW.SHIPMENT_ID
=24438 ) or (ORDERS.TC_ORDER_ID ='201142612' and ORDERS.ORDER_ID =20494472 and ORDERS_SHIPMENT_RELATION_VIEW.SHIPMENT_ID
=24438 ) or (ORDERS.TC_ORDER_ID ='201142691' and ORDERS.ORDER_ID =20495361 and ORDERS_SHIPMENT_RELATION_VIEW.SHIPMENT_ID
=24438 ) or (ORDERS.TC_ORDER_ID ='201142692' and ORDERS.ORDER_ID =20495395 and ORDERS_SHIPMENT_RELATION_VIEW.SHIPMENT_ID
=24438 ) or (ORDERS.TC_ORDER_ID ='201142729' and ORDERS.ORDER_ID =20496169 and ORDERS_SHIPMENT_RELATION_VIEW.SHIPMENT_ID
=24438 ) ) ORDER BY ORDERS.TC_ORDER_ID asc {executed in 74560 ms}