Oracle SQL view-CodePudding

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?

CodePudding user response：

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

CodePudding user response：

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.