Using MAX function in HAVING of SQL

[warburp]

Hi

I'm trying to create an sql to report on 2 tables where I have a max run time permitted for a job and the run history of a job. I want to identify all those jobs where the MAX of the run history is within 90% of the max run time permitted. Can anyone help me how I can do this, the SQL is below and works within Oracle (I'm more familiar with) but not on iSeries SQL. The OS is version 5 release 2.

SELECT job.JOBNAM, job.PROGDS, job.SCHOVR,
job.MAXRUN, job.RTMXRT Action_CD,
INT((max(hist.CMRTIM)/job.MAXRUN)*100)as MAXRUN_P,
min(hist.CMRTIM) MIN, max(hist.CMRTIM)as MAX,
INT(avg(hist.CMRTIM)) AVG,
count(hist.CMRNAM)No_RECS
FROM table1 job, table2 hist
where hist.CMRNAM=job.JOBNAM
AND job.JOBTYP <> 'G'
group by JOBNAM, job.PROGDS, job.SCHOVR, job.MAXRUN, job.RTMXRT
having INT((max(hist.CMRTIM)/job.MAXRUN)*100)>90

Thanks

Phil.

 

[blom0344]

Does this work: (in-line view approach)

Select * from
(SELECT job.JOBNAM, job.PROGDS, job.SCHOVR,
job.MAXRUN, job.RTMXRT Action_CD,
INT((max(hist.CMRTIM)/job.MAXRUN)*100)as MAXRUN_P,
min(hist.CMRTIM) MIN, max(hist.CMRTIM)as MAX,
INT(avg(hist.CMRTIM)) AVG,
count(hist.CMRNAM)No_RECS
FROM table1 job, table2 hist
where hist.CMRNAM=job.JOBNAM
AND job.JOBTYP <> 'G'
group by JOBNAM, job.PROGDS, job.SCHOVR, job.MAXRUN, job.RTMXRT) temp
where temp.MAXRUN_P > 90

Ties Blom
Information analyst
tbl@shimano-eu.com

 

[warburp]

Ties

I get the error 'Selection error involving field CMRTIM' when running your sql. Any other ideas?

Thank

Phil.

 

[blom0344]

Slightly modified on alias side:

Select X,Y,Z,XX,YY,MAXRUN_P,MAX,AVG,No_RECS from
(SELECT job.JOBNAM as X,
 job.PROGDS AS Y,
 job.SCHOVR AS Z,                     
 job.MAXRUN AS XX,
 job.RTMXRT AS YY,                              
INT((max(hist.CMRTIM)/job.MAXRUN)*100)as MAXRUN_P,             
min(hist.CMRTIM) MIN, max(hist.CMRTIM)as MAX,                  
INT(avg(hist.CMRTIM)) as AVG,                                     
count(hist.CMRNAM)as No_RECS                                      
FROM table1 job, table2 hist                                   
where hist.CMRNAM=job.JOBNAM                                   
AND job.JOBTYP <> 'G'                                          
group by JOBNAM, job.PROGDS, job.SCHOVR, job.MAXRUN, job.RTMXRT) temp
where temp.MAXRUN_P > 90

Ties Blom
Information analyst
tbl@shimano-eu.com

 

[blom0344]

Phil,

Did it resolve your problem?

Ties Blom
Information analyst
tbl@shimano-eu.com

 

[warburp]

Ties

Afraid not, still got the error:

Selection error involving field CMRTIM.

I can't belive this is so difficult on OS400!

Thanks

Phil.

 

[blom0344]

Hmm,

I just tried a similar exercise within ORACLE, but it is not allowed there either. The division itself seems to be the problem. Are you sure this is working for you in ORACLE?

Ties Blom
Information analyst
tbl@shimano-eu.com