Am having a nightmare with a certain piece of code.. have tried almost everything and just cannot see what the issue is..
Code:
CREATE OR REPLACE VIEW TOP_EARNER_PER_LOCATION
AS
SELECT E.FIRST_NAME || ' ' || E.LAST_NAME AS EMPLOYEE_NAME,
L.REGIONAL_GROUP AS REGIONAL_GROUP,
E.SALARY AS SALARY,
J.JOB_ID
FROM EMPLOYEE E
LEFT OUTER JOIN DEPARTMENT D
ON D.DEPARTMENT_ID = E.DEPARTMENT_ID
LEFT OUTER JOIN LOCATION L
ON L.LOCATION_ID = D.LOCATION_ID
LEFT OUTER JOIN JOB J
ON J.JOB_ID = E.JOB_ID
GROUP BY E.FIRST_NAME || ' ' || E.LAST_NAME
HAVING J.JOB_ID NOT IN (670,671,672)
;
REM #...SELECT STATEMENT TO RUN QUERY GOES HERE
However it doesn't pull through the HIGHEST earner per location, simply a list of earners, locations and salarys.
The query looks as such:
Code:
EMPLOYEE_NAME REGIONAL_GROUP SALARY
------------------------------- -------------------- ----------------------
FRED JAMES CHICAGO 950
DENISE SOMMERS CHICAGO 1850
JAMES MURRAY DALLAS 750
JOHN SMITH DALLAS 800
DIANE ADAMS DALLAS 1100
RICHARD LEWIS DALLAS 1800
DONALD SCOTT DALLAS 3000
JENNIFER FORD DALLAS 3000
ALICE JENSEN NEW YORK 750
MICHAEL DOUGLAS NEW YORK 800
BARBARA MILLER NEW YORK 1300
GRACE ROBERTS NEW YORK 2875
MATTHEW FISHER NEW YORK 3000
Any help would be greatly appreciated
Last edited by U_C_Dispatj; 06-10-2011 at 10:08 AM..
Group by should have all variable columns not aggregates, as it is used for aggregates, which you lack. Having is for testing aggregates. What is you general aim?
You might want two queries, derived tables, one with the aggregate and key only, finding top salary per dept and inner join that pathfinder query/derived table to the no-aggregate report query just as a filter on salary= and dept=. Might be many per dept on same high salary. Order by dept, name, maybe.
Last edited by DGPickett; 06-10-2011 at 04:15 PM..
Hello ,
I have three files :
sampleoutput1.txt has columns (in the following order) : hostname ; available patches , available packages
sampleoutput2.txt has columns (in the following order) : hostname ; patchwave ; BSID ; Application
sampleoutput3.txt has columns (in the following... (10 Replies)
I'm trying to return only one row with the highest value for PCT_MAX_USED. Any suggestions?
When I add this code, I get the ORA-00937 error.
trunc(max(decode( kbytes_max, 0, 0, (kbytes_alloc/kbytes_max)*100))) pct_max_used
This is the original and returns all rows.
select (select... (3 Replies)
I have some records to be updated in oracle table. I am using sql developer tool. could any one tell me how to update those records in oracle table. I am having excel sheet with those records. (4 Replies)
Hi,
I am trying to read files from NFS mount. The ID with which I am trying to read is part of 16+ groups.
This is causing problem in doing change directory to the NFS mount directory.
I was able to get some code which does newgrp to change the default group so that I could change... (3 Replies)
Greetings all,
Hopefully there is someone out there on this forum who can help with this Oracle SQL Developer issue I have.
Here is the code:
CREATE OR REPLACE VIEW
SALES_OVER_30000_WITH_TOTAL
AS
SELECT
E.FIRST_NAME || ' ' || E.LAST_NAME AS EMPLOYEE_NAME,
... (2 Replies)
Hi All,
I have a table with the following columns:
sysName, date, time, cpuNum, cpuPercentageBsy
There are multiple system names and multiple CPU numbers.
I need to produce a report that shows the cpuPercentageBsy for cpuNum's 0 and 1 and then an average of cpuPercentageBsy for all the... (1 Reply)