[Date Prev][Date Next][Thread Prev][Thread Next]
[Author Index]
[Date Index]
[Thread Index]
[SQR-USERS Info]
[SQRUG Home Page]
[sqr-users] File Help
Hello All! This is the first SQR I have written. I am outputting to a file
employee information. The sqr has 2 procedures, one pulls information about
active employees and the second pulls information about terminated
employees. The file should only contain around 1200 records but is
returning about 40000 or so. Not really sure where to look so I thought I
would post this and see if someone can help.
Much appreciated
Mel
#include 'setenv.sqc' !Set environment
#Include 'setup07.sqc' !Printer and page-size initialization
begin-REPORT
do Init-DateTime
do Get-Current-DateTime
do Stdapi-Init
display 'Building Interface File for Employee Website'
do Open-file
do MAIN
do close
do Commit-Transaction
display 'Finished Building Interface File for Employee Website'
end-REPORT
!************************************************************************
begin-Procedure Open-file
LET $OUTFILE = 'c:\ep_empwebsite.csv'
open $OUTFILE as 1 for-writing record = 200
let $csv = ','
end-procedure
!************************************************************************
begin-procedure close
close 1
end-procedure
!************************************************************************
begin-PROCEDURE MAIN
begin-SELECT distinct
EMP.EMPLID
JOB.EMPL_STATUS
NAM.LAST_NAME
NAM.FIRST_NAME
NAM.PREF_FIRST_NAME
JOB.DEPTID
DEPT.DESCR
JOBJR.SUPV_LVL_ID
JOB.GL_PAY_TYPE
EMP.BUSINESS_TITLE
LOC.ADDRESS1
LOC.ADDRESS2
LOC.CITY
LOC.STATE
LOC.POSTAL
LOC.COUNTRY
PHN.PHONE
POS.REG_TEMP
POS.FULL_PART_TIME
NAM2.NAME
LET $GL_PAY_TYPE = SUBSTR(&JOB.GL_PAY_TYPE, 0, 1)
IF &POS.REG_TEMP = 'T'
let $EMPL_STATUS = 'Temp'
ELSE
IF &POS.FULL_PART_TIME = 'F'
let $EMPL_STATUS = 'FT'
ELSE
let $EMPL_STATUS = 'PT'
END-IF
END-IF
IF &JOB.EMPL_STATUS = 'T'
let $TERM_FLAG = 'yes'
ELSE
let $TERM_FLAG = ' '
END-IF
LET $EMPLID = &EMP.EMPLID
LET $LASTNAME = &NAM.LAST_NAME
LET $FIRSTNAME = &NAM.FIRST_NAME
LET $PREFNAME = &NAM.PREF_FIRST_NAME
LET $DEPTID = substr(&JOB.DEPTID, 2, 3)
LET $WORKAT = &JOBJR.SUPV_LVL_ID
LET $DIVISION = substr(&JOB.GL_PAY_TYPE, 1, 1)
EVALUATE $DIVISION
WHEN = '1'
LET $DIVISIONDESCR = 'Local Sales'
WHEN = '2'
LET $DIVISIONDESCR = 'Sales Administration Support'
WHEN = '3'
LET $DIVISIONDESCR = 'Duncan'
WHEN = '4'
LET $DIVISIONDESCR = 'Colorado Springs'
WHEN = '5'
LET $DIVISIONDESCR = 'Partnership Marketing'
WHEN = '6'
LET $DIVISIONDESCR = 'Headquarters'
WHEN = '7'
LET $DIVISIONDESCR = 'Sally Foster'
WHEN-OTHER
BREAK
END-EVALUATE
LET $TITLE = &EMP.BUSINESS_TITLE
LET $ADDRESS1 = &LOC.ADDRESS1
LET $ADDRESS2 =&LOC.ADDRESS2
LET $CITY = &LOC.CITY
LET $STATE = &LOC.STATE
LET $ZIPCODE = &LOC.POSTAL
LET $COUNTRY = &LOC.COUNTRY
LET $PHONE = &PHN.PHONE
LET $REGTEMP = &POS.REG_TEMP
LET $FULLPART = &POS.FULL_PART_TIME
LET $SUPERVISOR = &NAM2.NAME
DO WRITE-FILE
DO TERMINATE
FROM SYSADM.PS_LOCATION_TBL LOC,
SYSADM.PS_DEPT_TBL DEPT,
SYSADM.PS_PERSONAL_PHONE PHN,
SYSADM.PS_POSITION_DATA POS2,
SYSADM.PS_POSITION_DATA POS,
SYSADM.PS_NAMES NAM2,
SYSADM.PS_NAMES NAM,
SYSADM.PS_JOB JOB2,
SYSADM.PS_JOB_JR JOBJR,
SYSADM.PS_JOB JOB,
SYSADM.PS_EMPLOYMENT EMP
WHERE EMP.EMPLID = JOB.EMPLID
AND JOB.EMPL_STATUS in ('A', 'L', 'T', 'S')
AND JOBJR.EMPLID = JOB.EMPLID
AND POS.POSITION_NBR = JOB.POSITION_NBR
AND POS2.POSITION_NBR = JOB.REPORTS_TO
AND JOB2.POSITION_NBR = POS2.POSITION_NBR
AND NAM.EMPLID = EMP.EMPLID
AND NAM.NAME_TYPE = 'PRI'
AND NAM2.Emplid = JOB2.EMPLID
AND NAM2.NAME_TYPE = 'PRI'
AND PHN.EMPLID(+) = EMP.EMPLID
AND PHN.PHONE_TYPE(+) = 'BUSN'
AND LOC.LOCATION(+) = JOB.LOCATION
AND DEPT.DEPTID = JOB.DEPTID
end-SELECT
end-PROCEDURE
! ****** This will pull terminated employees
begin-PROCEDURE TERMINATE
begin-SELECT distinct
TEMP.EMPLID
TJOB.EMPL_STATUS
TNAM.LAST_NAME
TNAM.FIRST_NAME
TNAM.PREF_FIRST_NAME
TJOB.DEPTID
TDEPT.DESCR
TJOBJR.SUPV_LVL_ID
TJOB.GL_PAY_TYPE
TEMP.BUSINESS_TITLE
TLOC.ADDRESS1
TLOC.ADDRESS2
TLOC.CITY
TLOC.STATE
TLOC.POSTAL
TLOC.COUNTRY
TPHN.PHONE
TPOS.REG_TEMP
TPOS.FULL_PART_TIME
TNAM2.NAME
LET $GL_PAY_TYPE = SUBSTR(&TJOB.GL_PAY_TYPE, 0, 1)
LET $EMPL_STATUS = ''
IF &TJOB.EMPL_STATUS = 'T'
let $TERM_FLAG = 'yes'
ELSE
let $TERM_FLAG = ' '
END-IF
LET $EMPLID = &TEMP.EMPLID
LET $LASTNAME = &TNAM.LAST_NAME
LET $FIRSTNAME = &TNAM.FIRST_NAME
LET $PREFNAME = &TNAM.PREF_FIRST_NAME
LET $DEPTID = substr(&TJOB.DEPTID, 2, 3)
LET $WORKAT = &TJOBJR.SUPV_LVL_ID
LET $DIVISION = substr(&TJOB.GL_PAY_TYPE, 1, 1)
EVALUATE $DIVISION
WHEN = '1'
LET $DIVISIONDESCR = 'Local Sales'
WHEN = '2'
LET $DIVISIONDESCR = 'Sales Administration Support'
WHEN = '3'
LET $DIVISIONDESCR = 'Duncan'
WHEN = '4'
LET $DIVISIONDESCR = 'Colorado Springs'
WHEN = '5'
LET $DIVISIONDESCR = 'Partnership Marketing'
WHEN = '6'
LET $DIVISIONDESCR = 'Headquarters'
WHEN = '7'
LET $DIVISIONDESCR = 'Sally Foster'
WHEN-OTHER
BREAK
END-EVALUATE
LET $TITLE = &TEMP.BUSINESS_TITLE
LET $ADDRESS1 = &TLOC.ADDRESS1
LET $ADDRESS2 =&TLOC.ADDRESS2
LET $CITY = &TLOC.CITY
LET $STATE = &TLOC.STATE
LET $ZIPCODE = &TLOC.POSTAL
LET $COUNTRY = &TLOC.COUNTRY
LET $PHONE = &TPHN.PHONE
LET $REGTEMP = &TPOS.REG_TEMP
LET $FULLPART = &TPOS.FULL_PART_TIME
LET $SUPERVISOR = &TNAM2.NAME
DO WRITE-FILE
FROM SYSADM.PS_LOCATION_TBL TLOC,
SYSADM.PS_DEPT_TBL TDEPT,
SYSADM.PS_PERSONAL_PHONE TPHN,
SYSADM.PS_POSITION_DATA TPOS2,
SYSADM.PS_POSITION_DATA TPOS,
SYSADM.PS_NAMES TNAM2,
SYSADM.PS_NAMES TNAM,
SYSADM.PS_JOB TJOB2,
SYSADM.PS_JOB_JR TJOBJR,
SYSADM.PS_JOB TJOB,
SYSADM.PS_EMPLOYMENT TEMP
WHERE TEMP.EMPLID = TJOB.EMPLID
AND TJOB.EMPL_STATUS in ('T', 'S')
AND TNAM.EMPLID = TEMP.EMPLID
AND TNAM.NAME_TYPE = 'PRI'
AND TJOBJR.EMPLID(+) = TJOB.EMPLID
AND TPOS.POSITION_NBR(+) = TJOB.POSITION_NBR
AND TPOS2.POSITION_NBR(+) = TJOB.REPORTS_TO
AND TJOB2.POSITION_NBR(+) = TPOS2.POSITION_NBR
AND TNAM2.Emplid(+) = TJOB2.EMPLID
AND TNAM2.NAME_TYPE(+) = 'PRI'
AND TPHN.EMPLID(+) = TEMP.EMPLID
AND TPHN.PHONE_TYPE(+) = 'BUSN'
AND TLOC.LOCATION(+) = TJOB.LOCATION
AND TDEPT.DEPTID(+) = TJOB.DEPTID
end-SELECT
end-PROCEDURE
!**********************************************************************
begin-Procedure Write-File
write 1 from
$TERM_FLAG
$csv
$EMPLID
$csv
$LASTNAME
$csv
$FIRSTNAME
$csv
$PREFNAME
$csv
$EMPL_STATUS
$csv
$DEPTID
$csv
$WORKAT
$csv
$GL_PAY_TYPE
$csv
$DIVISIONDESCR
$csv
$TITLE
$csv
$ADDRESS1
$csv
$ADDRESS2
$csv
$CITY
$csv
$STATE
$csv
$ZIPCODE
$csv
$COUNTRY
$csv
$PHONE
$csv
$SUPERVISOR
end-PROCEDURE
!************************************************************************
#include 'askaod.sqc' !Ask As Of Date input
#Include 'datetime.sqc' !Routine for Date and Time formatting
#Include 'curdttim.sqc' !Get-Current-DateTime procedure
#Include 'ldorgsb.sqc' !Load Orgcode Symbols procedure
#Include 'getorgsb.sqc' !Get Orgcode Symbol procedure
#Include 'tranctrl.sqc' !Common Transaction Handling
#Include 'sqrtrans.sqc' !sql error procedure
#include 'prcsapi.sqc'
#include 'prcsdef.sqc'
#include 'stdapi.sqc' !Routine to Update Run Status
_________________________________________________________________
MSN 8 helps eliminate e-mail viruses. Get 2 months FREE*.
http://join.msn.com/?page=features/virus
_______________________________________________
sqr-users mailing list
sqr-users@sqrug.org
http://www.sqrug.org/mailman/listinfo/sqr-users