RE: [sqr-users] Problem in SQL

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

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,
 

__________________________________________________
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around 
http://mail.yahoo.com 

_______________________________________________
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