Hello,
we have 2 systems, both oracle 12c. one, that I call ban, that holds the students records and the other, I call dw and uses the db link dwlnk, which pulls a lot of information from ban. it shows students their degree progress. Sum the credits from both systems and find those students where they are not equal. I have written a query that works and gives me the results desired. this is just annoying me. as I tried to write it in newer style sql. I brought it down to the minimum that one can do and continue to get the error -- invalid identifier. since I copied and pasted from one query to the second one it is valid. so what is the issue? am I just doing something really stupid? or running into issue with 2 different systems - but then why

ban.shrtgpa_pidm = rtrim(rad_primary_mst.rad_user_def1)
rtrim(rad_primary_mst.rad_user_def1) is defined as CHAR(12)
ban.shrtgpa_pidm is defined as number(8)


thank you for any assistance you can provide.

WORKING QUERY
-- sum_crds_diff_dw_ban
--
-- sum up the credits on DW
-- sum up the credits on Banner
-- find students where the credits are not equal
--
--
select rad_student_mst.rad_id rad_bid,
rtrim(rad_primary_mst.rad_user_def1) rad_pidm,
rtrim(rad_primary_mst.rad_name) rad_name,
rad_student_mst.rad_term rad_term,
rad_goal_dtl.rad_school rad_school,
rad_goal_dtl.rad_degree_code rad_degree_code,

(select nvl(sum(dap_resclass_dtl.dap_aud_credits),0) as dap_aud_crd
from schema_buc.dap_resclass_dtl@dwlnk dap_resclass_dtl
where
rad_student_mst.rad_id = dap_resclass_dtl.dap_stu_id
and dap_resclass_dtl.dap_audit_type = 'AA'
and substr(dap_resclass_dtl.dap_active_term,1,6) =
substr(rad_student_mst.rad_term,1,6)
and dap_resclass_dtl.dap_stu_id = rad_goal_dtl.rad_id
and dap_resclass_dtl.dap_degree = rad_goal_dtl.rad_degree_code
and dap_resclass_dtl.dap_transfer_type ='AC'
and dap_resclass_dtl.dap_term < rad_student_mst.rad_term )
AS sum_dw,

(select nvl(sum(shrtgpa_hours_earned),0) as shrtgpa_hrs_earned
from shrtgpa
where shrtgpa_pidm = rtrim(rad_primary_mst.rad_user_def1)
and shrtgpa_gpa_type_ind='I'
and shrtgpa_term_code != '000000' )
AS sum_ban

from
schema_buc.rad_student_mst@dwlnk rad_student_mst,
schema_buc.rad_primary_mst@dwlnk rad_primary_mst,
schema_buc.rad_goal_dtl@dwlnk rad_goal_dtl
where

(select nvl(sum(dap_resclass_dtl.dap_aud_credits),0) as dap_aud_crd
from schema_buc.dap_resclass_dtl@dwlnk dap_resclass_dtl
where
rad_student_mst.rad_id = dap_resclass_dtl.dap_stu_id
and dap_resclass_dtl.dap_audit_type = 'AA'
and substr(dap_resclass_dtl.dap_active_term,1,6) =
substr(rad_student_mst.rad_term,1,6)
and dap_resclass_dtl.dap_stu_id = rad_goal_dtl.rad_id
and dap_resclass_dtl.dap_degree = rad_goal_dtl.rad_degree_code
and dap_resclass_dtl.dap_transfer_type ='AC'
and dap_resclass_dtl.dap_term < rad_student_mst.rad_term )
!=
(select nvl(sum(shrtgpa_hours_earned),0) as shrtgpa_hrs_earned
from shrtgpa
where shrtgpa_pidm = rtrim(rad_primary_mst.rad_user_def1)
and shrtgpa_gpa_type_ind='I'
and shrtgpa_term_code != '000000' )

and rad_student_mst.rad_term = '201610'
and rad_student_mst.rad_id = rad_primary_mst.rad_id
and rad_student_mst.rad_id = rad_goal_dtl.rad_id
order by rad_student_mst.rad_id


THIS IS WHERE I TRIED TO REWRITE IT -- I just used the one sum to begin with.
In this it complains about rad_primary_mst as invalid identifier.
if I swap the order rtrim(rad_primary_mst.rad_user_def1) = ban.shrtgpa_pidm
then it complains about ban.shrtgpa_pidm

select rad_student_mst.rad_id rad_BID,
rtrim(rad_primary_mst.rad_user_def1) rad_pidm,
rtrim(rad_primary_mst.rad_name) rad_name,
rad_student_mst.rad_term rad_term,
rad_goal_dtl.rad_school rad_school,
rad_goal_dtl.rad_degree_code rad_degree_code,
ban.shrtgpa_hrs_earned

from
schema_buc.rad_student_mst@dwlnk rad_student_mst,
schema_buc.rad_primary_mst@dwlnk rad_primary_mst,
schema_buc.rad_goal_dtl@dwlnk rad_goal_dtl

INNER JOIN
(select nvl(sum(shrtgpa_hours_earned),0) as shrtgpa_hrs_earned
from shrtgpa
where shrtgpa_gpa_type_ind='I'
and shrtgpa_term_code != '000000'
) ban
ON ban.shrtgpa_pidm = rtrim(rad_primary_mst.rad_user_def1)

WHERE
rad_student_mst.rad_term = '201610'
and rad_student_mst.rad_id = rad_primary_mst.rad_id
and rad_student_mst.rad_id = rad_goal_dtl.rad_id