Problem with using an Order By clause in a sub select statment 2

[CrystalizeCanada]

Hi there,

I have to use a sub select statement (or perhaps this is referred to as an inline statement) within a main statement and have come across the missing right parentesis issue when using an Order By clause in oracle PL-SQL.

Heres a example to explain:

SELECT EmpId, HireDate
(
SELECT EmpId AS PreviousHire
WHERE EmpId <> E.EmpId and HireDate < E.HireDate and RowNum =1
ORDER BY HireDate DESC
)
From Emp E

In the above example which is similar in concept I'm trying find the previously hired employee. I want to use an ORDER BY clause but I'm not allowed to due to the fact that it is an sub select.

Is there an alternative way of doing this that works as a subselect statement. (I have to use a subselect because I'm using a reporting tool where I can take advantage of subselect statements.)

Any help would be greatly appreciated.

Thanks
Gordon

Gordon BOCP
Crystalize

 

[carp]

If your subquery is only returning one row, why would you do an ORDER BY?

 

[SantaMufasa]

Gordon,

I feel as though I am your neighbor this week since we received a gift of frigid Canadian wind from your neck of the woods a few days ago.

Now to your question...There are some syntax and logic issues with your code. First I'll address those issues, then I'll suggest a better way to achieve your objective. Here is corrected code that shows that you can have an "order by" clause in an in-line SELECT:

SELECT PreviousHire, HireDate
  from (SELECT empId AS Previoushire ,hiredate
          from emp e 
         WHERE EmpId <> E.EmpId
           and HireDate < E.HireDate
           and RowNum =1
         ORDER BY HireDate DESC);

no rows selected

Here are the issues with your code:[ul][li]An in-line SELECT still requires a FROM clause of its own. Your in-line did not have a FROM clause, and it cannot "see" the outer SELECT's FROM clause.[/li][li]If your in-line SELECT will be providing the result set for your outer SELECT, then the outer SELECT must refer to the result set from the in-line SELECT. Your outer SELECT wanted to "see" EmpId and HireDate, therefore you need "Hiredate" returned from the in-line SELECT, and the name of the EmpId expression has been aliased to PreviousHire.[/li][li]The reason that there are "no rows selected" in the result set is because the only row that the in-line SELECT considers is "WHERE...rownum=1". In a WHERE clause, "rownum=1" does not take the first row AFTER sorting instead, it limits the initial result set to just the first row from the table.[/li][/ul]

Now for an alternative...(There are many, many alternatives, including the use of Oracle Analytics, but at this point, I believe that the logically simplest method is a good one to start with.) The method I'll illustrate follows this scenario:[ul][li]Identifty the EmpID of the employee that is your focus (i.e., the employee for which you are trying to locate the just-previously-hired employee)[/li][li]Find the HireDate of your focus employee.[/li][li]Find the HireDate of the employee whose HireDate is just previous to your focus employee.[/li][li]Find the employee with that just-previous HireDate[/li][/ul]Following are two pieces of code and their results:

1) A SELECT of the dataset, sorted in HIREDATE descending order, that I'm using to simulate your environment.

2) Code that prompts for an EmpID of the employee that is your starting focus (...the employee for which you are looking for a just-previouly-hired employee). Then, based upon the focus employee, the code SELECTs the previously hired employee. Since there is a SQL*Plus ACCEPT statement in the code, one cannot simply cut-and-paste the ACCEPT and the SELECT statement directly to a SQL*Plus prompt. Instead the code must run from a script. In this case, I have saved the code to a script named TEMP.SQL, which I then invoke from the SQL*Plus prompt:

select empid, last_name, hiredate from emp order by hiredate desc;

EMPID LAST_NAME       HIREDATE
----- --------------- ---------
    9 Catchpole       09-FEB-92
   16 Maduro          07-FEB-92
   14 Nguyen          22-JAN-92
   12 Giljum          18-JAN-92
   15 Dumas           09-OCT-91
   19 Patel           06-AUG-91
   20 Newman          21-JUL-91
    3 Nagayama        17-JUN-91
   21 Markarian       26-MAY-91
   25 Schwartz        09-MAY-91
   24 Dancs           17-MAR-91
   10 Havel           27-FEB-91
   13 Sedeghi         18-FEB-91
   18 Nozaki          09-FEB-91
    6 Urguhart        18-JAN-91
   22 Chang           30-NOV-90
   23 Patel           17-OCT-90
    7 Menchu          14-MAY-90
   11 Magee           14-MAY-90
    4 Quick-To-See    07-APR-90
    8 Biri            07-APR-90
   17 Smith           08-MAR-90
    5 Ropeburn        04-MAR-90
    1 Velasquez       03-MAR-90
    2 Ngao            08-MAR-90

