×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

from keyword not found where expected

from keyword not found where expected

from keyword not found where expected

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

RE: from keyword not found where expected

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

RE: from keyword not found where expected

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

RE: from keyword not found where expected

(OP)
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

RE: from keyword not found where expected

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: Oracle: Oracle release - 8 and 8i

r937.com | rudy.ca

RE: from keyword not found where expected

(OP)
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.

RE: from keyword not found where expected

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

RE: from keyword not found where expected

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

RE: from keyword not found where expected

wow, that's some fancy indenting, nice job smile

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

RE: from keyword not found where expected

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?

RE: from keyword not found where expected

(OP)
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 smile

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login


Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close