Editing SQL Query with Add Command

[mrichey]

I'm trying to edit the below sql query in CR 9 to select only the MAX of "TIV_AGG"."W_PCENTPROCTIME" for each server (TIV_AGG"."SERVER") on the selected date.

------------------------------------------
SELECT "TIV_AGG"."W_PCENTPROCTIME", "TIV_AGG"."S_DATE", "TIV_AGG"."SERVER", "TIV_AGG"."S_TIME"
FROM "TIVOLI"."TIV_AGG" "TIV_AGG"
WHERE "TIV_AGG"."S_DATE"='20040621'
---------------------------------------------

I'm attempting this by using the database expert ADD command. I'm copying and pasting the original query into the ADD command and then trying to edit it to something like below:

SELECT MAX ("TIV_AGG"."W_PCENTPROCTIME"), "TIV_AGG"."S_DATE", "TIV_AGG"."SERVER", "TIV_AGG"."S_TIME"
FROM "TIVOLI"."TIV_AGG" "TIV_AGG"
WHERE "TIV_AGG"."S_DATE"='20040621'
ORDER BY "TIV_AGG"."SERVER"

I get an Oracle error "not a single group function". I'm not a programmer, as I'm certain is evident from the above attempt. : ) Can anyone help? Thanks!!

 

[Turkbear]

Hi,
Instead of

SELECT MAX ("TIV_AGG"."W_PCENTPROCTIME"), "TIV_AGG"."S_DATE", "TIV_AGG"."SERVER", "TIV_AGG"."S_TIME"
 FROM   "TIVOLI"."TIV_AGG" "TIV_AGG"
 WHERE  "TIV_AGG"."S_DATE"='20040621'
 ORDER BY "TIV_AGG"."SERVER"

Use ( to meet Oracle systax rules ):

SELECT "TIV_AGG"."S_DATE", "TIV_AGG"."SERVER", "TIV_AGG"."S_TIME",MAX ("TIV_AGG"."W_PCENTPROCTIME")
 FROM   "TIVOLI"."TIV_AGG" "TIV_AGG"
 WHERE  "TIV_AGG"."S_DATE"='20040621'
 GROUP BY "TIV_AGG"."S_DATE", "TIV_AGG"."SERVER",
 "TIV_AGG"."S_TIME"

In Oracle Sql you need to 'tell' it how to Group the Max value..

 

[mrichey]

That's great!!

It accepted that formula. Now my question is, when I go to database/show sql query. I still see only the original query. I used the Add command for the new one, which incorporates most of what's in the original with the additional request for bringing in the MAX of cpu. Can I edit the original query instead of using the add command?

My purpose in all this is to get the max coming in so I don't have to use formulas against the summary later, which can't be charted. Am I making any sense?

Really appreciate your help on this.