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.
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: