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 .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.|||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 .|||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 .|||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!|||I again gives the same oracle error msg which i posted .|||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:
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
?|||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.|||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 .|||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))|||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 .|||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|||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|||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|||Also , how do I get the counts of employees with Total amount = 0 & the rest separately ?|||Originally posted by ritz1975
Also , how do I get the counts of employees with Total amount = 0 & the rest separately ?
Now that DOES need an inline view:
SELECT COUNT(*) FROM
( SELECT emp_id, SUM(...)
FROM ...
GROUP BY emp_id
HAVING SUM(...) = 0
)
