SQL running on and on and on

[franceshd]

I inherited this program (1200+ report pages). Employees can have multiple positions/jobs(PREMPJOB.JOB_NO) with varying hours per day, days worked per year(PREMPJOB.CONTR_DAYS), percent worked(PREMPJOB.PERC_WKED), salary, etc. The end user says that only the 1st job per employee is being listed. And they want the TOTAL days and percent worked (cannot be > 100%) in the header record. I know summaries have to be in the footer record. So I duped the program and made it a separate subreport to do the summaries. But not it just runs on and on.
I have read something about (1)"RESET QUERY CACHE" but dont know how and "Select SQL-NO-CACHE" but get "An unexpected was found following it. Following is the SQL.
THANKS SO MUCH FOR YOUR HELP!

SELECT DISTINCT HRPERSON.LAST_NAME,HRPERSON.MID_NAME, HRPERSON.FIRST_NAME, HRPERSON.SS_NO, PREMPM.EMPL_NO, PREMPM.TERM_DATE, PREMPM.HIRE_DATE, CCTR.GLCVAL_VAL, CCTR.CCTR_DESC, PREMPJOB.CONTR_BEG_DATE, PREMPJOB.CONTR_END_DATE, PREMPJOB.ANNUAL_SAL, PREMPJOB.HR_STAT_CD, PREMPJOB.TERM_DATE, PREMPJOB.PERC_WKED, PREMPJOB.JOB_NO, PRETYPE.CERT_FLAG_CD, PREMPCPI.ST_JOB_CD, HRSTPCODE.SJ_DESC, PREMPJOB.CONTR_DAYS, PREMPJOB.EMPL_TYPE, PREMPJOB.UOM, PREMPJOB.TERM_CD, PREMPJOB.EMPL_NO, PREMPJOB.JOB_TYPE, GLMACT.GLACCTDISPLAY, prempss.sal_sch_cd, prempss.rank_cd, prempss.yrs_exper, (((prsalsch.st_based_sal * (1.0 + prejss.prorate_st_factor)) + prempss.suppl_amt) * (coalesce (prsalsch2.st_based_perc, 1))) as CALCSAL, prempjob.perc_wked

FROM ((((((CENOFF.PREMPJOB PREMPJOB INNER JOIN CENOFF.PREMPM PREMPM ON PREMPJOB.EMPL_NO=PREMPM.EMPL_NO) left outer join cenoff.prejss prejss on prempjob.empl_no = prejss.empl_no and prempjob.job_no = prejss.job_no and sal_sch_perc <> 0 left outer join cenoff.prempss prempss on prempss.empl_no = prejss.empl_no and prempss.sal_sch_tag = prejss.sal_sch_tag left outer join cenoff.prsalsch prsalsch on prempss.sal_sch_cd = prsalsch.sal_sch_cd and prempss.rank_cd = prsalsch.rank_cd and prempss.yrs_exper >= prsalsch.yrs_exper and prempss.yrs_exper <= prsalsch.thru_yrs_exper left outer join cenoff.prsalsch prsalsch2 on prempss.perc_sal_sch_cd = prsalsch2.sal_sch_cd and prempss.perc_sal_rank = prsalsch2.rank_cd and prempss.perc_yrs_exper >= prsalsch2.yrs_exper and prempss.perc_yrs_exper <= prsalsch2.thru_yrs_exper INNER JOIN (CENOFF.GLMACT GLMACT INNER JOIN CENOFF.PREJGL PREJGL ON GLMACT.GLACCTASN=PREJGL.GLACCTASN) ON (PREMPJOB.JOB_NO=PREJGL.JOB_NO) AND (PREMPJOB.EMPL_NO=PREJGL.EMPL_NO)) INNER JOIN CENOFF.HRPERSON HRPERSON ON PREMPM.PERS_ID=HRPERSON.PERS_ID) INNER JOIN CENOFF.CCTR CCTR ON PREMPM.GLCVAL_VAL=CCTR.GLCVAL_VAL) INNER JOIN CENOFF.PREMPCPI PREMPCPI ON PREMPM.EMPL_NO=PREMPCPI.EMPL_NO) INNER JOIN CENOFF.PRETYPE PRETYPE ON HRPERSON.CERT_FLAG_CD=PRETYPE.CERT_FLAG_CD) LEFT OUTER JOIN CENOFF.HRSTPCODE HRSTPCODE ON PREMPCPI.ST_JOB_CD=HRSTPCODE.ST_JOB_CD

WHERE
(PREMPJOB.HR_STAT_CD='A' OR (PREMPJOB.HR_STAT_CD='T' AND PREMPJOB.TERM_DATE>'2008-06-30' AND PREMPJOB.TERM_DATE<'2009-07-01') OR (PREMPJOB.HR_STAT_CD='T' AND PREMPJOB.TERM_DATE>'2008-06-30' AND PREMPJOB.TERM_DATE<'2009-07-01' AND PREMPJOB.TERM_CD='LVE')) AND PRETYPE.CERT_FLAG_CD='C' AND PREMPJOB.UOM='D' AND (PREMPJOB.JOB_NO <=12) AND PREMPJOB.ANNUAL_SAL > 0

 

[lbass]

Summaries do not have to be in the footer section, unless you have to use running totals or variables for some reason. What happens if you right click on the field you want summarized and insert a summary at the group and/or the report level? Are the values correct? If so, you can just drag them into the corresponding header sections. If not, you probably do need a subreport linked on the group field.

-LB

 

[franceshd]

Thanks, lbass. No the amount was wrong (doubled in fact). I sent it all to our software company. After 3 hours, the original Crystal guy said he was stumped and was sending it on to someone with more experience. No word back yet. My boss finally said to just put it in the footer instead of the header after all, that the user would just have to get over having the data slightly rearranged. (You know, the old ong-and-dance routine, "But it's ALWAYS been printed that way!") THANKS AGAIN for all your help these months! Have a GREAT afternoon!
F