get the correct function value

pardeshirahul
pardeshirahul used Ask the Experts™
on
I have 2 tables  xxeis_Cost_function
it has the columns

employee_name, cost_center, payroll_group function  


and table2 xxeis_job_function
has column

cost_center, payroll_group, function

I have to find the employee
for the payroll_group, cost_center in second table,  dosent have the correct function value in the first table


table 2 has the correct fiunction value

and display the correct function_value and the employee name and wrong function_value

my query

select a.employee_full_name, substr(a.payroll_name,1,3) payroll_group, a.employee_number , a.pj#job_function
, regexp_substr(organization_name, '[0-9.]+') cost_center from xxeis_cost_function a
           where not exists( select null from xxeis_job_function b
                where substr(a.payroll_name,1,3)= b.payroll_name
                 and  regexp_substr(a.organization_name, '[0-9.]+')=b.cost_center
                 and a.pj#job_function=b.function )
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Author

Commented:
the main trick here is to

display

employee_name , wrong function_value  , correct_function_value
Hi pardeshirahul,

How do you know (identify) which rows have the wrong values?  And which row has the correct values?

It would seem that the join criteria would involve the columns with potentially wrong data so joining the rows does seem like a reliable process.

Will the rows with the wrong data join to the wrong row?  Or perhaps not join to anything?

Author

Commented:
Hi,
Thanks,
Rahul


Table 1 has column function_value now we have to check this function value is correct from the table 2 for column payroll , cost_center
Should you be charging more for IT Services?

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
everything in table 2 is correct payroll_group, cost_center, function
Ok.

Something like this then :

SELECT employee_name, c.cost_function, j.cost_function
FROM xxeis_Cost_Function  C
LEFT JOIN xxeis_.job_function j
  ON c.cost_center = j.cost_center
 AND c.payroll_group = j.payroll_group
WHERE c.cost_function <> j.cost_function

Open in new window

awking00Information Technology Specialist

Commented:
select c.employee_name, d.function as wrong_function, j.function as correct_function
from
(select cost_center, payroll_group from xxeis_cost_function
 minus
 select cost_center, payroll_group from xxeis_job_function) d
inner join xxeis_cost_function c
on d. cost_center = c.cost_center and d.payroll_group = c.payroll_group
inner join xxeis_job_function j
on d. cost_center = j.cost_center and d.payroll_group = j.payroll_group;
awking00Information Technology Specialist
Commented:
Oops, I left out the important part -
select c.employee_name, d.function as wrong_function, j.function as correct_function
 from
 (select cost_center, payroll_group, function from xxeis_cost_function
  minus
  select cost_center, payroll_group, function from xxeis_job_function) d
 inner join xxeis_cost_function c
 on d. cost_center = c.cost_center and d.payroll_group = c.payroll_group
 inner join xxeis_job_function j
 on d. cost_center = j.cost_center and d.payroll_group = j.payroll_group;

Author

Commented:
the query is not working I cjhecked for 1 employee the function value is 40 but the query is showing as null
awking00Information Technology Specialist

Commented:
Can you post the relevant data from the two tables for that employee?

Author

Commented:
There are some employees that currently have nothing for their function in their employee assignment. These should be on the report as well, since they are incorrect.

Author

Commented:
the second sql worked but I want it to be written in simple format

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial