Hey, I'm having an issue where a complex select is returning a 02063 (it has an 'invalid identifier' on a column that clearly exists)
I would post the query but it's pretty large and joins 2 views. One of the views selects through a dblink, so it's imbedded pretty far in. I cannot isolate parts of the query that are causing the issue because of this.
What I wanted to know is the best way to write the link or the view to avoid these parser issues. Here's the complete error message I get as well as the script for the link:
ORA-00904: "A"."BUYING_MEMBER_ID": invalid identifier
ORA-02063: preceding line from WEBAPP6
create public database link "WEBAPP6.REGRESS.RDBMS.DEV.US.ORACLE.COM"
connect to ABC_WEBAPP
identified by "<pwd>"
using 'abcdb';
And the script for the view which uses the link:
SELECT a.ID member_id, a.NAME member_nm, a.url member_url, a.address_1,
a.address_2, a.city, a.fda_license, a.is_abc_board_member,
a.is_gsabc_board_member, a.is_abc_foundation_board_member,
c.short_name state_id, d.code zip_cd, e.code county_cd, a.phone_main,
a.phone_alt, a.fax, b.financial_ctr_id, b.benchmarking_ctr_id,
b.piag_ctr_id
FROM MEMBER@webapp6 a LEFT OUTER JOIN lu_member_dw b ON a.ID = b.member_id
JOIN state@webapp6 c ON a.state_id = c.ID
JOIN zip@webapp6 d ON a.zip_id = d.ID
JOIN county@webapp6 e ON a.county_id = e.ID
UNION
SELECT member_id, member_nm, member_url, ' ', ' ', ' ', ' ', ' ', ' ', ' ',
' ', ' ', ' ', ' ', ' ', ' ', financial_ctr_id, benchmarking_ctr_id,
piag_ctr_id
FROM lu_member_dw
WHERE member_id NOT IN (SELECT ID
FROM MEMBER@webapp6)