RE: [sqr-users] RE: Problem in SQL

["Alexander, Steve" <Steven.Alexander@sanjoseca.gov>]

Denise,

Thanks for the overview.  I'm at version 8 SP1.  It's good to know what's
coming in my next upgrade.  I hope my other suggestions helped.

Steven

-----Original Message-----
From: White, Denise [mailto:DEWhite@vicr.com]
Sent: Friday, October 15, 2004 7:46 AM
To: SQR Users Group (E-mail)
Subject: [sqr-users] RE: Problem in SQL


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

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