Oracle SQL - select inside select (in the same table!)

Question

I will try to explain what I am trying to achieve quickly, since I have no idea how to explain it otherwise!

We have a table that shows the entire employment history for all employees, I want to "Start_Date" the current message ("Current_Flag" = "Y"). Also, I want the "End_Date" message before (going to filter by the current flag, sort by end date and just grab the top one)

Anyway, here is my code:

SELECT "Gc_Staff_Number", "Start_Date", (SELECT "End_Date" FROM "Employment_History" WHERE "Current_Flag" != 'Y' AND ROWNUM = 1 AND "Employee_Number" = "Employment_History"."Employee_Number" ORDER BY "End_Date" ASC) FROM "Employment_History" WHERE "Current_Flag" = 'Y' 

Any suggestions on how to get this job will be fantastic, we hope that the above makes a little sense - to be honest, at the moment the request will not work, which really sucks, hmm.

(edit: Oh! I am writing this to query an existing system ... which for some reason has all the silly double quotes around table and field names, sigh!)

Answer 1

This is exactly the scenario in which analytics comes to the rescue.

Given these test data:

 SQL> select * from employment_history 2 order by Gc_Staff_Number 3 , start_date 4 / GC_STAFF_NUMBER START_DAT END_DATE C --------------- --------- --------- - 1111 16-OCT-09 Y 2222 08-MAR-08 26-MAY-09 N 2222 12-DEC-09 Y 3333 18-MAR-07 08-MAR-08 N 3333 01-JUL-09 21-MAR-09 N 3333 30-JUL-10 Y 6 rows selected. SQL> 

The built-in view with the analytic function LAG () gives the correct answer:

 SQL> select Gc_Staff_Number 2 , start_date 3 , prev_end_date 4 from ( 5 select Gc_Staff_Number 6 , start_date 7 , lag (end_date) over (partition by Gc_Staff_Number 8 order by start_date ) 9 as prev_end_date 10 , current_flag 11 from employment_history 12 ) 13 where current_flag = 'Y' 14 / GC_STAFF_NUMBER START_DAT PREV_END_ --------------- --------- --------- 1111 16-OCT-09 2222 12-DEC-09 26-MAY-09 3333 30-JUL-10 21-MAR-09 SQL> 

Built-in browsing is critical to getting the right result. Otherwise, the CURRENT_FLAG filter deletes the previous lines.

Answer 2

I'm a little confused by quotes, however below should work for you:

 SELECT "Gc_Staff_Number", "Start_Date", x.end_date FROM "Employment_History" eh, (SELECT "End_Date" FROM "Employment_History" WHERE "Current_Flag" != 'Y' AND ROWNUM = 1 AND "Employee_Number" = eh.Employee_Number ORDER BY "End_Date" ASC) x WHERE "Current_Flag" = 'Y' 

Answer 3

 SELECT "Gc_Staff_Number", "Start_Date", (SELECT "End_Date" FROM "Employment_History" WHERE "Current_Flag" != 'Y' AND ROWNUM = 1 AND "Employee_Number" = "Employment_History"."Employee_Number" ORDER BY "End_Date" ASC) FROM "Employment_History" WHERE "Current_Flag" = 'Y' 

FYI, ROWNUM = 1 is evaluated to ORDER BY in this case, so the internal query will sort the total number (maximum) of one record.

If you are really looking for the earliest end_date for this employee (where current_flag <> 'Y') is this what you are looking for?

 SELECT "Gc_Staff_Number", "Start_Date", eh.end_date FROM "Employment_History" eh LEFT OUTER JOIN -- in case the current record is the only record... (SELECT "Employee_Number" , MIN("End_Date") as end_date FROM "Employment_History" WHERE "Current_Flag" != 'Y' GROUP BY "Employee_Number" ) emp_end_date ON eh."Employee_Number" = emp_end_date."Employee_Number" WHERE eh."Current_Flag" = 'Y' 

Answer 4

 SELECT eh."Gc_Staff_Number", eh."Start_Date", MAX(eh2."End_Date") AS "End_Date" FROM "Employment_History" eh LEFT JOIN "Employment_History" eh2 ON eh."Employee_Number" = eh2."Employee_Number" and eh2."Current_Flag" != 'Y' WHERE eh."Current_Flag" = 'Y' GROUP BY eh."Gc_Staff_Number", eh."Start_Date 

Answer 5

This is what I used the LAG function for:

 SELECT eh.gc_staff_number, eh.start_date, LAG(eh.end_date) OVER (PARTITION BY eh.gc_staff_number ORDER BY eh.end_date) AS prev_end_date FROM EMPLOYMENT_HISTORY eh WHERE eh.current_flag = 'Y' 

If you want to look ahead, you should use the LEAD function .

Compatibility:

As far as I know, this is supported by 9i +, but I have not confirmed that 8i is supported, as are the documentation requirements.

LEAD and LAG are finally ANSI, but only Oracle and PostgreSQL v8.4 + currently support them.

Answer 6

Basically, all you have to do is

 select ..., (select ... from ... where ...) as ..., ..., from ... where ... 

For instance. You can insert (select ... from ... where) wherever you want, it will be replaced with the corresponding data.

I know that other examples (even if each of them is really wonderful :)) is a little hard to understand for beginners (like me: p), so I hope this “simple” example helps some of you guys :)