WHERE clause in nested select

Question

So I'm having a bit of a problem with a nested select I'm trying to do on a table of data.

I have a number of books across a number of libraries. Each book has an ID and each location a name. Every time a book is taken out or returned, a transaction is recorded on a transaction table.

The table looks a little like this:

+---------+--------+----------+------------+
| BOOK_ID | IN_OUT | LOCATION |    DATE    |
+---------+--------+----------+------------+
| B01     | O      | RED      | 2013-10-04 |
| B02     | O      | BLUE     | 2013-10-04 |
| B01     | I      | RED      | 2013-10-19 |
| B01     | O      | RED      | 2013-10-20 |
| B02     | I      | RED      | 2013-10-21 |
| B01     | I      | BLUE     | 2013-10-24 |
+---------+--------+----------+------------+

(Thanks, @Senseful!)

Right now, I can show all transactions and their in_time, out_time displayed in pairs:

SELECT i.BOOK_ID
                  ,(SELECT MAX(o.DATE)
                      FROM TRANSACTIONS o
                     WHERE (o.IN_OUT = 'O')
                       AND o.BOOK_ID = i.BOOK_ID
                       AND o.DATE < i.DATE
               ) AS out_time
  , i.DATE AS in_time
  , LOCATION
  FROM TRANSACTIONS i
 WHERE i.IN_OUT = 'I'
 ORDER BY i.DATE
;

Now, however, I'm wanting to only display transactions where books are being returned to a different location: so in the example above, I've tried selecting LOCATION as out_location with the intention of adding a WHERE clause to check if it is equal to in_location.

Fair enough:

,(SELECT MAX(o.DATE), LOCATION as out_location
                      FROM TRANSACTIONS o

No can do. "ORA-00913: too many values".

Any ideas on what would be the most sensible way of limiting the output like this?

You can get results using analyitic functions. Check this question for an example: http://stackoverflow.com/questions/17824432/oracle-analytic-functions-resetting-a-windowing-clause — Alen Oblak, Dec 04 '13 at 11:54
You could add `AND o.location <> i.location` to your inner select, but that doesn't give you the current location of the book. — SQB, Dec 04 '13 at 12:05
@SQB Right, that's looking a bit more promising... evaluates correctly, giving me a `(null)` on the `out_time` as expected. Adding `where ... out_time is not null` on outer select though gives `ORA-00904: "i"."out_time": invalid identifier` - doesn't 'see' that out_time column then? — Ron, Dec 04 '13 at 12:20

score 1 · Accepted Answer · answered Dec 04 '13 at 12:10

1

Analyitic functions are great for this kind of queries. You can use LEAD or LAG to get the previous or next row in a group.

I created an SQLFiddle example for you: http://www.sqlfiddle.com/#!4/725c1/14

The resulting select statement is this:

select *
from (
   SELECT i.BOOK_ID
   , i.in_out
   , i.event_date AS in_time
   , LOCATION in_location
   , lag(event_date) over (partition by book_id order by event_date) out_date
   , lag(location) over (partition by book_id order by event_date) out_location
   FROM TRANSACTIONS i
   ORDER BY i.event_date)
where in_out = 'I'
and   in_location != out_location;

answered Dec 04 '13 at 12:10

Alen Oblak

3,285
13
27

Amazing. I now have yet another thing to go read about... analytic functions! Thank you @Alen. – Ron Dec 04 '13 at 12:26
1

This is me coming back to thank you again. I've just used it in another context, completely replacing a behemoth of a statement I had. This is beautiful. I feel like a new man. Life is good. – Ron Dec 04 '13 at 12:40
He he, I'm glad you like it. – Alen Oblak Dec 04 '13 at 12:44

WHERE clause in nested select

1 Answers1