[sqr-users] RE: Problem in SQL

["White, Denise" <DEWhite@vicr.com>]

Steve,

Actually, in version 8.8 SP1 (and possibly earlier versions of 8), you do need 
to check the PER_STATUS.  I agree that in 7.5 and earlier, it was very 
convenient that non-employees did not have EMPLOYMENT or JOB rows, and most 
selects included one or both of those tables, so it filtered out the 
non-employees automatically.  However, now non-employees do in fact have rows 
in those tables.  As part of the upgrade we are now doing (7.51 to 8.8 SP1), 
the instructors from the Administer Training module are being converted to 
non-employees.  We had to provide a jobcode and department to use for them.  
Therefore, we must now code to exclude these non-employees.  Further 
complications arise from the fact that the NAMES table has the potential for 
multiple names per employee (or non-employee), that the ADDRESSES table has the 
potential for multiple addresses, and PERSONAL_PHONE can contain multiple phone 
numbers, where in the past we could use PERSONAL_DATA which had one row per 
employee to get this information.  There are many new tables and views that can 
be used to limit the data.  PERS_DTA_EMP_VW will only return employees, but 
only has EMPLID and NAME on it.  PERSON_NAME returns only primary names, but 
could include non-employees.  PERSON_ADDR_HOM will return only home addresses, 
but could include non-employees.  PERSON_PHONE will return only home phone, but 
could include non-employees.  In general, if you only want employees, you must 
join with PERS_DTA_EMP_VW, which returns only employees, or join with one of 
the tables/views that contain PER_STATUS (PERSON, PERS_DATA_EFFDT, 
PERSONAL_DT_FST) and then do an explicit condition to check for a value of E.  
It is a pain!

Denise M. White
Sr. Software Engineer
HR/Payroll Systems
Vicor Corporation
------------------------------

Message: 4
Date: Thu, 14 Oct 2004 09:30:37 -0700
From: "Alexander, Steve" <Steven.Alexander@sanjoseca.gov>
Subject: RE: [sqr-users] Problem in SQL
To: "'This list is for discussion about the SQR database
        reportinglangu age      from Hyperion Solutions.'"<sqr-users@sqrug.org>
Message-ID:
        <62AFC819CCF3D611827A00065B3F798106BA8AD1@sj-exchange.ci.sj.ca.u s>
Content-Type: text/plain;       charset=iso-8859-1

Do you really need to test person.per_status = 'E' (employee)?  If the
person has an employment row and job rows, couldn't you assume that he or
she is an employee?  You could eliminate the join with ps_person.  If you
must keep it, add a person.emplid = a.emplid.

Another small improvement might be to replace the c.full_part_time != 'D'
with c.full_part_time in ('F','P', ... ).  This helps if full_part_time is
indexed (but it probably isn't).


-----Original Message-----
From: Deepu [mailto:pradeepkanneganti@yahoo.com]
Sent: Wednesday, October 13, 2004 6:38 AM
To: peoplesoft fans
Subject: [sqr-users] Problem in SQL


Hi All,
 
I'm having a problem with this sql..which is retrieving around 3 million
rows. 
 
SELECT 
a.emplid, a.NAME, pers1.national_id, b.hire_dt, b.rehire_dt,
b.termination_dt, c.deptid, c.full_part_time, c.action, c.action_reason,
c.action_dt, c.effdt, c.reg_temp, c.empl_status, c.empl_rcd
FROM 
ps_names a, ps_pers_nid pers1, ps_employment b, ps_job c, ps_person person
WHERE 
person.per_status = 'E' AND a.emplid = c.emplid
AND a.emplid = pers1.emplid
AND pers1.country = 'USA'
AND pers1.national_id_type = 'PR'
AND b.emplid = c.emplid
AND ( c.effdt =
 (SELECT MAX (effdt), FROM ps_job h
  WHERE h.emplid = c.emplid AND h.empl_rcd = c.empl_rcd   AND h.effdt <=
'30-JUN-2001')
    AND c.effseq = (SELECT MAX (effseq) FROM ps_job i
  WHERE i.emplid = c.emplid AND i.empl_rcd = c.empl_rcd
  AND i.effdt = c.effdt))
AND b.barg_unit = '9' AND c.full_part_time != 'D'
AND a.name_type = 'PRI' 
AND a.effdt = (SELECT MAX (effdt) FROM ps_names a1
  WHERE a.emplid = a1.emplid AND a.name_type =   a1.name_type)
ORDER BY C.Reg_Temp,C.Empl_Status,A.Name 

I was trying to tune this sql...can any one pls suggest me on how to do
it...any help is highly appreciated.
 
Thanks,



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