1 Reply Latest reply: Jul 2, 2013 10:10 AM by DavidGreenfield RSS

    Retrieving column unavailable in oracle table

    user2062772

      Hi,

      I am facing an issue in the module wherein when processing and loading data from source table to target table if a source column is unavailable in the source ttable, then a default value of '0' needs to be inserted to the target table.

       

      Please could anyone provide me what keyword OR function to be used to validate this in a Minus query?

       

      Eg:

      Source table: Select empname, empid, deptid from Src_roll;

       

      Target table: Select empname, empid, deptid, deptname from Stg_roll;

       

      Query:

      select Srcp.empname, Srcp.empid, Srcp.deptid, NVL(Srcp.deptname,0) from Src_roll Srcp

      MINUS

      select empname, empid, deptid, deptname from Stg_roll Stgp;

       

      Output:
      The result should include the column deptname as the fourth column if there is no column "deptname" in the source table. But the result is ORA-00904: "Srcp"."deptname": Invalid identifier.

      please could someone help me if there is any other way to overcome this problem? Many thanks

      jaj