CODEWITHSUNDEEP
sqloracle-database
Sandra
Sandra

Reputation: 1

Oracle SQL view

I have created this views but I can't get it to work in my database. Does this look alright? I have to provide this views see below. All flight reservations made by John Smith including, for those flights that have flown, the duration of the flight.

CREATE VIEW ViewA AS
SELECT F.FlightID, (F.ArrivalTime-F.DepartTime) As FlightDuration
FROM FLIGHT as F
INNER JOIN RESERVATION as R
ON A.FlightID = R.FlightID
INNER JOIN CUSTOMER as C
ON C.CustomerID = R.CustomerID
WHERE F.DepartTime < Convert(Time, GetDate())
AND C.FirstName = ‘John’ 
AND C.LastName = ‘Smith’;

If I run this is says SQL command not properly ended! What am I doing wrong please help?

Upvotes: 0

Views: 42

Answers (2)

Jack D
Jack D

Reputation: 183

You have an error in your SQL syntax; check the manual that corresponds to your SQL server version for the right syntax to use near

'GetDate()) AND C.FirstName = ‘ John ’ AND C.LastName = ‘ Smith ’'

May I know your SQL database version.

Upvotes: 0

Littlefoot
Littlefoot

Reputation: 142705

I guess you'd rather

CREATE OR REPLACE VIEW viewa
AS
   SELECT f.flightid, (f.arrivaltime - f.departtime) AS flightduration
     FROM flight f
          INNER JOIN reservation r ON a.flightid = r.flightid
          INNER JOIN customer c ON c.customerid = r.customerid
    WHERE     f.departtime < SYSDATE
          AND c.firstname = 'John'
          AND c.lastname = 'Smith';

because

  • table alias - in Oracle - doesn't support the as keyword
  • you should get rid of fancy single quotes and use straight ones
  • use supported function(s) - there's no getdate in Oracle, and convert is used for different purpose. I believe it is the sysdate you need

Upvotes: 2

Related Questions