'Oracle Pivot with 3 Tables
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.
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
Solution | Source |
---|