[Date Prev][Date Next][Thread Prev][Thread Next]
[Author Index]
[Date Index]
[Thread Index]
[SQR-USERS Info]
[SQRUG Home Page]
RE: [sqr-users] File Help
Good question (perhaps not for this group, though).
Setids are a way to group business units which are a way to group departments
(I think. At least that's how we use them.) So, for example, since we are a
university, business units generally correspond to campuses but, at the system
level, there are some BUs which are not campuses that we want to group as a
single reporting entity.
Richard Knapp
Database Programmer/Analyst
Institutional Research and Planning
University of Missouri System
573-882-8856
knappr@umsystem.edu
-----Original Message-----
From: m rapson [mailto:mrapson@hotmail.com]
Sent: Thursday, July 31, 2003 4:47 PM
To: sqr-users@sqrug.org
Subject: Re: [sqr-users] File Help
Can I ask you a dumb question, what are set id's within PS? I am still
learning!
Thanks,
Melissa
----Original Message Follows----
From: "Vishali Chandramouli" <vichandr@syr.edu>
Reply-To: sqr-users@sqrug.org
To: <sqr-users@sqrug.org>
Subject: Re: [sqr-users] File Help
Date: Thu, 31 Jul 2003 12:14:32 -0400
Hi,
I guess the problem is that you have not taken care of effective dating.
Also, you need to match all keys.
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
! My changes - Begin
AND JOB.EFFDT = (SELECT MAX(JOB_1.EFFDT) FROM PS_JOB JOB_1
WHERE JOB_1.EMPLID = JOB.EMPLID
AND JOB_1.EMPL_RCD# = JOB.EMPL_RCD#
AND JOB_1.EFFDT <= $AsOfToday)
!$AsOfToday is got from some sqc
AND JOB.EFFSEQ = (SELECT MAX(JOB_1.EFFSEQ) FROM PS_JOB JOB_1
WHERE JOB_1.EMPLID = JOB.EMPLID
AND JOB_1.EMPL_RCD# = JOB.EMPL_RCD#
AND JOB_1.EFFDT = JOB.EFFDT)
AND LOC.EFFDT = (SELECT MAX(LOC_1.EFFDT) FROM PS_LOCATION_TBL LOC_1
WHERE LOC_1. LOCATION = LOC.LOCATION
AND LOC_1.SETID = LOC.SETID
AND LOC_1.EFFDT <= $AsOfToday)
AND DEPT.EFFDT = (SELECT MAX(DEPT_1.EFFDT) FROM PS_DEPT_TBL DEPT_1
WHERE DEPT_1. DEPTID = DEPT.DEPTID
AND DEPT_1.SETID = DEPT.SETID
AND DEPT_1.EFFDT <= $AsOfToday)
AND JOB.SETID_DEPT = DEPT.SETID
AND JOB.SETID_LOCATION = LOC.SETID
! Also take care of taking most current position data for table - POSITION
_DATA and matching all keys
! My changes - End
Do the same for fetching terminated employees also.
HTH,
Vishali
>>> mrapson@hotmail.com 07/31/03 11:50AM >>>
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
_______________________________________________
sqr-users mailing list
sqr-users@sqrug.org
http://www.sqrug.org/mailman/listinfo/sqr-users
_________________________________________________________________
MSN 8 with e-mail virus protection service: 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
_______________________________________________
sqr-users mailing list
sqr-users@sqrug.org
http://www.sqrug.org/mailman/listinfo/sqr-users