25 rows selected.

(Contents of TEMP.SQL):

accept focus_emp_id prompt "Enter the EmpID of the employee that was hired just after the employee you wish to see: "

select EmpID PreviousHire, Hiredate
  from emp
 where HireDate = (select max(HireDate) from Emp
                    where hiredate < (select HireDate
                                        from emp
                                       where EmpID = &focus_emp_id))
/

SQL> @TEMP.SQL

Enter the EmpID of the employee that was hired just after the employee you wish to see: 3

PREVIOUSHIRE HIREDATE
------------ ---------------
          21 26-MAY-91

1 row selected.

SQL> @TEMP

Enter the EmpID of the employee that was hired just after the employee you wish to see: 22

PREVIOUSHIRE HIREDATE
------------ ---------------
          23 17-OCT-90

1 row selected.

By comparing the original set of employees, sorted by HIREDATE, you can see that TEMP.SQL does what you want.

If you have additional questions, please post.

Mufasa
(aka Dave of Sandy, Utah, USA)
“People may forget what you say, but they will never forget how you made them feel.

 

[CrystalizeCanada]

Hi Mufasa,

Thanks very much for the detailed reply. Unfortunately I'm restricted to using an SQL Expression to figure this out. This I think excludes the solutions your suggesting because you have moved the SQL to the where clause.

The outer part of the query is generated by my report so I can only change whats in the brackets:

SELECT Emp, HireDate, (This is my SQL can go)
FROM Emp

And I think this is where I hit the problem using an Order By in the sub select statement.

You obviously know your stuff. With that restriction in mind any ideas about how to do it as an SQL expression?

Thanks again for your kind help

Gordon



Gordon BOCP
Crystalize

 

[CrystalizeCanada]

Hi Carp,

To answer your question below:

If your subquery is only returning one row, why would you do an ORDER BY?

The ORDER BY clause is needed to put the hiredates in descending order so the rownum = 1 will pick the right one. Otherwise you may not end up with the correct value.

Thanks
Gordon


Gordon BOCP
Crystalize

 

[SantaMufasa]

CC,

In direct response to your clarification to Carp, as both he and I are explaining, WHERE clauses are evaluated first, and ORDER BY happens last. So, effectively what your code is saying is:

1) Go get just the first row from the table, then
2) Sort it by descending HireDate.

...That certaining is not what you want.

I'll soon post an alternative for you, and let's see if that meets your specifications.

Mufasa
(aka Dave of Sandy, Utah, USA)
“People may forget what you say, but they will never forget how you made them feel.

 

[CrystalizeCanada]

Hi,

I understand now regarding the rownum. Yes that filter is not going to work so I can see why Carp asked me now about the need for the order by clause.

If it helps here is what I would normally do in T-SQL. So in T-SQL it would be (something like):

SELECT EmpId, HireDate
(
SELECT TOP 1 EmpId
FROM Emp
WHERE Emp <> E.Emp and HireDate < E.HireDate
ORDER BY HireDate DESC
) AS PreviousEmp
FROM Emp E

Thanks for your help with this

Gordon BOCP
Crystalize

 

[SantaMufasa]

Gordon,

I'm sorry to be so dense, but could you please help my by posting sample rows of output that you want? Do you want, for example, all rows in EMP to display with each row's "Next Emp Hired" information printed on the same row, or do you want just a single row of output based upon some parameter that you are passing into the code?

From the T-SQL example, it appears that your result set is composed of all employees. Please confirm.

Mufasa
(aka Dave of Sandy, Utah, USA)
“People may forget what you say, but they will never forget how you made them feel.

 

[CrystalizeCanada]

Hi Mufasa,

Thanks for taking the time. Yes the result I would get would be all employees.

So this is what the source data looks like:

EmpId HireDate
Ringo 1/1/1982
Paul 1/1/1985
John 1/1/1988
George 1/1/1991
Mick 1/1/1994
Keith 1/1/1997

