I have two tables which share one-to-many relationship. MY_FACT is the parent table whereas MY_RMDETAILS is the child table having multiple records for a single parent record.

Table MY_FACT:

Table MY_RMDETAILS:

I need to write a query to have below output.

Logic:

If MY_RMDETAILS.PROMISE_QTY is less than MY_RMDETAILS.REQUEST_QTY, the supply is insufficient.

So for any given MY_FACT record, if any one of its children records from MY_RMDETAILS has PROMISE_QTY less than REQUEST_QTY, the flag SUPPLY_SUFFICIENT in output should be N else it should be Y.

And INSUFFICIENT_RMs column in output should show the MY_RMDETAILS.RM_ITEM_NAME of "insufficient" records as comma separated format.

EXPECTED OUTPUT:

Please help. Thanks in advance.

CodePudding user response：

You can try to use subquery with condition aggregate function.

SELECT t2.*,
       CASE WHEN t1.INSUFFICIENT_cnt > 0 THEN 'N' ELSE 'Y' END,
       t1.INSUFFICIENT_RMs
FROM (
    SELECT FACT_ID,
        LISTAGG(CASE WHEN PROMISE_QTY < REQUEST_QTY THEN RM_ITEM_NAME END, ', ')  WITHIN GROUP (ORDER BY RM_ID)  INSUFFICIENT_RMs,
        COUNT(CASE WHEN PROMISE_QTY < REQUEST_QTY   THEN RM_ITEM_NAME END) INSUFFICIENT_cnt
    FROM MY_RMDETAILS
    GROUP BY FACT_ID
) t1 INNER JOIN MY_FACT t2
ON t1.FACT_ID = t2.FACT_ID