1 Reply Latest reply on Jul 2, 2013 3:10 PM by DavidGreenfield

    Retrieving column unavailable in oracle table



      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?



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


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



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


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


      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