Query in Oracle to select with subquery

Posted on

Query in Oracle to select with subquery – This article will take you through the common SQL errors that you might encounter while working with sql, oracle,  date. The wrong arrangement of keywords will certainly cause an error, but wrongly arranged commands may also be an issue. SQL keyword errors occur when one of the words that the SQL query language reserves for its commands and clauses is misspelled. If the user wants to resolve all these reported errors, without finding the original one, what started as a simple typo, becomes a much bigger problem.

SQL Problem :

I have the following table on my Oracle database:

 flight NUMBER(4),
 date   DATE,
 action VARCHAR2(50),
 CONSTRAINT pk PRIMARY KEY (flight,date)

and the following registers:

 INSERT INTO test VALUES ('1234', '2020-02-29 18:00', 'Departure');
 INSERT INTO test VALUES ('1234', '2020-02-29 19:00', 'Arrival');

 INSERT INTO test VALUES ('4321', '2020-02-20 22:00', 'Departure');
 INSERT INTO test VALUES ('4321', '2020-02-21 04:30', 'Arrival');

My problem is that I want to make a SELECT that returns me the flight number only when it has departed and arrived at the same day. For example, flight ‘1234’ has departed on day 29 and arrived on day 29, but in the other hand, the flight ‘4321’ has departed on day 20 and arrived on day 21. I only would like to select the flight ‘1234’, because it is the only one who meets the requeriments.

I have been thinking about the following select, but it doesn’t work because the subquery returns more than just one value:

SELECT flight
  FROM test
 WHERE action = 'Departure'
                                        FROM test
                                       WHERE action = 'Arrival');

Thank you so much.

Solution :

You just need distinct action within a day. So, use HAVING count distinctly of two individual actions GROUPed BY flight and day :

SELECT flight
  FROM test
 WHERE action in ('Departure','Arrival')
 GROUP BY flight, trunc("date")
HAVING COUNT(distinct action)=2

where there should be distinct clause, need is observed if the sample data set is extended


  • date is a reserved keyword and cannot be used as a column name for Oracle database. So, I preferred "date"
  • Prefix the time literals with timestamp keyword and add portion :00 at the end to represent second such as timestamp'2020-02-21 04:30:00' during the insertion

We don’t know, whether a flight number in your data model uniquely represents a flight or not. We also don’t know whether there can be any actions apart from ‘Departure’ and ‘Arrival’.

The following solutions look at a flight number in combination with the flight date and consider the two actions in question, so they work in any case:


select flight, trunc(date) from test where action = 'Departure'
select flight, trunc(date) from test where action = 'Arrival';

With aggregation:

select flight, trunc(date)
from test
group by flight, trunc(date)
having count(case when action = 'Departure' then 1 end) = 1
   and count(case when action = 'Arrival' then 1 end) = 1;

With COUNT() window function:

select distinct flight
from (
  select t.*, 
    count(distinct action) over (partition by flight, to_char("date", 'YYYY-MM-DD')) counter
  from test t
where counter = 2

See the demo.

> | FLIGHT |
> | -----: |
> |   1234 |

Yet another option is to use the join with flight number and date as follows:

SELECT distinct d.flight
  FROM test d JOIN test a 
    ON d.action = 'Departure'
    AND trunc(d.date) = trunc(a.date)
    AND a.action = 'Arrival'
    AND a.flight = d.flight;


You need the same table two time in join

SELECT t1.flight
FROM test t1
INNER JOIN test t2 ON t1.action = 'Departure'
    AND TO_CHAR(t1.date, 'YYYY-MM-DD')  = TO_CHAR(t2.date, 'YYYY-MM-DD')
    AND t2.action = 'Arrival'
    AND t1.flight =t2.flight

Or using the subquery ..use a proper FROM clause again

SELECT flight
FROM test
WHERE action = 'Departure'
AND (TO_CHAR(date, 'YYYY-MM-DD'), flight)  = (
    SELECT TO_CHAR(date, 'YYYY-MM-DD'), flight
    FROM test
    WHERE action = 'Arrival');        

A PIVOT statement could work too

FROM (SELECT flight, date, action
    FROM test)

      FOR ACTION IN ('Departure' AS DEP, 'Arrival' AS AR)

Finding SQL syntax errors can be complicated, but there are some tips on how to make it a bit easier. Using the aforementioned Error List helps in a great way. It allows the user to check for errors while still writing the project, and avoid later searching through thousands lines of code.

Leave a Reply

Your email address will not be published. Required fields are marked *