Hi I\'m trying to get names from two tables which is call \"Bookings\" and \"Wai

Question

Hi I'm trying to get names from two tables which is call "Bookings" and "WaitingList" . And I want to order the result by timestamp but I'm getting this error message.

java.sql.SQLSyntaxErrorException: Column 'TIMESTAMP' is not in the result of the query expression.
   at org.apache.derby.client.am.SQLExceptionFactory40.getSQLException(Unknown Source)
   at org.apache.derby.client.am.SqlException.getSQLException(Unknown Source)
   at org.apache.derby.client.am.Connection.prepareStatement(Unknown Source)
   at flightschedulersfs5208.Booking.<init>(Booking.java:85)
   at flightschedulersfs5208.FlightSchedulersfs5208.<init>(FlightSchedulersfs5208.java:133)
   at flightschedulersfs5208.FlightSchedulersfs5208.main(FlightSchedulersfs5208.java:798)
Caused by: org.apache.derby.client.am.SqlException: Column 'TIMESTAMP' is not in the result of the query expression.
   at org.apache.derby.client.am.Statement.completeSqlca(Unknown Source)
   at org.apache.derby.client.net.NetStatementReply.parsePrepareError(Unknown Source)
   at org.apache.derby.client.net.NetStatementReply.parsePRPSQLSTTreply(Unknown Source)
   at org.apache.derby.client.net.NetStatementReply.readPrepareDescribeOutput(Unknown Source)
   at org.apache.derby.client.net.StatementReply.readPrepareDescribeOutput(Unknown Source)
   at org.apache.derby.client.net.NetStatement.readPrepareDescribeOutput_(Unknown Source)
   at org.apache.derby.client.am.Statement.readPrepareDescribeOutput(Unknown Source)

This is the code

selectNames = connection.prepareStatement("SELECT name FROM Bookings where flight = ? UNION SELECT name FROM waitingList where flight = ? ORDER BY timestamp DESC");

Tables 76 BOOKINGS get Flight Seats select count (flight) from Bookings where flight 77 connection preparestatement and date BooKINGID 78 selectAll People connection .preparestatement SELECT FROM Booking where flight and date select Bookings connection preparestatement SELECT FROM Bookings where name 79 select Booking SELECT flight FROM Bookings where name and date 80 connection prepa restatement FLIGHT select Booking Id connection Statement SELECT booking id FROM Bookings where name and date 81 insert Booking connection prepa restatement INSERT INTO Bookings Name, Flight,Date, time Stamp 82 VALUES DATE remove Booking prepare Statement DELETE FROM Bookings where name 83 connection and date El TIMESTAMP update Booking connection prepare Statement UPDATE Bookings set nam where BookingId and flight 84 Indexes selectNames connection prepare Statement SELECT name FROM Bookings where flight 85 UNION SELECT name FROM w Foreign Keys select Flights connection SELECT flight FROM Bookings where date 86 prepare Statement WAITINGLIST 8 wAITLISTID. 88 89 catch (SQLException sq LException) NAME 90 sqlException. printStackTrace System. exit (1) BE DATE 92 93 TIMESTAMP Indexes Previous Next Find Select Foreign Keys Views Output Procedures Flight Schedul (run Java DB Database Process SQL 2 execution Other schemas jdbc: derby://localhost:152 run ava.sgl.SQLSyntaxErrorException: Column 'TIMESTAMP n the result of the query expression is not i Servers at org.apache .derby. client.am.SQLException Factory40.getSQLException (Unknown Source Maven Repositories at org.apache.derby.client.am.SquException.getSQLException (Unknown Source) Cloud ipn. preparestatement (Unknown Source at org.apache.derby.client.am. Connect Hudson Builders at flightschedulersfs5208. Booking. Kinit (Bookin ava: 85 Docker at flightschedulersfs5208. FlightSchedulersfs5208.

Explanation / Answer

Ans-

As you are combining (Union ) of two table query into one you are not giving any reference of timstamp in order by.

selectNames = connection.prepareStatement("SELECT name FROM Bookings where flight = ? UNION SELECT name FROM waitingList where flight = ? ORDER BY timestamp DESC");

in this you didn't write the timestamp in the result list

selectNames = connection.prepareStatement("SELECT name,timestamp FROM Bookings where flight = ? UNION SELECT name,timestamp FROM waitingList where flight = ? ORDER BY timestamp DESC");

now as you are using union so which table reference you are using for the order by like table booking or table waiting list

you have to mention this also

if using booking table reference

selectNames = connection.prepareStatement("SELECT b.name,b.timestamp FROM Bookings b where b.flight = ? UNION SELECT w.name,w.timestamp FROM waitingList w where w.flight = ? ORDER BY b.timestamp DESC");

if using waiting list table reference

selectNames = connection.prepareStatement("SELECT b.name,b.timestamp FROM Bookings b where b.flight = ? UNION SELECT w.name,w.timestamp FROM waitingList w where w.flight = ? ORDER BY w.timestamp DESC");

Depend on condition to fetch you can use either of three solution.

Related Questions

Navigate

• Browse (All)
• Browse H
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.