Here is my current SQL Query that I am thinking I want ot rebuild using a Command Object. Right now, I am getting Duplicate records because of the newly added table REVIEW. (We applied a patch for Program PA26 - which transitioned the NextReview Date from PAREVIEW to REVIEW:


SELECT "EMPLOYEE"."EMPLOYEE", "EMPLOYEE"."LAST_NAME", "EMPLOYEE"."FIRST_NAME", "EMPLOYEE"."MIDDLE_INIT", "PAEMPLOYEE"."HM_PHONE_NBR", "PAEMPLOYEE"."BIRTHDATE", "EMPLOYEE"."FICA_NBR", "EMPLOYEE"."ADDR1", "EMPLOYEE"."ADDR2", "EMPLOYEE"."CITY", "EMPLOYEE"."STATE", "EMPLOYEE"."ZIP", "EMPLOYEE"."ADJ_HIRE_DATE", "EMPLOYEE"."EXEMPT_EMP", "EMPLOYEE"."DATE_HIRED", "PAEMPPOS"."EFFECT_DATE", "PAEMPPOS"."DEPARTMENT", "PAEMPPOS"."JOB_CODE", "JOBCODE"."DESCRIPTION", "PAEMPPOS"."PAY_GRADE", "PAEMPPOS"."PAY_STEP", "PAEMPPOS"."PAY_RATE", "EMPLOYEE"."EMP_STATUS", "PAEMPPOS"."SCHEDULE", "PAEMPLOYEE"."SEX", "PAEMPPOS"."FTE", "PAEMPPOS"."EMPLOYEE", "PAEMPPOS"."POSITION", "PAEMPPOS"."END_DATE", "EMPLOYEE"."STAND_HOURS", "EMPLOYEE"."TERM_DATE", "EMPLOYEE"."PROCESS_LEVEL", "EMPLOYEE"."DEPARTMENT", "REVIEW"."NEXT_REVIEW", "REVIEW"."NEXT_REV_CODE"
FROM "LAWSON"."EMPLOYEE" "EMPLOYEE", "LAWSON"."PAEMPLOYEE" "PAEMPLOYEE", "LAWSON"."PAEMPPOS" "PAEMPPOS", "LAWSON"."REVIEW" "REVIEW", "LAWSON"."JOBCODE" "JOBCODE"
WHERE (("EMPLOYEE"."COMPANY"="PAEMPLOYEE"."COMPANY" (+)) AND ("EMPLOYEE"."EMPLOYEE"="PAEMPLOYEE"."EMPLOYEE" (+))) AND (("EMPLOYEE"."COMPANY"="PAEMPPOS"."COMPANY" (+)) AND ("EMPLOYEE"."EMPLOYEE"="PAEMPPOS"."EMPLOYEE" (+))) AND (("EMPLOYEE"."COMPANY"="REVIEW"."COMPANY") AND ("EMPLOYEE"."EMPLOYEE"="REVIEW"."EMPLOYEE")) AND (("PAEMPPOS"."COMPANY"="JOBCODE"."COMPANY") AND ("PAEMPPOS"."JOB_CODE"="JOBCODE"."JOB_CODE")) AND "EMPLOYEE"."EMPLOYEE"=359802
ORDER BY "EMPLOYEE"."DEPARTMENT"

Any ideas? :afro:

any particular reason youre using Left Outer Joins?

I'm not seeing NEXT_REVIEW or NEXT_REV_CODE in the REVIEW table. I do see them in PAEMPLOYEE however...

CJMART: Adter you apply CTP 45859 and run Program PA702 they will be there.

this report was built with alot of unique things - subreports, detail section A, detail Section B, etc...so, I am trying not to go away from the table linking and what-not (Command Object a no go for now)

So, what am I missing here? Why do I keep getting duplicate PAEMPPOS records?

I recently did a sort descending by REVIEW.NEXT_REVIEW and it looked like it worked, but sure enough, when I go to the next page the PAEMPPOS records begin to duplicate.

Is it my linking? Here are my links:

WHERE (("EMPLOYEE"."EMPLOYEE"="PAEMPPOS"."EMPLOYEE" (+)) AND ("EMPLOYEE"."COMPANY"="PAEMPPOS"."COMPANY" (+))) AND (("EMPLOYEE"."EMPLOYEE"="REVIEW"."EMPLOYEE") AND ("EMPLOYEE"."COMPANY"="REVIEW"."COMPANY")) AND (("PAEMPPOS"."COMPANY"="JOBCODE"."COMPANY") AND ("PAEMPPOS"."JOB_CODE"="JOBCODE"."JOB_CODE"))

HEELLLPPP :banghead: :banghead: :banghead: :blink: :blink: :brr:

Here is a visual of the table links - for those of us that are visually stimulated!

Does you selection criteria include Effective Dates/End Dates on the REVIEW and the PAEMPPOS tables? Those tables have multiple records per employee, so if you don't use some kind of date selection you could get duplicate records.

And/or, if you have employees with more than one position (levels 2-5), then you will get one PAEMPPOS record for each position level and you would see multiple lines for an employee in your query.

Hey Karen -

So I get you on the PAEMPPOS, but the Effective date suggestion. How would I state this in the Selection Criteria? I want to see all the Effective Date changes and/or Updates...so not sure how to get that?

FYI: My Next_Review date (REVIEW table) is in the Details A section, and works fine - It's when I get down to the Details B section that stores the PAEMPPOS changes for the employee.