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

["Alexander, Steve" <Steve.Alexander@ci.sj.ca.us>]

I agree with Rajib, except that the DO commands should be indented so the
begin-select paragraph doesn't interpret them as column names.  Also, you
were calling the other procedures for only the last row - not "only the
first one.."

-----Original Message-----
From: Sengupta, Rajib (CAG-CC-Contractor)
[mailto:Rajib.Sengupta@conagrafoods.com]
Sent: Friday, August 08, 2003 2:32 PM
To: sqr-users@sqrug.org
Subject: RE: [sqr-users] Program pulls only 1 record


oops..you have to put all your procedures inside the begin select, otherwise
it will bring only the first one..
try any SQR given with your HRMS module ..
the stucture should be like this ..i haven't checked all the details and
your sql....

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

Rajib Sengupta
Systems Analyst and Team Lead
Peoplesoft Financial Consultant
Conagra Business Systems
Phone: 402-595-5749



-----Original Message-----
From: Lisa Kelsey [mailto:lkelsey@alamo-group.com]
Sent: Friday, August 08, 2003 4: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

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