Home » SQL & PL/SQL » SQL & PL/SQL » Please correct this query for me
Please correct this query for me [message #217388] Thu, 01 February 2007 23:48 Go to next message
rak007
Messages: 107
Registered: October 2006
Location: Mumbai / Pune, India
Senior Member
Hi The following query uses an inline query. I want the same output after removing the inline query. Please some one modify the same. here table NUIF_HB_E02J_SHIPMENTS is a custom table.

select count(*)
into l_back_detail_cnt
FROM
(SELECT WDA.DELIVERY_ID DELIVERY_ID,
WDD.DELIVERY_DETAIL_ID DELIVERY_DETAIL_ID
FROM WSH_DELIVERY_DETAILS WDD,
WSH_DELIVERY_ASSIGNMENTS WDA
WHERE
WDA.DELIVERY_DETAIL_ID= WDD.DELIVERY_DETAIL_ID) PLD,
NUIF_HB_E02J_SHIPMENTS E2J
WHERE PLD.DELIVERY_ID = p_delivery_rec_i.DELIVERY_ID and
PLD.DELIVERY_ID = E2J.DELIVERY_ID(+) and
PLD.DELIVERY_DETAIL_ID = E2J.PICKING_SLIP_LINE_NUMBER(+) and
E2J.PICKING_SLIP_LINE_NUMBER is null and
PLD.DELIVERY_DETAIL_ID = PLD.DELIVERY_DETAIL_ID
Re: Please correct this query for me [message #217432 is a reply to message #217388] Fri, 02 February 2007 03:07 Go to previous messageGo to next message
martijn
Messages: 283
Registered: December 2006
Location: Netherlands
Senior Member
Maybe something like :


select count(*) into l_back_detail_cnt
FROM WSH_DELIVERY_DETAILS WDD,
WSH_DELIVERY_ASSIGNMENTS WDA
WHERE
WDA.DELIVERY_DETAIL_ID= WDD.DELIVERY_DETAIL_ID) PLD,
NUIF_HB_E02J_SHIPMENTS E2J
WHERE PLD.DELIVERY_ID = p_delivery_rec_i.DELIVERY_ID and
PLD.DELIVERY_ID = E2J.DELIVERY_ID(+) and
PLD.DELIVERY_DETAIL_ID = E2J.PICKING_SLIP_LINE_NUMBER(+) and
E2J.PICKING_SLIP_LINE_NUMBER is null and
PLD.DELIVERY_DETAIL_ID = PLD.DELIVERY_DETAIL_ID


Re: Please correct this query for me [message #217437 is a reply to message #217388] Fri, 02 February 2007 03:26 Go to previous message
rak007
Messages: 107
Registered: October 2006
Location: Mumbai / Pune, India
Senior Member
The inline query
SELECT WDA.DELIVERY_ID DELIVERY_ID,
WDD.DELIVERY_DETAIL_ID DELIVERY_DETAIL_ID
FROM WSH_DELIVERY_DETAILS WDD,
WSH_DELIVERY_ASSIGNMENTS WDA
WHERE
WDA.DELIVERY_DETAIL_ID= WDD.DELIVERY_DETAIL_ID

shouldnt be used instead use the tables in the main query
Previous Topic: what is mean by query optimization? and Explain plan?How we optimize the query??
Next Topic: Please Help to Solve this Query..
Goto Forum:
  


Current Time: Mon Dec 05 04:53:04 CST 2016

Total time taken to generate the page: 0.12941 seconds