[Date Prev][Date Next][Thread Prev][Thread Next]
[Author Index]
[Date Index]
[Thread Index]
[SQR-USERS Info]
[SQRUG Home Page]
Re: [sqr-users] pulls all records now but ignores DO GET_EMPLID_COMPANY_JOB
You also may want to check out some of the delivered SQRs from PeopleSoft.
(I presume that you are running PeopleSoft here, judging by your table
names... :-) ) You can find good examples of almost everything in the
delivered SQRs. You also may want to watch your effective date logic;
remember that lots of tables have effective dates and effective sequences.
I'm not sure what language you are coming from, but SQR handles the Selects
quite differently than most languages do; instead of just getting the
information, it is also a control structure. Instead of thinking about
the BEGIN-SELECT ... END-SELECT as being a static piece of SQL, consider it
more like a loop statement, that executes for every row that the SQL
returns. It's generally a good idea to put code between the column list
and the FROM clause of the SQL, with the SQL being the only code in column
one, like:
BEGIN-SELECT
T1.COL1
T1.COL2
! Begin SQR Code
Move &T1.Col1 to $Col1
Move &T1.Col2 to $Col2
Do Something
Do Something-Else
! End SQR Code
FROM Table_name T1
WHERE T1.Col1 = $Value
END-SELECT
The code fragment (I've commented it !Begin SQR Code and !End SQR Code)
will execute once for each row that the SQL statement brings back. For
example, if Table_name has fifteen rows on it, the code segment will run 15
times, each time with a different row of data. It will call Something and
Something-Else fifteen times, and you can reference the values of $Col1 and
$Col2 in each of those calls. (You can also reference &T1.COL1 and
&T1.COL2, but this leads to some very unreadable code. Have pity on the
person who will be maintaining your code, for it may be you...) If the
procedure Something say, gathered more information from the system and
Something-Else wrote the information to the file, you would end up with 15
rows of information out to your file.
After reading through a couple of delivered SQRs, you may also want to look
at the language reference. For PS SQR users, it's located at
$PS_HOME\SQRBinW\DOC\SQR\langref.pdf. I'd be willing to bet that most
versions of SQR would probably have that DOC directory too.
Welcome to the wonderful world of SQR Programming!
Allen Thornton
Lisa Kelsey
<lkelsey@alamo-g To: SQR Group
<sqr-users@sqrug.org>
roup.com> cc:
Sent by: Subject: [sqr-users] pulls all
records now but ignores DO GET_EMPLID_COMPANY_JOB
sqr-users-admin@
sqrug.org
08/09/2003 03:25
PM
Please respond
to sqr-users
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
_______________________________________________
sqr-users mailing list
sqr-users@sqrug.org
http://www.sqrug.org/mailman/listinfo/sqr-users