How do I write DB2 SQL to return all employee records with proper WHERE clause?

I have the below DB2 SQL that I am trying to retrun all 200,000 employees including those that have a History record pay changes:

SELECT
A.HM_DIST_CO AS "Dist Company"
,VARCHAR(A.HM_ACCT_UNIT) AS "Dist Acct Unit"
,VARCHAR(A.HM_ACCOUNT) AS "Dist Account"
,A.EMP_STATUS AS "Status"
,A.TERM_DATE AS "Term Date"
,Z.COMPANY AS "HRH Company"
,VARCHAR(Z.EMPLOYEE) AS "HRH Employee"
,Z.FLD_NBR AS "HRH Fld Nbr"
,Z.N_VALUE AS "HRH N Value"
,Z.DATE_STAMP AS "HRH Date Stamp"
,Z.ACT_OBJ_ID AS "HRH Act Obj ID"
,C.HM_PHONE_NBR AS "Phone"
,C.SEX AS "Gender"
,C.BIRTHDATE AS "Birth Date"

FROM
DBHREMP A

JOIN DBHRPEM C
ON A.COMPANY = C.COMPANY
AND A.EMPLOYEE = C.EMPLOYEE

JOIN DBHRHRH Z
ON A.COMPANY = Z.COMPANY
AND A.EMPLOYEE = Z.EMPLOYEE

WHERE Z.FLD_NBR =730
AND Z.DATE_STAMP = (SELECT MAX(Y.DATE_STAMP) FROM DBHRHRH Y
WHERE Y.EMPLOYEE = Z.EMPLOYEE)

ORDER BY A.EMPLOYEE

The issue with the above SQL is that with the  Z.DATE_STAMP = (SELECT MAX(Y.DATE_STAMP) FROM DBHRHRH Y WHERE Y.EMPLOYEE = Z.EMPLOYEE) statement only 7,100 records are returned.

How do I structure the DB2 SQL retruning records of those with pay changes and those that don't have pay reocrd changes to include all 200,000 employees?

garyjgsAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Kent OlsenDBACommented:
Hi Gary,

I don't understand your data, but I suspect that it lies with the fact that you're doing inner joins, when you need outer joins.

FROM
DBHREMP A

JOIN DBHRPEM C
ON A.COMPANY = C.COMPANY
AND A.EMPLOYEE = C.EMPLOYEE

JOIN DBHRHRH Z
ON A.COMPANY = Z.COMPANY
AND A.EMPLOYEE = Z.EMPLOYEE


In the three tables joined here, which has the "master list" of all employees?  That should probably be the table that the other two are "outer joined" to.


Kent
momi_sabagCommented:
what happens if you remove  Z.DATE_STAMP = (SELECT MAX(Y.DATE_STAMP) FROM DBHRHRH Y WHERE Y.EMPLOYEE = Z.EMPLOYEE) ?
garyjgsAuthor Commented:
DBHREMP A is the mater table which maintains 200,000 employees.  When doing an OUTER JOIN DBHRHRH Z, I receive an error: Column qualifier or table Z undefined.


Exploring ASP.NET Core: Fundamentals

Learn to build web apps and services, IoT apps, and mobile backends by covering the fundamentals of ASP.NET Core and  exploring the core foundations for app libraries.

garyjgsAuthor Commented:
what happens if you remove  Z.DATE_STAMP = (SELECT MAX(Y.DATE_STAMP) FROM DBHRHRH Y WHERE Y.EMPLOYEE = Z.EMPLOYEE) ?  -  When this statement is removed, 300,000 plus records are returned since an employee may have more than one pay history record.  I am trying to return the most recent pay history record.
momi_sabagCommented:
try

