Oracle combine multiple rows and get based on condition-CodePudding

I want to find if all the items of a company are in status cancelled or pending and all the origins are only fee then take such companies only.

Comb_id status_id   status  origin_id   origin
1   6   Submitted   1   Premium
1   6   Submitted   1   Premium
2   6   Submitted   2   Fee
2   6   Submitted   1   Premium
2   6   Submitted   2   Fee
2   6   Submitted   1   Premium
3   6   Submitted   1   Premium
3   6   Submitted   1   Premium
4   1   Pending 2   Fee
4   1   Pending 2   Fee
4   1   Pending 2   Fee
4   1   Pending 2   Fee
4   1   Pending 2   Fee
4   1   Pending 2   Fee

The below query is working fine and gives correct result 4 but is this possible to use ids of status and origin rather than description and also better than this query.

SELECT
    COMB_ID
FROM
    (
    SELECT
        COMB_ID,
        CASE
            WHEN TRIM(REPLACE(REPLACE(REPLACE(STATUS, 'Cancelled', ''), 'Pending', ''), ',', '')) IS NULL THEN 1
            ELSE 0
        END AS STATUS,
        CASE
            WHEN TRIM(REPLACE(REPLACE(ORIGIN, 'Fee', ''), ',', '')) IS NULL THEN 1
            ELSE 0
        END AS ORIGIN
    FROM
        (
        SELECT
            COMB_ID,
            LISTAGG(STATUS,
            ', ') WITHIN GROUP (
        ORDER BY
            STATUS) STATUS,
            LISTAGG(ORIGIN,
            ', ') WITHIN GROUP (
        ORDER BY
            ORIGIN) ORIGIN
        FROM
            (
            SELECT
                COMB_ID,
                STATUS_ID,
                STATUS,
                ORI_ID,
                ORIGIN
            FROM
                COMPANY
            WHERE
                DETAILS = 1 )
        GROUP BY
            COMB_ID))
WHERE
    STATUS = 1;

CodePudding user response：

Using basic aggregation we can try:

SELECT COMB_ID
FROM COMPANY
GROUP BY COMB_ID
HAVING COUNT(CASE WHEN STATUS NOT IN ('Cancelled', 'Pending')
                  THEN 1 END) = 0 AND
       COUNT(CASE WHEN ORIGIN <> 'FEE' THEN 1 END) = 0;

The first portion of the HAVING clause asserts that the only status values for a matching company are cancelled and pending. The second portion asserts that the only origin value is fee.