collapsing NULL values in Oracle query

Question

I often write queries wherein I pivot data and end up with NULL values that I want to collapse. E.g. data like the following:

id  time_in     time_out
1               2009-11-01
1   2009-10-30
2   2008-12-15
2               2009-02-03

I then do an outer query like so:

SELECT id,
       MIN(time_in) AS time_in,
       MIN(time_out) AS time_out
FROM (...query above...)
GROUP BY id

This would produce data like this:

id  time_in     time_out
1   2009-10-30  2009-11-01
2   2008-12-15  2009-02-03

The problem is that I use a limited interface to access the Oracle database, and queries using MIN and MAX often time out. I was wondering if there is a more efficient way of "collapsing" NULL values than what I have done. Sometimes I GROUP BY another field that is not an index, whereas id shown above is a primary key.

Jeffrey Kemp · Accepted Answer · 2009-11-19 12:52:04Z

1

You'll need to examine the plan for your statement to see why it's timing out - unless your grouping can use an index to get the rows in a guaranteed order, the query must retrieve all the source rows before it can return any result.

Another option to consider (which may or may not help):

SELECT DISTINCT
       id,
       MAX(time_in) OVER (PARTITION BY id),
       MAX(time_out) OVER (PARTITION BY id)
FROM (...)

answered Nov 19, 2009 at 12:52

Jeffrey Kemp

60.5k15 gold badges111 silver badges161 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

anon · Accepted Answer · 2009-11-18 21:56:50Z

1

SELECT a.id,
       a.time_in,
       b.time_out
FROM 
(
  SELECT id, time_out
  FROM (...query above...)
  WHERE time_in is not null
) a,
(
  SELECT id, time_out
  FROM (...query above...)
  WHERE time_out is not null
) b
WHERE a.id = b.id;

answered Nov 18, 2009 at 21:56

anon

Comments

Egor Rogov · Accepted Answer · 2009-11-19 08:08:32Z

0

If your query times out, the subquery probably returns many rows, which Oracle usually have to sort in order to group by. I suggest to inspect your subquery to see if you can avoid nulls there.

answered Nov 19, 2009 at 8:08

Egor Rogov

5,48828 silver badges40 bronze badges

Collectives™ on Stack Overflow

collapsing NULL values in Oracle query

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related