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:
CREATE TABLE test ( flight NUMBER(4), date DATE, action VARCHAR2(50), CONSTRAINT pk PRIMARY KEY (flight,date) );
and the following registers:
BEGIN 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'); END; /
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' AND TO_CHAR(date, 'YYYY-MM-DD') = (SELECT TO_CHAR(date, 'YYYY-MM-DD') FROM test WHERE action = 'Arrival');
Thank you so much.
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
dateis a reserved keyword and cannot be used as a column name for Oracle database. So, I preferred
- Prefix the time literals with
timestampkeyword and add portion
:00at the end to represent
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' intersect select flight, trunc(date) from test where action = 'Arrival';
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;
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
SELECT * FROM (SELECT flight, date, action FROM test) PIVOT ( MAX(DATE) FOR ACTION IN ('Departure' AS DEP, 'Arrival' AS AR) )WHERE TRUNC(DEP) = TRUNC(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.