Results 1 to 2 of 2
  1. #1
    Join Date
    Mar 2016
    Posts
    1

    Unanswered: invalid identifier

    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

  2. #2
    Join Date
    Sep 2016
    Location
    Pune
    Posts
    16
    I tried to write the following inner join query using an Oracle database:

    SELECT Employee.EMPLID as EmpID, Employee.FIRST_NAME AS Name,
    Team.DEPARTMENT_CODE AS TeamID, Team.Department_Name AS teamname
    FROM PS_TBL_EMPLOYEE_DETAILS Employee
    INNER JOIN PS_TBL_DEPARTMENT_DETAILS Team
    ON Team.DEPARTMENT_CODE = Employee.DEPTID
    That gives the below error:

    INNER JOIN PS_TBL_DEPARTMENT_DETAILS Team ON Team.DEPARTMENT_CODE = Employee.DEPTID
    *
    ERROR at line 4:
    ORA-00904: "TEAM"."DEPARTMENT_CODE": invalid identifier

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •