Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

From keywork not found where expected

Status
Not open for further replies.
PappaG

PappaG

Technical User
Nov 21, 2003
288
0
0
GB
ok guys this is a biggie but can't see the error here can anyone spot it. Error is as per header from keywork not found where expected

SELECT O.OBJECT_NAME,
T.DATE_YYYYMMDD,
V.T_LOGIN,
SUM (CR14R1V.T_BREAK + CR14R1V.T_LUNCH + AUX + V.T_OUTBOUND + V.T_HOLD + CR14R1V.T_ACW + V.T_WAIT + V.T_TALK) AS NOT_READY,
V.T_TALK,
V.T_WAIT,
CR14R1V.T_ACW,
V.T_HOLD,
V.T_OUTBOUND,
SUM (CR14R1V.T_TRAINING + CR14R1V.T_PERSONAL + CR14R1V.T_EMAIL + CR14R1V.T_COACHING + CR14R1V.T_AUTHORISED +
CR14R1V.T_MEETING + CR14R1V.T_ADMIN + CR60V.T_WITHCUSTOMER) AS AUX,
SUM (CR14R1V.T_BREAK + CR14R1V.T_LUNCH) AS BREAK_LUNCH,
(V.T_LOGIN / 60) / 60 AS TOTAL_STAFF_TIME_HR,
((SUM (CR14R1V.T_BREAK + CR14R1V.T_LUNCH + AUX + V.T_OUTBOUND + V.T_HOLD + CR14R1V.T_ACW + V.T_WAIT + V.T_TALK)) / 60) / 60 AS TOTAL_NOT_READY_HR,
(V.T_TALK / 60) / 60 AS TOTAL_TALK_HR,
(V.T_WAIT / 60) / 60 AS TOTAL_WAIT_HR,
(CR14R1V.T_ACW / 60) / 60 AS TOTAL_ACW_HR,
(V.T_HOLD / 60) / 60 AS TOTAL_HOLD_HR,
(V.T_OUTBOUND / 60) / 60 AS TOTAL_OUTBOUND_HR,
((SUM (CR14R1V.T_TRAINING + CR14R1V.T_PERSONAL + CR14R1V.T_EMAIL + CR14R1V.T_COACHING + CR14R1V.T_AUTHORISED +
CR14R1V.T_MEETING + CR14R1V.T_ADMIN + CR60V.T_WITHCUSTOMER)) / 60) / 60 AS TOTAL_AUX_HR,
((SUM (CR14R1V.T_BREAK + CR14R1V.T_LUNCH))/ 60) / 60 AS TOTAL_BREAK_LUNCH_HR,
((V.T_LOGIN / 60) / 60) / 5.4 AS TOTAL_STAFF_TIME_FTE,
(((SUM (CR14R1V.T_BREAK + CR14R1V.T_LUNCH + AUX + V.T_OUTBOUND + V.T_HOLD + CR14R1V.T_ACW + V.T_WAIT + V.T_TALK)) / 60) / 60)) / 5.4 AS TOTAL_NOT_READY_FTE,
((V.T_TALK / 60) / 60) / 5.4 AS TOTAL_TALK_FTE,
((V.T_WAIT / 60) / 60) / 5.4 AS TOTAL_WAIT_FTE,
((CR14R1V.T_ACW / 60) / 60) / 5.4 AS TOTAL_ACW_FTE,
((V.T_HOLD / 60) / 60) / 5.4 AS TOTAL_HOLD_FTE,
((V.T_OUTBOUND / 60) / 60) / 5.4 AS TOTAL_OUTBOUND_FTE,
(((SUM (CR14R1V.T_TRAINING + CR14R1V.T_PERSONAL + CR14R1V.T_EMAIL + CR14R1V.T_COACHING + CR14R1V.T_AUTHORISED +
CR14R1V.T_MEETING + CR14R1V.T_ADMIN + CR60V.T_WITHCUSTOMER)) / 60) / 60) / 5.4 AS TOTAL_AUX_FTE,
(((SUM (CR14R1V.T_BREAK + CR14R1V.T_LUNCH))/ 60) / 60) / 5.4 AS TOTAL_BREAK_LUNCH_FTE,
(V.T_TALK + V.T_WAIT + V.T_HOLD)/(V.T_LOGIN - (CR14R1V.T_BREAK + CR14R1V.T_LUNCH)) AS TOTAL_STAFF_UTIL_PERCENT,
NOT_READY / (V.T_LOGIN - (CR14R1V.T_BREAK + CR14R1V.T_LUNCH)) AS TOTAL_NOT_READY_UTIL_PERCENT,
V.T_TALK / (V.T_LOGIN - (CR14R1V.T_BREAK + CR14R1V.T_LUNCH)) AS TOTAL_TALK_UTIL_PERCENT,
V.T_WAIT / (V.T_LOGIN - (CR14R1V.T_BREAK + CR14R1V.T_LUNCH)) AS TOTAL_WAIT_UTIL_PERCENT,
CR14R1V.T_ACW / (V.T_LOGIN - (CR14R1V.T_BREAK + CR14R1V.T_LUNCH)) AS TOTAL_ACW_UTIL_PERCENT,
V.T_HOLD / (V.T_LOGIN - (CR14R1V.T_BREAK + CR14R1V.T_LUNCH)) AS TOTAL_HOLD_UTIL_PERCENT,
V.T_OUTBOUND / (V.T_LOGIN - (CR14R1V.T_BREAK + CR14R1V.T_LUNCH)) AS TOTAL_OUTBOUND_UTIL_PERCENT,
(SUM (CR14R1V.T_TRAINING + CR14R1V.T_PERSONAL + CR14R1V.T_EMAIL + CR14R1V.T_COACHING + CR14R1V.T_AUTHORISED +
CR14R1V.T_MEETING + CR14R1V.T_ADMIN + CR60V.T_WITHCUSTOMER)) / (V.T_LOGIN - (CR14R1V.T_BREAK + CR14R1V.T_LUNCH)) AS TOTAL_AUX_UTIL_PERCENT,
SUM ((((V.T_TALK / 60) / 60) / 5.4) + (((V.T_WAIT / 60) / 60) / 5.4) + (((V.T_HOLD / 60) / 60) / 5.4)) AS STAFF_TIME_AVAIL_FTE
FROM ((DATAMART01.O_CR60_AG_DAY CR60O INNER JOIN DATAMART01.V_CR60_AG_DAY CR60V ON CR60O.OBJECT_ID = CR60V.OBJECT_ID)
INNER JOIN DATAMART01.T_CR60_AG_DAY CR60T ON CR60V.TIME_KEY = CR60T.TIME_KEY)
INNER JOIN (((DATAMART01.O_GROFAGS_DAY O INNER JOIN DATAMART01.V_GROFAGS_DAY V ON O.OBJECT_ID = V.OBJECT_ID)
INNER JOIN DATAMART01.T_GROFAGS_DAY T ON V.TIME_KEY = T.TIME_KEY)
INNER JOIN ((DATAMART01.O_CR14R1_AG_DAY CR14R1O
INNER JOIN DATAMART01.V_CR14R1_AG_DAY CR14R1V ON CR14R1O.OBJECT_ID = CR14R1V.OBJECT_ID)
INNER JOIN DATAMART01.T_CR14R1_AG_DAY CR14R1T ON CR14R1V.TIME_KEY = CR14R1T.TIME_KEY)
ON (T.TIME_KEY = CR14R1T.TIME_KEY) AND (V.OBJECT_ID = CR14R1V.OBJECT_ID))
ON (CR60T.TIME_KEY = T.TIME_KEY) AND (CR60V.OBJECT_ID = V.OBJECT_ID)
WHERE (((O.OBJECT_NAME)='ALL_AG_SHOP') AND ((T.DATE_YYYYMMDD)='20080203'));
 