And this is the result I want:

EmpId HireDate PreviousEmpId
Ringo 1/1/1982
Paul 1/1/1985 Ringo
John 1/1/1988 Paul
George 1/1/1991 John
Mick 1/1/1994 George
Keith 1/1/1997 Mick

Hope this is clearer and I hope its possible to figure out the Prevous column as a subselect (inline).

Thanks again
Gordon

Gordon BOCP
Crystalize

 

[SantaMufasa]

Thanks, Gordon, for the followup. Your clarification simplifies the code enormously ! Additionally, it provides the opportunity to illustrate one of the uses of one of the powerful Oracle Analytics functions available to you:

select EmpId||' '||last_name EmpId
      ,Hiredate
      ,lead(EmpId||' '||last_name,1) over (order by HireDate desc) PreviousEmpID
  from Emp
/

EMPID                HIREDATE        PREVIOUSEMPID
-------------------- --------------- --------------
9 Catchpole          09-FEB-92       16 Maduro
16 Maduro            07-FEB-92       14 Nguyen
14 Nguyen            22-JAN-92       12 Giljum
12 Giljum            18-JAN-92       15 Dumas
15 Dumas             09-OCT-91       19 Patel
19 Patel             06-AUG-91       20 Newman
20 Newman            21-JUL-91       3 Nagayama
3 Nagayama           17-JUN-91       21 Markarian
21 Markarian         26-MAY-91       25 Schwartz
25 Schwartz          09-MAY-91       24 Dancs
24 Dancs             17-MAR-91       10 Havel
10 Havel             27-FEB-91       13 Sedeghi
13 Sedeghi           18-FEB-91       18 Nozaki
18 Nozaki            09-FEB-91       6 Urguhart
6 Urguhart           18-JAN-91       22 Chang
22 Chang             30-NOV-90       23 Patel
23 Patel             17-OCT-90       7 Menchu
7 Menchu             14-MAY-90       11 Magee
11 Magee             14-MAY-90       4 Quick-To-See
4 Quick-To-See       07-APR-90       8 Biri
8 Biri               07-APR-90       17 Smith
17 Smith             08-MAR-90       5 Ropeburn
5 Ropeburn           04-MAR-90       1 Velasquez
1 Velasquez          03-MAR-90       2 Ngao
2 Ngao               08-MAR-90

25 rows selected.

Now, as you mentioned, if you must use this code from an in-line view, then it is simple to make that happen:

select EmpID, Hiredate, PreviousEmpID
  from (select EmpId||' '||last_name EmpId
              ,Hiredate
              ,lead(EmpId||' '||last_name,1) over (order by HireDate desc) PreviousEmpID
          from Emp)
/

EMPID                HIREDATE        PREVIOUSEMPID
-------------------- --------------- --------------
9 Catchpole          09-FEB-92       16 Maduro
16 Maduro            07-FEB-92       14 Nguyen
14 Nguyen            22-JAN-92       12 Giljum
12 Giljum            18-JAN-92       15 Dumas
15 Dumas             09-OCT-91       19 Patel
19 Patel             06-AUG-91       20 Newman
20 Newman            21-JUL-91       3 Nagayama
3 Nagayama           17-JUN-91       21 Markarian
21 Markarian         26-MAY-91       25 Schwartz
25 Schwartz          09-MAY-91       24 Dancs
24 Dancs             17-MAR-91       10 Havel
10 Havel             27-FEB-91       13 Sedeghi
13 Sedeghi           18-FEB-91       18 Nozaki
18 Nozaki            09-FEB-91       6 Urguhart
6 Urguhart           18-JAN-91       22 Chang
22 Chang             30-NOV-90       23 Patel
23 Patel             17-OCT-90       7 Menchu
7 Menchu             14-MAY-90       11 Magee
11 Magee             14-MAY-90       4 Quick-To-See
4 Quick-To-See       07-APR-90       8 Biri
8 Biri               07-APR-90       17 Smith
17 Smith             08-MAR-90       5 Ropeburn
5 Ropeburn           04-MAR-90       1 Velasquez
1 Velasquez          03-MAR-90       2 Ngao
2 Ngao               08-MAR-90

25 rows selected.

Additionally, if you want the results sorted by some order besides the default HIREDATE order (e.g., alphabetical order), it's a very minor addition of the ORDER BY in either the in-line or the outer SELECT:

