I have two tables that I need to compare to make sure the values match. For context, one is the employee's time sheet and the other is a table to holds their requests. Each has a time sheet code and a number of hours. My plan was to compare by summary to see if they match. However, I am having problems getting my logic to work perfectly every time. Without me posting my code (it is becoming a mess quickly anyway), how would you approach this? The comparison needs to be able to be run programmatically and in the end return a true/false.
This can be an RPG solution or a SQL solution.
This is what I need to make sure is true.
Table 1
02 1.5
04 16.0
Table 2
02 1.5
04 16.0
The problem is when
Table 1
02 1.5
Table 2
02 1.5
04 16.0
or when
Table 1
02 1.5
04 16.0
Table 2
02 1.5
or more so when
Table 1
02 1.5
04 16.0
Table 2
I put my two SQL results into views then based on many peoples feedback already came up with this. I think this could be created into it's own stored procedure.
SELECT SUM ( ERROR_COUNT ) AS TOTAL_ERRORS INTO NUM_ERRORS FROM (
SELECT COUNT ( * ) AS ERROR_COUNT
FROM MPRLIB . V_TSHOURSUMM A EXCEPTION JOIN MPRLIB . V_REQHOURSUMM B
ON A . EM_NUMBER = B . EM_NUMBER AND A . TIMESHEET_CODE = B . TIMESHEET_CODE AND A.HOURS_SUMMARY = B . HOURS_SUMMARY
WHERE A . EM_NUMBER = EMPLOYEE_ID OR B . EM_NUMBER = EMPLOYEE_ID
UNION
SELECT COUNT ( * ) AS ERROR_COUNT
FROM MPRLIB . V_REQHOURSUMM A EXCEPTION JOIN MPRLIB . V_TSHOURSUMM B
ON A . EM_NUMBER = B . EM_NUMBER AND A . TIMESHEET_CODE = B . TIMESHEET_CODE AND A . HOURS_SUMMARY = B . HOURS_SUMMARY
WHERE A . EM_NUMBER = EMPLOYEE_ID OR B . EM_NUMBER = EMPLOYEE_ID ) TABLE
It seems to work, but seems... excessive. Thoughts? Is there a better way?