Oracle Pivot with 3 Tables

111 Views Asked by At

I am using a PIVOT to combine two rows with the same ID into a single row:

SELECT B1_ALT_ID, B1_CHECKLIST_1, B1_CHECKLIST_2
FROM 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' )
PIVOT(
    MAX( B.B1_CHECKLIST_COMMENT )
    FOR B1_CHECKBOX_DESC IN (
        'Certificate Number' AS B1_CHECKLIST_1,
        'DIF_Category' AS B1_CHECKLIST_2
    )
)
WHERE( B1_ALT_ID LIKE 'DIF2%' OR B1_ALT_ID LIKE 'DIF1%' );

It works great.

However, I need to get data from a THIRD table, called BPERMIT_DETAIL, from a column called C.B1_SHORT_NOTES but I get an error:

ORA-00904: "C"."B1_SHORT_NOTES": invalid identifier

This is the query I'm using:

SELECT B1_ALT_ID, B1_CHECKLIST_1, B1_CHECKLIST_2, C.B1_SHORT_NOTES
FROM 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 B1_CHECKLIST_1,
        'DIF_Category' AS B1_CHECKLIST_2
    )
)
WHERE( B1_ALT_ID LIKE 'DIF2%' OR B1_ALT_ID LIKE 'DIF1%' );

Why am I getting "invalid identifier"? If I remove C.B1_SHORT_NOTES from the SELECT clause, it works, but I really need to get the data from that column.

I'm using Oracle version 12.1.

0

There are 0 best solutions below