from keyword not found where expected

PappaG · Feb 4, 2008

Hi guys can appologies for the messy query but can anyone spot the error. It says from keyword not found where expected. Also can someone tell me if the second query is valid as i thought i could reference the names of previous AS "Blah" statements in following calculations.

Code:

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'));

query2

Code:

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'));

PappaG · Feb 4, 2008

OK someone pointed out a few errors and i have fixed them but now get error message nested group function without group by, if i add a group by o.object_name at the end i 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'));

r937 · Feb 4, 2008

that's some real jaw-dropping sql there

did you write that query? or are you just the poor guy who has to support it? because the guy who wrote it should be shot

which dbms is this please? i would like to direct you to the appropriate forum

r937.com | rudy.ca

PappaG · Feb 4, 2008

i guess i should put my hand up and stand in line. I wrote it but i dont have any sql skills i use access to give me a start point then just have a good old bash at it appologies i am aware i have a massive lack of skills at this but hey i try....using oracle 8i

r937 · Feb 4, 2008

for an access developer, it's a pretty good query

i absolutely *love* access, at least the front-end query-building user-helpful graphical user interface part of it

the sql it generates is pretty awful, though

i guess the main thing i want to convey to you is the importance of indentation

(this is one of the reasons i never store my sql in access, i always store it in an external text-based query library, because access will happily munge your indentation)

please try your question again in forum186

r937.com | rudy.ca

PappaG · Feb 4, 2008

cheers r937 i have already tried posting it there but always find the resposes well not to helpfull as they tend only to point you in the right direction and when you don't know sql sometimes it isn't a great help. The guy i work with always posts his query's in here and the responses he gets are usually spot on and apart from fixing his syntax they normally show him how to improve his sql.

r937 · Feb 4, 2008

start by creating a view or views (in ms access, these would be stored queries) which pre-calculate the values of NOT_READY, TOTAL_NOT_READY_HR, etc.

what you're working with is really too clumsy

r937.com | rudy.ca

jaxtell · Feb 4, 2008

Wow. Here is my best attempt...

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')
         )
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,
         (v.t_login / 60) / 60,
         (v.t_talk / 60) / 60,
         (v.t_wait / 60) / 60,
         (cr14r1v.t_acw / 60) / 60,
         (v.t_hold / 60) / 60,
         (v.t_outbound / 60) / 60,
         ((v.t_login / 60) / 60) / 5.4,
         ((v.t_talk / 60) / 60) / 5.4,
         ((v.t_wait / 60) / 60) / 5.4,
         ((cr14r1v.t_acw / 60) / 60) / 5.4,
         ((v.t_hold / 60) / 60) / 5.4,
         ((v.t_outbound / 60) / 60) / 5.4,
         v.t_talk / (v.t_login - (cr14r1v.t_break + cr14r1v.t_lunch)),
         v.t_wait / (v.t_login - (cr14r1v.t_break + cr14r1v.t_lunch)),
         cr14r1v.t_acw / (v.t_login - (cr14r1v.t_break + cr14r1v.t_lunch)),
         v.t_hold / (v.t_login - (cr14r1v.t_break + cr14r1v.t_lunch)),
         v.t_outbound / (v.t_login - (cr14r1v.t_break + cr14r1v.t_lunch));

r937 · Feb 4, 2008

wow, that's some fancy indenting, nice job

i wasn't aware one could nest SUM functions -- i would be reluctant to even try it, because of the semantic problems

what semantic problems? well, we all know that a GROUP BY query can produce an aggregate result like SUM, but here it appears you want to take a particular SUM, and include it inside another SUM calculation, resulting in the nested SUM being added in to the outer SUM as many times as there are rows in the outer aggregation?

also, i would be very suspicious of the tables involved here

what are the significant differences in these tables:

datamart01.o_cr60_ag_day
datamart01.v_cr60_ag_day
datamart01.t_cr60_ag_day
datamart01.o_grofags_day
datamart01.v_grofags_day
datamart01.t_grofags_day
datamart01.o_cr14r1_ag_day
datamart01.v_cr14r1_ag_day
datamart01.t_cr14r1_ag_day

are they, in fact, all separate tables? some of them look like they might be views

and why so many tables?

r937.com | rudy.ca

JarlH · Feb 4, 2008

Does Oracle really allow all those mathematical expressions in the GROUP BY clause?

Can't you create views performing several of those things, making the main query much easier to understand/write?

PappaG · Feb 5, 2008

Can't create views i only have access to whats currently in place no changes although 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

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!

from keyword not found where expected

PappaG

Technical User

PappaG

Technical User

r937

Technical User

PappaG

Technical User

r937

Technical User

PappaG

Technical User

r937

Technical User

jaxtell

Programmer

r937

Technical User

JarlH

Programmer

PappaG

Technical User

Similar threads

Part and Inventory Search

Sponsor