Sort by date Sort by votes
Check brackets; my guess is that at some point in your text you have got more closing than opening brackets.
Does the error message give you a line number? If so, count there first!

hope this helps
 
Upvote 0 Downvote
I'd agree with hoinz, I reckon the lin:
Code: 
(((SUM (CR14R1V.T_BREAK + CR14R1V.T_LUNCH + AUX + V.T_OUTBOUND + V.T_HOLD + CR14R1V.T_ACW + V.T_WAIT + V.T_TALK)) / 60) / 60)) / 5.4 AS TOTAL_NOT_READY_FTE,
Will be the/a problem.

Hope this helps

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.
 
Upvote 0 Downvote
Thanks for the quick response got me a little further but i now get a new error. nested group function without group by.....i have tried adding a group by at the end of my query but get "NOT A SINGLE GROUP GROUP FUNCTION"

Code: 
SELECT O.OBJECT_NAME, T.DATE_YYYYMMDD, 
V.T_LOGIN,
SUM (CR14R1V.T_BREAK + CR14R1V.T_LUNCH + (SUM (CR14R1V.T_TRAINING + CR14R1V.T_PERSONAL + CR14R1V.T_EMAIL + CR14R1V.T_COACHING + CR14R1V.T_AUTHORISED + 
CR14R1V.T_MEETING + CR14R1V.T_ADMIN + CR60V.T_WITHCUSTOMER)) + V.T_OUTBOUND + V.T_HOLD + CR14R1V.T_ACW + V.T_WAIT + V.T_TALK) AS NOT_READY,
V.T_TALK, 
V.T_WAIT, CR14R1V.T_ACW,
V.T_HOLD, V.T_OUTBOUND,
SUM (CR14R1V.T_TRAINING + CR14R1V.T_PERSONAL + CR14R1V.T_EMAIL + CR14R1V.T_COACHING + CR14R1V.T_AUTHORISED + 
CR14R1V.T_MEETING + CR14R1V.T_ADMIN + CR60V.T_WITHCUSTOMER) AS AUX, 
SUM (CR14R1V.T_BREAK + CR14R1V.T_LUNCH) AS BREAK_LUNCH, 
(V.T_LOGIN / 60) / 60 AS TOTAL_STAFF_TIME_HR, 
((SUM (CR14R1V.T_BREAK + CR14R1V.T_LUNCH + (SUM (CR14R1V.T_TRAINING + CR14R1V.T_PERSONAL + CR14R1V.T_EMAIL + CR14R1V.T_COACHING + CR14R1V.T_AUTHORISED + 
CR14R1V.T_MEETING + CR14R1V.T_ADMIN + CR60V.T_WITHCUSTOMER)) + V.T_OUTBOUND + V.T_HOLD + CR14R1V.T_ACW + V.T_WAIT + V.T_TALK)) / 60) / 60 AS TOTAL_NOT_READY_HR,
(V.T_TALK / 60) / 60 AS TOTAL_TALK_HR,
(V.T_WAIT / 60) / 60 AS TOTAL_WAIT_HR,
(CR14R1V.T_ACW / 60) / 60 AS TOTAL_ACW_HR,
(V.T_HOLD / 60) / 60 AS TOTAL_HOLD_HR,
(V.T_OUTBOUND / 60) / 60 AS TOTAL_OUTBOUND_HR,
((SUM (CR14R1V.T_TRAINING + CR14R1V.T_PERSONAL + CR14R1V.T_EMAIL + CR14R1V.T_COACHING + CR14R1V.T_AUTHORISED + 
CR14R1V.T_MEETING + CR14R1V.T_ADMIN + CR60V.T_WITHCUSTOMER)) / 60) / 60 AS TOTAL_AUX_HR,
((SUM (CR14R1V.T_BREAK + CR14R1V.T_LUNCH))/ 60) / 60 AS TOTAL_BREAK_LUNCH_HR,
((V.T_LOGIN / 60) / 60) / 5.4 AS TOTAL_STAFF_TIME_FTE,
SUM (CR14R1V.T_BREAK + CR14R1V.T_LUNCH + (SUM (CR14R1V.T_TRAINING + CR14R1V.T_PERSONAL + CR14R1V.T_EMAIL + CR14R1V.T_COACHING + CR14R1V.T_AUTHORISED + 
CR14R1V.T_MEETING + CR14R1V.T_ADMIN + CR60V.T_WITHCUSTOMER)) + V.T_OUTBOUND + V.T_HOLD + CR14R1V.T_ACW + V.T_WAIT + V.T_TALK) / 60 / 60 / 5.4 AS TOTAL_NOT_READY_FTE,
((V.T_TALK / 60) / 60) / 5.4 AS TOTAL_TALK_FTE,
((V.T_WAIT / 60) / 60) / 5.4 AS TOTAL_WAIT_FTE,
((CR14R1V.T_ACW / 60) / 60) / 5.4 AS TOTAL_ACW_FTE,
((V.T_HOLD / 60) / 60) / 5.4 AS TOTAL_HOLD_FTE,
((V.T_OUTBOUND / 60) / 60) / 5.4 AS TOTAL_OUTBOUND_FTE,
(((SUM (CR14R1V.T_TRAINING + CR14R1V.T_PERSONAL + CR14R1V.T_EMAIL + CR14R1V.T_COACHING + CR14R1V.T_AUTHORISED + 
CR14R1V.T_MEETING + CR14R1V.T_ADMIN + CR60V.T_WITHCUSTOMER)) / 60) / 60) / 5.4 AS TOTAL_AUX_FTE,
(((SUM (CR14R1V.T_BREAK + CR14R1V.T_LUNCH))/ 60) / 60) / 5.4 AS TOTAL_BREAK_LUNCH_FTE,
(V.T_TALK + V.T_WAIT + V.T_HOLD)/(V.T_LOGIN - (CR14R1V.T_BREAK + CR14R1V.T_LUNCH)) AS TOTAL_STAFF_UTIL_PERCENT,
(SUM (CR14R1V.T_BREAK + CR14R1V.T_LUNCH + (SUM (CR14R1V.T_TRAINING + CR14R1V.T_PERSONAL + CR14R1V.T_EMAIL + CR14R1V.T_COACHING + CR14R1V.T_AUTHORISED + 
CR14R1V.T_MEETING + CR14R1V.T_ADMIN + CR60V.T_WITHCUSTOMER)) + V.T_OUTBOUND + V.T_HOLD + CR14R1V.T_ACW + V.T_WAIT + V.T_TALK))/ (V.T_LOGIN - (CR14R1V.T_BREAK + CR14R1V.T_LUNCH)) AS TOTAL_NOT_READY_UTIL_PERCENT,
V.T_TALK / (V.T_LOGIN - (CR14R1V.T_BREAK + CR14R1V.T_LUNCH)) AS TOTAL_TALK_UTIL_PERCENT,
V.T_WAIT / (V.T_LOGIN - (CR14R1V.T_BREAK + CR14R1V.T_LUNCH)) AS TOTAL_WAIT_UTIL_PERCENT,
CR14R1V.T_ACW / (V.T_LOGIN - (CR14R1V.T_BREAK + CR14R1V.T_LUNCH)) AS TOTAL_ACW_UTIL_PERCENT,
V.T_HOLD / (V.T_LOGIN - (CR14R1V.T_BREAK + CR14R1V.T_LUNCH)) AS TOTAL_HOLD_UTIL_PERCENT,
V.T_OUTBOUND / (V.T_LOGIN - (CR14R1V.T_BREAK + CR14R1V.T_LUNCH)) AS TOTAL_OUTBOUND_UTIL_PERCENT,
(SUM (CR14R1V.T_TRAINING + CR14R1V.T_PERSONAL + CR14R1V.T_EMAIL + CR14R1V.T_COACHING + CR14R1V.T_AUTHORISED + 
CR14R1V.T_MEETING + CR14R1V.T_ADMIN + CR60V.T_WITHCUSTOMER)) / (V.T_LOGIN - (CR14R1V.T_BREAK + CR14R1V.T_LUNCH)) AS TOTAL_AUX_UTIL_PERCENT,
SUM ((((V.T_TALK / 60) / 60) / 5.4) + (((V.T_WAIT / 60) / 60) / 5.4) + (((V.T_HOLD / 60) / 60) / 5.4)) AS STAFF_TIME_AVAIL_FTE
FROM ((DATAMART01.O_CR60_AG_DAY CR60O INNER JOIN DATAMART01.V_CR60_AG_DAY CR60V ON CR60O.OBJECT_ID = CR60V.OBJECT_ID) 
INNER JOIN DATAMART01.T_CR60_AG_DAY CR60T ON CR60V.TIME_KEY = CR60T.TIME_KEY) 
INNER JOIN (((DATAMART01.O_GROFAGS_DAY O INNER JOIN DATAMART01.V_GROFAGS_DAY V ON O.OBJECT_ID = V.OBJECT_ID) 
INNER JOIN DATAMART01.T_GROFAGS_DAY T ON V.TIME_KEY = T.TIME_KEY) 
INNER JOIN ((DATAMART01.O_CR14R1_AG_DAY CR14R1O 
INNER JOIN DATAMART01.V_CR14R1_AG_DAY CR14R1V ON CR14R1O.OBJECT_ID = CR14R1V.OBJECT_ID) 
INNER JOIN DATAMART01.T_CR14R1_AG_DAY CR14R1T ON CR14R1V.TIME_KEY = CR14R1T.TIME_KEY) 
ON (T.TIME_KEY = CR14R1T.TIME_KEY) AND (V.OBJECT_ID = CR14R1V.OBJECT_ID)) 
ON (CR60T.TIME_KEY = T.TIME_KEY) AND (CR60V.OBJECT_ID = V.OBJECT_ID)
WHERE (((O.OBJECT_NAME)='ALL_AG_SHOP') AND ((T.DATE_YYYYMMDD)='20080203'));
 
