rawEmpDS = load '/user/akhanolk/joinProject/data/employees_active/part-e' using PigStorage(',') as (empNo:chararray,dOB:chararray,lName:chararray,fName:chararray,gender:chararray,hireDate:chararray,deptNo:chararray);

empDS = foreach rawEmpDS generate empNo,fName,lName,gender,deptNo;

sortedEmpDS = ORDER empDS by empNo;

rawSalDS = load '/user/akhanolk/joinProject/data/salaries_history/part-sh' using PigStorage(',') as (empNo:chararray,salary:long,fromDate:chararray,toDate:chararray);

filteredSalDS = filter rawSalDS by toDate == '9999-01-01';

salDS = foreach filteredSalDS generate empNo, salary;

sortedSalDS = ORDER salDS by empNo;

joinedDS = join sortedEmpDS by empNo, sortedSalDS by empNo using 'merge';

finalDS = foreach joinedDS generate sortedEmpDS::empNo,sortedEmpDS::fName,sortedEmpDS::lName,sortedEmpDS::gender,sortedEmpDS::deptNo,sortedSalDS::salary;

store finalDS into '/user/akhanolk/joinProject/output/pig-RSJ';