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
Your question does not appear to be related to Oracle OLAP, but if you want to copy only new rows (based on empname, empid, deptid) from SRC_ROLL into STG_ROLL, then can't you just use the following?
select Srcp.empname, Srcp.empid, Srcp.deptid, 0 deptname from Src_roll Srcp
select empname, empid, deptid, 0 deptname from Stg_roll Stgp;