Upvote 0 Downvote
You'd have to group by every field that's not aggregated.

Could you show us the group by you tried?

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.
 
Upvote 0 Downvote
tried group by o.bject_name, t.date_yyyymmdd and tried them both individualy but will try what you have suggested
 
Upvote 0 Downvote
tried the following group by but still get "not a single group group function"
GROUP BY O.OBJECT_NAME, T.DATE_YYYYMMDD, V.T_LOGIN, V.T_TALK, V.T_WAIT, CR14R1V.T_ACW, V.T_HOLD, V.T_OUTBOUND;
 
Upvote 0 Downvote
You need to group by every single column that is referenced in the select clause that is not part of an aggregation
 
Upvote 0 Downvote
Thanks jim i thought thats what the above group by showed...as you can probally tell from the query im not an sql user so may have misunderstood your comment can you explain further as all other colums to me appear to be aggregated
 
Upvote 0 Downvote
PappaG,

The aggregated columns are the colomns that use a function (e.g. SUM()). If the column doesn't use a function you'll need to group by it (i.e. you'll need to group by all the columns that are simple division).

Hope this helps

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.
 
Upvote 0 Downvote
There are 11 columns as far as I can see:
O.OBJECT_NAME, T.DATE_YYYYMMDD, V.T_LOGIN. V.T_TALK,V.T_WAIT
,CR14R1V.T_ACW,V.T_HOLD,V.T_OUTBOUND, CR14R1V.T_BREAK, CR14R1V.T_LUNCH,NOT_READY
 
Upvote 0 Downvote
Jim said:
There are 11 columns as far as I can see...
Click to expand...
Jim, I count 9 commas in the fragment you cite, above...That makes Oracle believe there are 10 columns.

[santa]Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
 
Upvote 0 Downvote
LOL,
V.T_LOGIN. V.T_TALK should of course be V.T_LOGIN, V.T_TALK giving 10 commas. Of course I took a fairly cursory glance at the code, hopefully PappaG can confirm
 
Upvote 0 Downvote
Not all the tables are required as i can chop some of the them off but when i did this i got some strange results i couldn't work out. There are 3 sets of three tables each set of three gives me a set of agent group stats but i wanted to combine all three sets so i could query only the one agent group and get all availible stats. I'm going to take this back to scratch and start all over again as this query just seems to complicated to fix any problems.....thanks for all your help anyway at least it gives me a few things to consider in my next attempt :)
 
Upvote 0 Downvote
PappaG. If you can, supply create table and insert scripts to create a test case (obviously use 'dummy' data but not too 'dummy' make it meaningful. Show us what you need, maybe we can still help if we can get a feel for the actual data that you are working with.
Note, please don't just post some data though, put it into scripts for us :)
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

Rasheed Shaik
  • Locked
  • Question
\Orant\DBS\FMCUS.MSG not found
Replies
0
Views
379
Rasheed Shaik
Rasheed Shaik
George L
  • Locked
  • Question
web app not saving "all" records suddenly
Replies
3
Views
294
carp
carp
Livia8
  • Locked
  • Question
SQL SELECT - where date is null or greater than 2
Replies
9
Views
2K
carp
carp
Andrzejek
  • Locked
  • Question
Show value from DATA_DEFAULT field 1
Replies
2
Views
345
Andrzejek
Andrzejek
mibeach7
  • Locked
  • Question
OEM extract ORA- from string text 1
Replies
2
Views
104
mibeach7
mibeach7

Part and Inventory Search

Sponsor

Back
Top