select EmpID, Hiredate, PreviousEmpID
  from (select EmpId||' '||last_name EmpId
              ,Hiredate
              ,lead(EmpId||' '||last_name,1) over (order by HireDate desc) PreviousEmpID
          from Emp
         order by last_name)
/

EMPID                HIREDATE        PREVIOUSEMPID
-------------------- --------------- ---------------
8 Biri               07-APR-90       17 Smith
9 Catchpole          09-FEB-92       16 Maduro
22 Chang             30-NOV-90       23 Patel
24 Dancs             17-MAR-91       10 Havel
15 Dumas             09-OCT-91       19 Patel
12 Giljum            18-JAN-92       15 Dumas
10 Havel             27-FEB-91       13 Sedeghi
16 Maduro            07-FEB-92       14 Nguyen
11 Magee             14-MAY-90       4 Quick-To-See
21 Markarian         26-MAY-91       25 Schwartz
7 Menchu             14-MAY-90       11 Magee
3 Nagayama           17-JUN-91       21 Markarian
20 Newman            21-JUL-91       3 Nagayama
2 Ngao               08-MAR-90
14 Nguyen            22-JAN-92       12 Giljum
18 Nozaki            09-FEB-91       6 Urguhart
23 Patel             17-OCT-90       7 Menchu
19 Patel             06-AUG-91       20 Newman
4 Quick-To-See       07-APR-90       8 Biri
5 Ropeburn           04-MAR-90       1 Velasquez
25 Schwartz          09-MAY-91       24 Dancs
13 Sedeghi           18-FEB-91       18 Nozaki
17 Smith             08-MAR-90       5 Ropeburn
6 Urguhart           18-JAN-91       22 Chang
1 Velasquez          03-MAR-90       2 Ngao

25 rows selected.

Let us know if this helps resolve your need.


Mufasa
(aka Dave of Sandy, Utah, USA)
“People may forget what you say, but they will never forget how you made them feel.

 

[CrystalizeCanada]

Hi Mufasa,

Thanks for the reply.

The lead and lag functions remind me of next and previous functions that I'm familiar with. Interesting functions - I am trying out and will get back to you shortly.

I don't have the option to use a subselect where you have used it in the second option which is with the FROM. My subselect has to go on the SELECT line as an SQL Expression.

So I'm trying the first option which is using the lead function as an SQL expression is the one I'm trying out on my data.

Thanks for the suggestion. Will get back to you once I've tested.

Gordon



Gordon BOCP
Crystalize

 

[CrystalizeCanada]

Hi Mufasa,

I tried but I don't think this will work for as there is one extra thing which is that the outer data may be fitlered.

The lead and lag functions give you the next and previous values based on the record set. If the record set was filtered to say one employee Lead or Lag with return nothing as there isn't a next or previous record.

select EmpId||' '||last_name EmpId
,Hiredate
,lead(EmpId||' '||last_name,1) over (order by HireDate desc) PreviousEmpID
from Emp

So if I added
WHERE EmpId = 8
to the end of the statement it wouldn't return the Previous employee.

And I still need it to return the previous employee as the user may end up filtering the report and a where clause may get added.

So I'm back to the drawing board I think on this one.

If you have any other suggestions that would be great but you have been very generous with your time.

Thanks and sorry about the cold Canadian winds blowing your way

Gordon

Gordon BOCP
Crystalize

 

[Dagon]

You might be able to use something like this:

select empid, hiredate, 
       (select empid from emp e2 
         where e2.hiredate = 
           (select max(e3.hiredate) from emp e3
              where e3.hiredate < e1.hiredate)) previousEmpid
from emp e1

It does contain a sub-select, but it's in column selection rather than the where clause.

To be honest, if you're using a tool that doesn't even permit sub-selects, you're going to struggle to do anything useful with Oracle. If the front-end tool is so useless that it stops you writing anything vaguely complicated, then you may have to resort to hiding the SQL from it by creating views.

 

[CrystalizeCanada]

Thanks Dagon,

Will test out and reply back. I see there is no order by clause so I am hopeful it will work.

Gordon

Gordon BOCP
Crystalize

 

[CrystalizeCanada]

Hi Dagon,

Works!

Tx

Gordon

Gordon BOCP
Crystalize