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;
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