SELECT
A.HM_DIST_CO AS "Dist Company"
,VARCHAR(A.HM_ACCT_UNIT) AS "Dist Acct Unit"
,VARCHAR(A.HM_ACCOUNT) AS "Dist Account"
,A.EMP_STATUS AS "Status"
,A.TERM_DATE AS "Term Date"
,Z.COMPANY AS "HRH Company"
,VARCHAR(Z.EMPLOYEE) AS "HRH Employee"
,Z.FLD_NBR AS "HRH Fld Nbr"
,Z.N_VALUE AS "HRH N Value"
,Z.DATE_STAMP AS "HRH Date Stamp"
,Z.ACT_OBJ_ID AS "HRH Act Obj ID"
,C.HM_PHONE_NBR AS "Phone"
,C.SEX AS "Gender"
,C.BIRTHDATE AS "Birth Date"

FROM
DBHREMP A

JOIN DBHRPEM C
ON A.COMPANY = C.COMPANY
AND A.EMPLOYEE = C.EMPLOYEE

left outer JOIN DBHRHRH Z
ON A.COMPANY = Z.COMPANY
AND A.EMPLOYEE = Z.EMPLOYEE

WHERE Z.FLD_NBR =730
AND Z.DATE_STAMP = (SELECT MAX(Y.DATE_STAMP) FROM DBHRHRH Y
WHERE A.EMPLOYEE = Z.EMPLOYEE)

ORDER BY A.EMPLOYEE
Kent OlsenDBACommented:
Changing the join like this:

FROM
DBHREMP A

OUTER JOIN DBHRPEM C
ON A.COMPANY = C.COMPANY
AND A.EMPLOYEE = C.EMPLOYEE

OUTER JOIN DBHRHRH Z
ON A.COMPANY = Z.COMPANY
AND A.EMPLOYEE = Z.EMPLOYEE

should get you the entire employee list.  It might get you too much, but we can pare that down once you get the full list.


kent

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
garyjgsAuthor Commented:
Using the syntax:

lleft outer JOIN DBHRHRH Z
ON A.COMPANY = Z.COMPANY
AND A.EMPLOYEE = Z.EMPLOYEE

the query hangs.  I am unable to return records.
Kent OlsenDBACommented:
I suspect that the query is just taking a long time.

How many rows in each of the tables?  
garyjgsAuthor Commented:
The primary table DBHREMP A has 212,000 records.

The history table DBHRHRH Z has probably 600,000 records (or maybe more).


Kent OlsenDBACommented:
Ok.

In the primary table, can there be duplicates with regard to fields COMPANY and EMPLOYEE?  That is:

  SELECT * FROM DBHREMP where COMPANY = x and EMPLOYEE = y

Will that produce exactly 1 row at most?


garyjgsAuthor Commented:
In the primary table, there are 2 companies, comapany 100 and company 125.
In the primary table, there is only one employee.  An employee cannot be entered more than once.

For example, select * from DBHREMP where company = 100 and employee =1 returns only one record.

(Note:  Using the syntax:

lleft outer JOIN DBHRHRH Z
ON A.COMPANY = Z.COMPANY
AND A.EMPLOYEE = Z.EMPLOYEE

I have left the query running over 2 hours and no records have returned as of yet)
Kent OlsenDBACommented:
Hi Gary,

That's not that big a join, suggesting that there is a cartesian product being generated.  i.e. many millions of rows.

Can you post the query that you're running this time?  Maybe another set of eyes will help.


Kent
garyjgsAuthor Commented:
After working with the DBA, it was identifiied that there are over 2 million history records.  The query that I was trying to write was going to be used in a Crystal Report.  Having heard the volume of history records another approach is going to have to be identified within the context of the erp software or a view is going to have to be created as not to disrupt the database.
Kent OlsenDBACommented:
A traditional view won't make this any faster.  In fact, depending on usage, it could make it worse.

A materialized view may be the best solution.  Check with you DBA.


Kent
garyjgsAuthor Commented:
This would be the best solution if the number of records were not as they are.  The follow up was also very helpful.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
DB2

From novice to tech pro — start learning today.