Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Jan 2003
    Location
    New Delhi
    Posts
    11

    Unhappy Unanswered: Newbie : Need help in Sorting

    I have written a query to find out the total worked hours & total dollars
    for the employees who have worked in a particular time period .I need to sort them such that the employees with total amount =0 ( for the given period) come first followed by the employees who are to be paid .
    At the end , I want to count the number of employees with total Amt = 0
    & then the count of no. of employees who are to be paid . Here is the query

    SELECT TAB1.EMP_ID ,
    TAB1.EMP_NAME ,
    TO_CHAR(ROUND((SUM(TAB1.WRKD_MINUTES))/60,2))
    WRKD_HOURS,
    TO_CHAR(ROUND(SUM(TAB1.WRKD_MINUTES *
    TAB1.WRKD_RATE)/60)) TOTAL_AMT
    FROM
    ( SELECT
    EMPLOYEE.EMP_ID,
    EMPLOYEE.EMP_NAME,
    (EMPLOYEE.EMP_FIRSTNAME||' '||EMPLOYEE.EMP_LASTNAME) FULLNAME,
    HOUR_TYPE.HTYPE_NAME,
    TIME_CODE.TCODE_NAME,
    WORK_DETAIL.WRKD_WORK_DATE,
    WORK_DETAIL.WRKD_MINUTES,
    WORK_DETAIL.WRKD_RATE,
    TO_CHAR(ROUND((WORK_DETAIL.WRKD_MINUTES)/60,2),'9999.00') WRKD_HOURS,
    TO_CHAR(ROUND((WORK_DETAIL.WRKD_MINUTES * WORK_DETAIL.WRKD_RATE)/60)) TOTAL_AMT,
    CALC_GROUP.CALCGRP_NAME,
    PAY_GROUP.PAYGRP_NAME
    FROM
    EMPLOYEE, HOUR_TYPE,TIME_CODE, WORK_DETAIL,
    WORK_SUMMARY, CALC_GROUP, JOB,PAY_GROUP
    -- WORKBRAIN_TEAM,EMPLOYEE_TEAM
    WHERE
    CALC_GROUP.CALCGRP_ID = EMPLOYEE.CALCGRP_ID
    AND HOUR_TYPE.HTYPE_ID = WORK_DETAIL.HTYPE_ID
    AND HOUR_TYPE.HTYPE_ID = TIME_CODE.HTYPE_ID
    -- AND hour_type.htype_name = 'UNPAID'
    -- AND time_code.tcode_name = 'UAT'
    AND WORK_DETAIL.WRKS_ID = WORK_SUMMARY.WRKS_ID
    AND WORK_SUMMARY.EMP_ID = EMPLOYEE.EMP_ID
    -- AND WORK_DETAIL.WRKD_WORK_DATE < SYSDATE
    -- AND WORKBRAIN_TEAM.WBT_ID = EMPLOYEE_TEAM.WBT_ID
    -- AND EMPLOYEE_TEAM.EMP_ID = EMPLOYEE.EMP_ID
    AND WORK_DETAIL.JOB_ID = JOB.JOB_ID
    AND EMPLOYEE.PAYGRP_ID = PAY_GROUP.PAYGRP_ID ) TAB1
    GROUP BY EMP_ID,EMP_NAME

    I have tried to group by Total Amount but it does not give the necessary order . Also , the database I am using is Oracle 8I enterprise version 8.1.7.7.0 .When I try to avoid TO_CHAR & just use ROUND , My query goes in a loop .

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: Newbie : Need help in Sorting

    You cannot group by the Total Amount, but you can order by it. Add this at the end:

    ORDER BY total_amt

    I don't understand your other problem with the ROUND.

  3. #3
    Join Date
    Jan 2003
    Location
    New Delhi
    Posts
    11
    I had earlier tried ORDER BY .But it gives an error message ,
    ERROR-937 at Line 1 Column 1 Message :ORA-00937:not a single-group group function.

    I was trying to avoid TO_CHAR so that I can test the query by giving conditions such as TOTAL_AMT > 45 etc .When I use TO_CHAR , I cannot use this comparisons .

  4. #4
    Join Date
    Jan 2003
    Location
    New Delhi
    Posts
    11
    If I remove TO_CHAR , My query does not fetch any result & when I try to refresh , it says "Data is being retrieved " .But I know , that the query would not return any results & I have to abort it .

  5. #5
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Originally posted by ritz1975
    If I remove TO_CHAR , My query does not fetch any result & when I try to refresh , it says "Data is being retrieved " .But I know , that the query would not return any results & I have to abort it .
    Post your query - without the TO_CHARs and with the ORDER BY that you tried. This isn't making sense!

  6. #6
    Join Date
    Jan 2003
    Location
    New Delhi
    Posts
    11

    Unhappy

    I again gives the same oracle error msg which i posted .

  7. #7
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Originally posted by andrewst
    Post your query - without the TO_CHARs and with the ORDER BY that you tried. This isn't making sense!
    I also don't understand why you are doing a select from a select - why not just this:

    Code:
    SELECT 
          EMPLOYEE.EMP_ID, 
          EMPLOYEE.EMP_NAME, 
          ROUND((SUM(WORK_DETAIL.WRKD_MINUTES))/60,2) WRKD_HOURS,
          ROUND(SUM(WORK_DETAIL.WRKD_MINUTES * WORK_DETAIL.WRKD_RATE)/60) TOTAL_AMT
    FROM 
          EMPLOYEE, HOUR_TYPE,TIME_CODE, WORK_DETAIL, 
          WORK_SUMMARY, CALC_GROUP, JOB,PAY_GROUP
          -- WORKBRAIN_TEAM,EMPLOYEE_TEAM 
    WHERE 
          CALC_GROUP.CALCGRP_ID = EMPLOYEE.CALCGRP_ID 
          AND HOUR_TYPE.HTYPE_ID = WORK_DETAIL.HTYPE_ID 
          AND HOUR_TYPE.HTYPE_ID = TIME_CODE.HTYPE_ID 
          -- AND hour_type.htype_name = 'UNPAID' 
          -- AND time_code.tcode_name = 'UAT' 
          AND WORK_DETAIL.WRKS_ID = WORK_SUMMARY.WRKS_ID 
          AND WORK_SUMMARY.EMP_ID = EMPLOYEE.EMP_ID 
          -- AND WORK_DETAIL.WRKD_WORK_DATE < SYSDATE 
          -- AND WORKBRAIN_TEAM.WBT_ID = EMPLOYEE_TEAM.WBT_ID 
          -- AND EMPLOYEE_TEAM.EMP_ID = EMPLOYEE.EMP_ID 
          AND WORK_DETAIL.JOB_ID = JOB.JOB_ID 
          AND EMPLOYEE.PAYGRP_ID = PAY_GROUP.PAYGRP_ID 
    GROUP BY EMP_ID,EMP_NAME
    ORDER BY TOTAL_AMT
    ?

  8. #8
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Originally posted by ritz1975
    I again gives the same oracle error msg which i posted .
    Try the version I just posted - or show me the code that you are actually running that gives the error message.

  9. #9
    Join Date
    Jan 2003
    Location
    New Delhi
    Posts
    11
    That's because I have to use the other variables like Hourtpe ,Calcgroup,Paygroup etc . in the report view .I am working on the PL/SQL inside a product called WORKBRAIN ERM (Employee Relationship Management) .

    Unless I use these variables in the SELECT ,I cannot define report criteria while designing the report .

    I am trying your query now .

  10. #10
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    I know nothing about WORKBRAIN ERM, but maybe it is part of the problem. I suggest you try running your query in SQL Plus first. Once it works there, THEN cut and paste it into WORKBRAIN ERM. If it then doesn't work, you know where the problem lies...

    I understand that you need to join to various tables to restrict your query, but I still don't see why that should mean you need to use an inline view (SELECT FROM (SELECT FROM))

  11. #11
    Join Date
    Jan 2003
    Location
    New Delhi
    Posts
    11
    I tried to run your code .But it now gives an error message ,
    ERROR-918 at Line 1 Column 1 Message :ORA-00918:column ambiguously defined .

    I changed the table order in the FROM statement to
    EMPLOYEE,WORK_DETAIL,HOUR_TYPE,TIME_CODE,
    WORK_SUMMARY, CALC_GROUP, JOB,PAY_GROUP

    instead of EMPLOYEE,HOUR_TYPE,TIME_CODE,WORK_DETAIL,
    WORK_SUMMARY, CALC_GROUP, JOB,PAY_GROUP

    but the result is the same .

  12. #12
    Join Date
    Jan 2003
    Location
    New Delhi
    Posts
    11
    See here is the sample code of the view

    CREATE OR REPLACE VIEW REPT_VIEW_IP_S_PAID_UNPAID
    (
    EMP_ID,
    EMP_NAME,
    FULLNAME,
    HYPE_NAME,
    TCODE_NAME,
    WRKD_WORK_DATE,
    WRKD_MINUTES,
    WRKD_HOURS,
    TOTAL_AMT,
    CALCGRP_NAME,
    PAYGRP_NAME
    )
    AS
    SELECT
    EMPLOYEE.EMP_ID,
    EMPLOYEE.EMP_NAME,
    (EMPLOYEE.EMP_FIRSTNAME||' '||EMPLOYEE.EMP_LASTNAME) FULLNAME,
    HOUR_TYPE.HTYPE_NAME,
    TIME_CODE.TCODE_NAME,
    WORK_DETAIL.WRKD_WORK_DATE,
    WORK_DETAIL.WRKD_MINUTES,
    TO_CHAR(ROUND(SUM(WORK_DETAIL.WRKD_MINUTES)/60,2),'9999.00') WRKD_HOURS,
    TO_CHAR(ROUND(SUM(WORK_DETAIL.WRKD_MINUTES * WORK_DETAIL.WRKD_RATE)/60)) TOTAL_AMT,
    CALC_GROUP.CALCGRP_NAME,
    PAY_GROUP.PAYGRP_NAME
    FROM
    EMPLOYEE, HOUR_TYPE,TIME_CODE, WORK_DETAIL,
    WORK_SUMMARY, CALC_GROUP, JOB,PAY_GROUP
    -- WORKBRAIN_TEAM,EMPLOYEE_TEAM
    WHERE
    CALC_GROUP.CALCGRP_ID = EMPLOYEE.CALCGRP_ID
    AND HOUR_TYPE.HTYPE_ID = WORK_DETAIL.HTYPE_ID
    AND HOUR_TYPE.HTYPE_ID = TIME_CODE.HTYPE_ID
    -- AND hour_type.htype_name <> 'UNPAID'
    -- AND time_code.tcode_name <> 'UAT'
    AND WORK_DETAIL.WRKS_ID = WORK_SUMMARY.WRKS_ID
    AND WORK_SUMMARY.EMP_ID = EMPLOYEE.EMP_ID
    -- AND WORK_DETAIL.WRKD_WORK_DATE < SYSDATE
    -- AND WORKBRAIN_TEAM.WBT_ID = EMPLOYEE_TEAM.WBT_ID
    -- AND EMPLOYEE_TEAM.EMP_ID = EMPLOYEE.EMP_ID
    AND WORK_DETAIL.JOB_ID = JOB.JOB_ID
    AND EMPLOYEE.PAYGRP_ID = PAY_GROUP.PAYGRP_ID


    I am using all those SELECT fields 'coz If I do not select all of them in the same order , I cannot define them under Create view statement

  13. #13
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    I had forgotten to put the table aliases on the GROUP BY columns. Try this now:

    SELECT
    EMPLOYEE.EMP_ID,
    EMPLOYEE.EMP_NAME,
    ROUND((SUM(WORK_DETAIL.WRKD_MINUTES))/60,2) WRKD_HOURS,
    ROUND(SUM(WORK_DETAIL.WRKD_MINUTES * WORK_DETAIL.WRKD_RATE)/60) TOTAL_AMT
    FROM
    EMPLOYEE, HOUR_TYPE,TIME_CODE, WORK_DETAIL,
    WORK_SUMMARY, CALC_GROUP, JOB,PAY_GROUP
    -- WORKBRAIN_TEAM,EMPLOYEE_TEAM
    WHERE
    CALC_GROUP.CALCGRP_ID = EMPLOYEE.CALCGRP_ID
    AND HOUR_TYPE.HTYPE_ID = WORK_DETAIL.HTYPE_ID
    AND HOUR_TYPE.HTYPE_ID = TIME_CODE.HTYPE_ID
    -- AND hour_type.htype_name = 'UNPAID'
    -- AND time_code.tcode_name = 'UAT'
    AND WORK_DETAIL.WRKS_ID = WORK_SUMMARY.WRKS_ID
    AND WORK_SUMMARY.EMP_ID = EMPLOYEE.EMP_ID
    -- AND WORK_DETAIL.WRKD_WORK_DATE < SYSDATE
    -- AND WORKBRAIN_TEAM.WBT_ID = EMPLOYEE_TEAM.WBT_ID
    -- AND EMPLOYEE_TEAM.EMP_ID = EMPLOYEE.EMP_ID
    AND WORK_DETAIL.JOB_ID = JOB.JOB_ID
    AND EMPLOYEE.PAYGRP_ID = PAY_GROUP.PAYGRP_ID
    GROUP BY EMPLOYEE.EMP_ID,EMPLOYEE.EMP_NAME
    ORDER BY TOTAL_AMT

  14. #14
    Join Date
    Jan 2003
    Location
    New Delhi
    Posts
    11

    Talking

    That Works .Some how it still goes into a loop when I am using ROUND .I used TO_CHAR in fornt of both these variables & it then works .

    Don't know why it does not work with a simple ROUND .I think I will need to check with the vendor .


    Thanks a lot ,Andrewst ...looks like this should complete my query

  15. #15
    Join Date
    Jan 2003
    Location
    New Delhi
    Posts
    11
    Also , how do I get the counts of employees with Total amount = 0 & the rest separately ?

Posting Permissions

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