Results 1 to 7 of 7
  1. #1
    Join Date
    Oct 2004
    Posts
    4

    Smile Unanswered: ORA-00979 for Inline View

    Hi

    I am created a query from a time clock system that stores employee punches in a detail table. There are multiple records for an employee for a day. I query this table to get a total of overtime hours and then group that total by the store that they work for. I also need to include the number of records that have not been authorized by a store supervisor. That total is in the summary table. It only has one record per employee per day. There is a one-to many relationship between the two tables. To get the count I had to create an inline view as I am using oracle version 9.2.0.4.0. However when I refer to the count, I get an ora-00979 error saying I need to include the count in the group by expression. When I add a function to the count, I get the same number for all the stores. Can someone take a look at my query and let me know what I am doing wrong? How to I get the count from my inline view?

    SELECT
    WORKBRAIN_TEAM.wbt_name,
    sum(decode(work_detail.htype_id,3,work_detail.wrkd _minutes/60,0)) tot_OT1,
    sum(decode(work_detail.htype_id,2,work_detail.wrkd _minutes/60,0)) tot_OT2,
    max(my_view_summary.totua)
    FROM (SELECT workbrain_team.wbt_name,
    sum(case when work_summary.wrks_authorized = 'N' then 1 else 0 end) as totua
    FROM
    VIEW_EMP_HOME_TEAM,
    WORKBRAIN_TEAM,
    WORK_SUMMARY
    WHERE VIEW_EMP_HOME_TEAM.CLIENT_ID=1 AND WORK_SUMMARY.CLIENT_ID=1 AND WORKBRAIN_TEAM.CLIENT_ID=1 AND
    WORKBRAIN_TEAM.wbt_id = VIEW_EMP_HOME_TEAM.wbt_id
    AND WORK_SUMMARY.emp_id = VIEW_EMP_HOME_TEAM.emp_id
    AND WORK_SUMMARY.wrks_work_date >= TO_DATE( SUBSTR('20041012 000000',1,8), 'yyyymmdd' )
    AND WORK_SUMMARY.wrks_work_date < ( TO_DATE( SUBSTR('20041012 000000',1,8), 'yyyymmdd' ) + (1) )
    AND WORK_SUMMARY.wrks_tcode_sum <> ' '
    AND WORK_SUMMARY.wrks_htype_sum <> ' '
    AND WORK_SUMMARY.emp_id IN (SELECT SEC_EMPLOYEE.EMP_ID FROM SEC_EMPLOYEE
    WHERE SEC_EMPLOYEE.CLIENT_ID=1 AND ( (SEC_EMPLOYEE.START_DATE <= TO_DATE( SUBSTR('20041012 000000',1,8), 'yyyymmdd' ) ) or
    (SEC_EMPLOYEE.START_DATE <= TO_DATE( SUBSTR('20041012 000000',1,8), 'yyyymmdd' ) ) ) and
    ( (SEC_EMPLOYEE.END_DATE >= TO_DATE( SUBSTR('20041012 000000',1,8), 'yyyymmdd' ) ) or
    (SEC_EMPLOYEE.END_DATE >= TO_DATE( SUBSTR('20041012 000000',1,8), 'yyyymmdd' ) ) ) and
    SEC_EMPLOYEE.WBU_ID = 27994 )
    AND (('true' = 'true') and
    WORKBRAIN_TEAM.WBT_ID in (select SEC_WORKBRAIN_TEAM.WBT_ID from SEC_WORKBRAIN_TEAM
    where SEC_WORKBRAIN_TEAM.CLIENT_ID=1 AND SEC_WORKBRAIN_TEAM.wbt_id in (select SEC_WB_TEAM_CHILD_PARENT.child_wbt_id from SEC_WB_TEAM_CHILD_PARENT
    where SEC_WB_TEAM_CHILD_PARENT.CLIENT_ID=1 AND ( (('10020' = '-99') AND (SEC_WB_TEAM_CHILD_PARENT.parent_wbt_id in (select SEC_WORKBRAIN_TEAM.wbt_id from SEC_WORKBRAIN_TEAM where SEC_WORKBRAIN_TEAM.CLIENT_ID=1 AND SEC_WORKBRAIN_TEAM.wbu_id = 27994)) )
    OR (SEC_WB_TEAM_CHILD_PARENT.parent_wbt_id IN (10020)) )
    )
    )
    or (('true' = 'false') and
    ( (('10020' = '-99') AND (WORKBRAIN_TEAM.WBT_ID in (select SEC_WORKBRAIN_TEAM.wbt_id from SEC_WORKBRAIN_TEAM where SEC_WORKBRAIN_TEAM.CLIENT_ID=1 AND SEC_WORKBRAIN_TEAM.wbu_id = 27994)))
    OR (WORKBRAIN_TEAM.WBT_ID in (10020))))
    )
    AND ( (VIEW_EMP_HOME_TEAM.EMPT_START_DATE <= TO_DATE( SUBSTR('20041012 000000',1,8), 'yyyymmdd' ) ) or
    (VIEW_EMP_HOME_TEAM.EMPT_START_DATE <= TO_DATE( SUBSTR('20041012 000000',1,8), 'yyyymmdd' ) ) )
    AND ( (VIEW_EMP_HOME_TEAM.EMPT_END_DATE >= TO_DATE( SUBSTR('20041012 000000',1,8), 'yyyymmdd' ) ) or
    (VIEW_EMP_HOME_TEAM.EMPT_END_DATE >= TO_DATE( SUBSTR('20041012 000000',1,8), 'yyyymmdd' ) ) )
    group by WORKBRAIN_TEAM.wbt_name) my_view_summary,
    VIEW_EMP_HOME_TEAM,
    WORKBRAIN_TEAM,
    WORK_SUMMARY,
    WORK_DETAIL
    WHERE WORKBRAIN_TEAM.wbt_id = VIEW_EMP_HOME_TEAM.wbt_id
    AND WORK_SUMMARY.emp_id = VIEW_EMP_HOME_TEAM.emp_id
    AND WORK_SUMMARY.wrks_id = WORK_DETAIL.wrks_id
    AND WORK_DETAIL.wrkd_work_date >= TO_DATE( SUBSTR('20041012 000000',1,8), 'yyyymmdd' )
    AND WORK_DETAIL.wrkd_work_date < ( TO_DATE( SUBSTR('20041012 000000',1,8), 'yyyymmdd' ) + (1) )
    AND WORK_SUMMARY.emp_id IN (SELECT SEC_EMPLOYEE.EMP_ID FROM SEC_EMPLOYEE
    WHERE SEC_EMPLOYEE.CLIENT_ID=1 AND ( (SEC_EMPLOYEE.START_DATE <= TO_DATE( SUBSTR('20041012 000000',1,8), 'yyyymmdd' ) ) or
    (SEC_EMPLOYEE.START_DATE <= TO_DATE( SUBSTR('20041012 000000',1,8), 'yyyymmdd' ) ) ) and
    ( (SEC_EMPLOYEE.END_DATE >= TO_DATE( SUBSTR('20041012 000000',1,8), 'yyyymmdd' ) ) or
    (SEC_EMPLOYEE.END_DATE >= TO_DATE( SUBSTR('20041012 000000',1,8), 'yyyymmdd' ) ) ) and
    SEC_EMPLOYEE.WBU_ID = 27994 )
    AND (('true' = 'true') and
    WORKBRAIN_TEAM.WBT_ID in (select SEC_WORKBRAIN_TEAM.WBT_ID from SEC_WORKBRAIN_TEAM
    where SEC_WORKBRAIN_TEAM.CLIENT_ID=1 AND SEC_WORKBRAIN_TEAM.wbt_id in (select SEC_WB_TEAM_CHILD_PARENT.child_wbt_id from SEC_WB_TEAM_CHILD_PARENT
    where SEC_WB_TEAM_CHILD_PARENT.CLIENT_ID=1 AND ( (('10020' = '-99') AND (SEC_WB_TEAM_CHILD_PARENT.parent_wbt_id in (select SEC_WORKBRAIN_TEAM.wbt_id from SEC_WORKBRAIN_TEAM where SEC_WORKBRAIN_TEAM.CLIENT_ID=1 AND SEC_WORKBRAIN_TEAM.wbu_id = 27994)) )
    OR (SEC_WB_TEAM_CHILD_PARENT.parent_wbt_id IN (10020)) )
    )
    )
    or (('true' = 'false') and
    ( (('10020' = '-99') AND (WORKBRAIN_TEAM.WBT_ID in (select SEC_WORKBRAIN_TEAM.wbt_id from SEC_WORKBRAIN_TEAM where SEC_WORKBRAIN_TEAM.CLIENT_ID=1 AND SEC_WORKBRAIN_TEAM.wbu_id = 27994)))
    OR (WORKBRAIN_TEAM.WBT_ID in (10020))))
    )
    AND ( (VIEW_EMP_HOME_TEAM.EMPT_START_DATE <= TO_DATE( SUBSTR('20041012 000000',1,8), 'yyyymmdd' ) ) or
    (VIEW_EMP_HOME_TEAM.EMPT_START_DATE <= TO_DATE( SUBSTR('20041012 000000',1,8), 'yyyymmdd' ) ) )
    AND ( (VIEW_EMP_HOME_TEAM.EMPT_END_DATE >= TO_DATE( SUBSTR('20041012 000000',1,8), 'yyyymmdd' ) ) or
    (VIEW_EMP_HOME_TEAM.EMPT_END_DATE >= TO_DATE( SUBSTR('20041012 000000',1,8), 'yyyymmdd' ) ) )
    GROUP BY WORKBRAIN_TEAM.wbt_name

    Thanks for your help!

  2. #2
    Join Date
    Nov 2002
    Location
    Desk, slightly south of keyboard
    Posts
    697
    Sorry,

    Haven't even tried to follow/understand your query, but WTF is

    or (('true' = 'false') and

    in your where clause?

    I trust it's some testing or something.

    Confused.
    Bill
    Please don't email me directly with questions. I've probably just got home from the pub and cannot guarantee the sanity of my answers. In fact, I can't believe I actually made it home.

  3. #3
    Join Date
    Oct 2004
    Posts
    4

    Talking Query is called by front in java program

    To those looking at this query, it is called by a front end UI using java and javascript. My main question without having to look at the confusing code is how do I refer to my inline total in the main query without getting the 00979 error?

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    I believe in the KISS principle.
    You might want to start with a small sample table to test the desired SQL technique.
    Alternatu=ively visiit, http://asktom.oracle.com and do a keyword search for
    "INLINE QUERY"
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    Join Date
    Oct 2004
    Posts
    4

    Easier View...Removed xtra stuff

    Here's an easier view of the query without all of the ztra stuff not needed to get the problems solved:

    SELECT
    WORKBRAIN_TEAM.wbt_name,
    sum(decode(work_detail.htype_id,3,work_detail.wrkd _minutes/60,0)) tot_OT1,
    sum(decode(work_detail.htype_id,2,work_detail.wrkd _minutes/60,0)) tot_OT2,
    my_view_summary.totua
    FROM (SELECT workbrain_team.wbt_name,
    sum(case when work_summary.wrks_authorized = 'N' then 1 else 0 end) as totua
    FROM
    VIEW_EMP_HOME_TEAM,
    WORKBRAIN_TEAM,
    WORK_SUMMARY
    WORKBRAIN_TEAM.wbt_id = VIEW_EMP_HOME_TEAM.wbt_id
    AND WORK_SUMMARY.emp_id = VIEW_EMP_HOME_TEAM.emp_id
    AND WORK_SUMMARY.wrks_work_date >= TO_DATE( SUBSTR('20041012 000000',1,8), 'yyyymmdd' )
    AND WORK_SUMMARY.wrks_work_date < ( TO_DATE( SUBSTR('20041012 000000',1,8), 'yyyymmdd' ) + (1) )
    AND WORK_SUMMARY.wrks_tcode_sum <> ' '
    AND WORK_SUMMARY.wrks_htype_sum <> ' '
    group by WORKBRAIN_TEAM.wbt_name) my_view_summary,
    VIEW_EMP_HOME_TEAM,
    WORKBRAIN_TEAM,
    WORK_SUMMARY,
    WORK_DETAIL
    WHERE WORKBRAIN_TEAM.wbt_id = VIEW_EMP_HOME_TEAM.wbt_id
    AND WORK_SUMMARY.emp_id = VIEW_EMP_HOME_TEAM.emp_id
    AND WORK_SUMMARY.wrks_id = WORK_DETAIL.wrks_id
    AND WORK_DETAIL.wrkd_work_date >= TO_DATE( SUBSTR('20041012 000000',1,8), 'yyyymmdd' )
    AND WORK_DETAIL.wrkd_work_date < ( TO_DATE( SUBSTR('20041012 000000',1,8), 'yyyymmdd' ) + (1) )
    GROUP BY WORKBRAIN_TEAM.wbt_name

  6. #6
    Join Date
    Nov 2002
    Location
    Desk, slightly south of keyboard
    Posts
    697
    (crap deleted)
    Last edited by billm; 10-12-04 at 21:58.
    Please don't email me directly with questions. I've probably just got home from the pub and cannot guarantee the sanity of my answers. In fact, I can't believe I actually made it home.

  7. #7
    Join Date
    Oct 2004
    Posts
    4
    Problem solved! I needed to join the fields from the calling query to the inline view. Once I did that, it worked!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •