RE: [sqr-users] Program pulls only 1 record

["Jon Alsager" <Jalsager@ISIF.STATE.ID.US>]

Also, I believe you may have a typo in GET_EMPLID_COMPANY_JOB.

WHERE EMPLID=$EMPLOYMENT_EMPLID   should be
WHERE EMPLID=$EMPLOYMENT.EMPLID   

Jon

-----Original Message-----
From: Lisa Kelsey [mailto:lkelsey@alamo-group.com] 
Sent: Friday, August 08, 2003 3:24 PM
To: sqr-users@sqrug.org
Subject: [sqr-users] Program pulls only 1 record

Help, I am trying to use this to pull all new hires in a given month.
For the month of July, I know I have 11 records, but when I run this
sqr, it only returns 1 record.  Also, appears to ignore the procedure
called GET_EMPLID_COMPANY_JOB.  Anyone have any ideas on what the
problem is?  Thanks for your help!
Lisa

! 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
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


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


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