[sqr-users] pulls all records now but ignores DO GET_EMPLID_COMPANY_JOB

[Lisa Kelsey <lkelsey@alamo-group.com>]

Okay, thanks for your responses.  I've made the changes suggested, and I
get all 11 records now, which is awesome!  I guess I don't understand
how my procedures need to be inside the BEGIN-SELECT.  The only one
working is the GET_PERSONAL_DATA.  I've included the revised version of
this sqr below.  Can anyone further enlighten me?  I'm not a programmer,
I just went to the SQR class that ADP offers so I am new to this. Thanks
very much!!!

! NEW HIRE FILE WRITE TO A TEXT FILE
#INCLUDE 'SETENV.SQC' !THIS DETERMINES MY OPERATING SYSTEM AND DATABASE.

BEGIN-PROGRAM !THIS IS WHERE THE PROGRAM BEGINS EXECUTING.

  Let $Program = 'newhire'
  let $full_name = $Program || '.txt'
  open $full_name as 1
    for-writing
    record=801:FIXED
   DO MAIN
   DO GET_EMPLID_COMPANY_JOB
   DO GET_PERSONAL_DATA
   DO PLACE_EMPLOYEE_DATA_IN_FILE
CLOSE 1
END-PROGRAM
BEGIN-PROCEDURE MAIN
!PROMPT USER TO ENTER BEGIN AND END DATE
    INPUT $BEGIN_DT 'ENTER BEGIN DATE' TYPE=DATE
    INPUT $END_DT 'ENTER END DATE' TYPE=DATE !MM-DD-YYYY

BEGIN-SELECT
EMPLID
   LET $EMPLOYMENT.EMPLID=&EMPLID
  DO GET_EMPLID_COMPANY_JOB
  DO GET_PERSONAL_DATA
  DO PLACE_EMPLOYEE_DATA_IN_FILE
HIRE_DT
FROM PS_EMPLOYMENT WHERE HIRE_DT >=$BEGIN_DT AND HIRE_DT <=$END_DT
END-SELECT
END-PROCEDURE MAIN
BEGIN-PROCEDURE GET_PERSONAL_DATA
BEGIN-SELECT
C.EMPLID
SSN
FIRST_NAME
LAST_NAME
STREET1
STREET2
CITY
STATE
ZIP
FROM PS_PERSONAL_DATA C WHERE EMPLID=$EMPLOYMENT.EMPLID
END-SELECT
END-PROCEDURE GET_PERSONAL_DATA

BEGIN-PROCEDURE GET_EMPLID_COMPANY_JOB
BEGIN-SELECT
A.EMPLID
A.EFFDT
A.COMPANY
FROM PS_JOB A WHERE EMPLID=$EMPLOYMENT.EMPLID AND
A.EFFDT = (SELECT MAX (EFFDT) FROM PS_JOB B
WHERE A.EMPLID=B.EMPLID AND
B.EFFDT<GETDATE())
END-SELECT

BEGIN-SELECT
COMPANY
FEDERAL_EIN
DESCR
STREET1 &CO.STREET1
CITY &CO.CITY
STATE &CO.STATE
ZIP &CO.ZIP
FROM PS_COMPANY_TBL WHERE COMPANY=&COMPANY
END-SELECT
END-PROCEDURE GET_EMPLID_COMPANY_JOB

BEGIN-PROCEDURE PLACE_EMPLOYEE_DATA_IN_FILE
     LET $W4='W4'
WRITE 1 FROM $W4:2  &SSN:9  &FIRST_NAME:16 &LAST_NAME:30 &STREET1:40
&STREET2:40 &CITY:25 &STATE:2 &ZIP:5 &FEDERAL_EIN:9  &DESCR:45
&CO.STREET1:40 &CO.CITY:25 &CO.STATE:2 &CO.ZIP:5
END-PROCEDURE PLACE_EMPLOYEE_DATA_IN_FILE

Lisa Kelsey
HR Generalist
Alamo Group
830-372-9689




_______________________________________________
sqr-users mailing list
sqr-users@sqrug.org
http://www.sqrug.org/mailman/listinfo/sqr-users