Error When Trying to Use MAX() Aggregate Function-CodePudding

I am using a query with a pivot to avoid multiple rows, but still getting multiple rows.

Query

SELECT B1_ALT_ID, SERV_PROV_CODE, CERTIFICATE_NUMBER, DIF_CATEGORY, B1_SHORT_NOTES, REC_DATE
FROM ACCELA.B1PERMIT A
JOIN BCHCKBOX B
ON A.B1_PER_ID1 = B.B1_PER_ID1
AND A.B1_PER_ID3 = B.B1_PER_ID3
AND( B.B1_CHECKBOX_DESC = 'Certificate Number' OR B.B1_CHECKBOX_DESC = 'DIF_Category' )
JOIN BPERMIT_DETAIL C
ON C.B1_PER_ID1 = B.B1_PER_ID1
AND C.B1_PER_ID3 = B.B1_PER_ID3
PIVOT(
    MAX( B.B1_CHECKLIST_COMMENT )
    FOR B1_CHECKBOX_DESC IN (
        'Certificate Number' AS CERTIFICATE_NUMBER,
        'DIF_Category' AS DIF_CATEGORY
    )
)
WHERE( B1_ALT_ID LIKE 'DIF2%' OR B1_ALT_ID LIKE 'DIF1%' );

Current Result

Desired Result

To avoid the multiple rows, I tried using the MAX() aggregate function:

SELECT B1_ALT_ID, SERV_PROV_CODE, CERTIFICATE_NUMBER, DIF_CATEGORY, B1_SHORT_NOTES, MAX( REC_DATE )
FROM ACCELA.B1PERMIT A
JOIN BCHCKBOX B
ON A.B1_PER_ID1 = B.B1_PER_ID1
AND A.B1_PER_ID3 = B.B1_PER_ID3
AND( B.B1_CHECKBOX_DESC = 'Certificate Number' OR B.B1_CHECKBOX_DESC = 'DIF_Category' )
JOIN BPERMIT_DETAIL C
ON C.B1_PER_ID1 = B.B1_PER_ID1
AND C.B1_PER_ID3 = B.B1_PER_ID3
PIVOT(
    MAX( B.B1_CHECKLIST_COMMENT )
    FOR B1_CHECKBOX_DESC IN (
        'Certificate Number' AS CERTIFICATE_NUMBER,
        'DIF_Category' AS DIF_CATEGORY
    )
)
WHERE( B1_ALT_ID LIKE 'DIF2%' OR B1_ALT_ID LIKE 'DIF1%' );
GROUP BY B1_ALT_ID, SERV_PROV_CODE, CERTIFICATE_NUMBER, DIF_CATEGORY, B1_SHORT_NOTES

But I got the error:

ORA-00937: not a single-group group function

I went to the Oracle docs and read the error is caused when using an aggregate function without the GROUP BY clause, but I'm using it. How can I avoid the error and not get multiple rows?

I'm using Oracle version 12.1.

CodePudding user response：

If your first query gives the result as stated then try doing aggregation outside of it:

SELECT 
    B1_ALT_ID, 
    SERV_PROV_CODE, 
    Max(CERTIFICATE_NUMBER) "CERTIFICATE_NUMBER", 
    Max(DIF_CATEGORY) "DIF_CATEGORY", 
    B1_SHORT_NOTES, 
    REC_DATE
FROM
    (
        SELECT B1_ALT_ID, SERV_PROV_CODE, CERTIFICATE_NUMBER, DIF_CATEGORY, B1_SHORT_NOTES, REC_DATE
        FROM ACCELA.B1PERMIT A
        JOIN BCHCKBOX B
        ON A.B1_PER_ID1 = B.B1_PER_ID1
        AND A.B1_PER_ID3 = B.B1_PER_ID3
        AND( B.B1_CHECKBOX_DESC = 'Certificate Number' OR B.B1_CHECKBOX_DESC = 'DIF_Category' )
        JOIN BPERMIT_DETAIL C
        ON C.B1_PER_ID1 = B.B1_PER_ID1
        AND C.B1_PER_ID3 = B.B1_PER_ID3
        PIVOT(
            MAX( B.B1_CHECKLIST_COMMENT )
            FOR B1_CHECKBOX_DESC IN (
                'Certificate Number' AS CERTIFICATE_NUMBER,
                'DIF_Category' AS DIF_CATEGORY
            )
        )
        WHERE( B1_ALT_ID LIKE 'DIF2%' OR B1_ALT_ID LIKE 'DIF1%' )
    )
GROUP BY
    B1_ALT_ID, 
    SERV_PROV_CODE, 
    B1_SHORT_NOTES, 
    REC_DATE

CodePudding user response：

I ended up answering my own question. It turns out the error was caused because I left the semicolon after the WHERE cause, which is why Oracle was complaining I didn't have a GROUP BY clause. My fixed query:

SELECT B1_ALT_ID, SERV_PROV_CODE, MAX( CERTIFICATE_NUMBER ), MAX( DIF_CATEGORY ), B1_SHORT_NOTES, REC_DATE
FROM ACCELA.B1PERMIT A
JOIN BCHCKBOX B
ON A.B1_PER_ID1 = B.B1_PER_ID1
AND A.B1_PER_ID3 = B.B1_PER_ID3
AND( B.B1_CHECKBOX_DESC = 'Certificate Number' OR B.B1_CHECKBOX_DESC = 'DIF_Category' )
JOIN BPERMIT_DETAIL C
ON C.B1_PER_ID1 = B.B1_PER_ID1
AND C.B1_PER_ID3 = B.B1_PER_ID3
PIVOT(
    MAX( B.B1_CHECKLIST_COMMENT )
    FOR B1_CHECKBOX_DESC IN (
        'Certificate Number' AS CERTIFICATE_NUMBER,
        'DIF_Category' AS DIF_CATEGORY
    )
)
WHERE( B1_ALT_ID LIKE 'DIF2%' OR B1_ALT_ID LIKE 'DIF1%' )
GROUP BY B1_ALT_ID, SERV_PROV_CODE, B1_SHORT_NOTES, REC_DATE;