Open Sales orders

Question

My aim is to write a SQL query on S/4 hana system to get a list of open sales orders.

Open sales orders are those orders which have been partially shipped or have not been shipped at all.

I have two tables - Sales order table and Shipped Orders table

Sales Order Table - table 1

VBELN - Order Number - primary key

Ord_qty - Order qty

values in the table

VBELN    ORD_QTY

1         10

2         20

3         30

Shipped Orders table - table 2

SHIP_ORD - Shipment number - Primary key

VBELN - Order number - Foreign key from table 1

Ship_qty - shipment quantity

Values in table 2

SHIP_ORD    VBELN     SHIP_QTY

100          1         4

200          1         5

300          2         20

I wrote this query. This returns me

VBELN    ORD_QTY   SHIP_QTY

1         10          9

But I want it also return another record 3 30 0 for the order that has not been shipped.

Here is my query

get open orders ie orders not shipped and orders that are not shipped completely

SELECT a~vbeln,sum( b~ship_qty ) as ship_qty, sum( ORD_QTY ) as ord_type
     INTO TABLE @DATA(LT_DATA)
       from zvic_order as a
       left outer join zvic_ship as b
       on a~vbeln = b~vbeln
       where b~ship_qty is not null or
       b~ship_qty is null
       GROUP BY A~VBELN
       HAVING sum( ord_qty ) > sum( b~ship_qty ) .  

I think it is the having condition that is eliminating the NULL entry from shipment table but I don't know any other way of writing this query and obtaining the desired result.

PS: I can get the records without having condition and then loop on the records and filter out but my aim to get the result directly from the query.

Answer 1

Could you please try following SQL SELECT command ?

select
    o.vbeln,
    o.ord_qty,
    s.ship_qty
from zvic_order as o
left outer join (
    select VBELN, sum(SHIP_QTY) as ship_qty from zvic_ship group by VBELN
) as s on o.vbeln = s.vbeln
where o.ord_qty > ifnull(s.ship_qty,0);

It produces following output

Answer 2

Try this -

SELECT a~vbeln,sum( b~ship_qty ) as ship_qty, sum( ORD_QTY ) as ord_type
INTO TABLE @DATA(LT_DATA)
   from zvic_order as a
   left outer join (SELECT b~VBELN, SSUM(SHIP_QTY) SHIP_QTY
                    FROM zvic_ship
                    GROUP BY b~VBELN) as b
   on a~vbeln = b~vbeln
   GROUP BY A~VBELN
   HAVING sum( ord_qty ) > sum( b~ship_qty )
          OR sum( b~ship_qty ) IS NULL

Answer 3

Use IFNULL for the SUM() to correctly handle rows not shipped at all

SELECT vbeln, ord_qty, IFNULL(SUM(ship_qty),0) as total_shipped
FROM (SELECT o.vbeln, ord_qty, s.ship_qty
      FROM zvic_order o
      LEFT JOIN zvic_ship s ON s.vbeln = o.vbeln ) as j
GROUP BY vbeln
HAVING (ord_qty > total_shipped)