How to multiple lines, and results show only in the first row, other lines show is empty-CodePudding

SQL is too long, only posted about structure
The select ohdr. Order_number
, oline line_number | | '| | oline. Shipment_number line_number
, asgn delivery_id
, (
Select
The sum (WDD. Requested_quantity)
The from oe_order_headers_all oh,
Oe_order_lines_all ol,
Wsh_delivery_details WDD,
Wsh_delivery_assignments wda,
Wsh_new_deliveries WND
Where _id _id=ol.org oh.org
And oh. Header_id=ol. Header_id
And ol. Line_id=WDD. Source_line_id
And WDD. DELIVERY_DETAIL_ID=wda. DELIVERY_DETAIL_ID
And wda. DELIVERY_ID=WND. DELIVERY_ID

And oh, order_number=ohdr order_number
And ol. Line_number=oline. Line_number
And ol. Shipment_number=oline. Shipment_number
And WDD. LOT_NUMBER=ddet. LOT_NUMBER

Group by Mr. Oh. Order_number,
Ol. Line_number,
Ol. Shipment_number,
WND. The name,
WDD. LOT_NUMBER

) qty_by_job
.
The FROM wsh_delivery_details ddet
, (SELECT l. *
And NVL (m1) inventory_item_id, l.i nventory_item_id) item_id
The FROM oe_order_lines l
, mtl_system_items_b m1
WHERE l.c ustomer_job=m1. Segment1 (+)
AND l.s hip_from_org_id=m1.org anization_id oline (+))
, oe_order_headers ohdr
./* are there any other table, to name a list, only the column and the subquery associated with */
GROUP BY.../* from used in all fields */

The REQUESTED_QUANTITY sum, according to the result is shown in QTY_BY_JOB
LOT_NUMBER=6069749, for example, there are two lines, LINE_NUMBER are respectively 2.2, 2.1

LINE_NUMBER is 2.2, REQUESTED_QUANTITY has two lines (excel line 4, 5), is 966, respectively, the results of the two Numbers 1000,
Excel of 4, 5 lines shows 1000, but I hope the excel line 4 shows 1000, 5 behavior empty

LINE_NUMBER is 2.1, REQUESTED_QUANTITY have two lines (excel line 6, 7, 1888320), the results of the two Numbers 2208,
Excel shows the 2208 6, 7 lines, but I hope the excel 2208, line 6 will show empty line 7

Pictured above is the query results, below is expected results

CodePudding user response:

What a great god for help

CodePudding user response:

What a great god for help

CodePudding user response:

At the bottom of the statement is too long, only to see you the two figure,

 decode (row_number () over (partition by lot_number order by requested_quantity desc), 1, qty_by_job, null)

CodePudding user response:

reference nayi_224 reply: 3/f

statement is too long, just see you below the two figure,
 decode (row_number () over (partition by lot_number order by requested_quantity desc), 1, qty_by_job, null) 

this function seems to be a result can reach the effect you want you go to baidu search under the function of the detailed usage should be, and you mean a little didn't understand in this picture, two rows are 2208 and 1000 but the results you want is really a value is the sum of the and want to get a maximum QUANTITY?