SQL syntax

[John Racars]

This is the SQL I use for my daily report:
SELECT DISTINCT Aircraft.Registration AS "Reg"
Flights.Callsign AS "Flight ID"
Flights.Route AS "Route"
Aircraft.AircraftTypeSmall AS "ICAO Type"
Aircraft.Airline AS "Airline"
Aircraft.AircraftTypeLong AS "Aircraft"
substr(Flights.EndTime,1,16) AS "Flight Ended"
Flights.StartAltitude AS "Start Alt"
Flights.EndAltitude AS "End Alt"
Flights.MsgCount AS "Count"
Aircraft.ModeS AS "Mode S"
FROM Aircraft LEFT OUTER JOIN Flights ON (Aircraft.ModeS=Flights.ModeS)
WHERE ((date('now','-1 day') = substr(Aircraft.LastTime,1,4)||"-"||substr(Aircraft.LastTime,6,2)||"-"||substr(Aircraft.LastTime,9,2)) AND (Flights.StartTime IS NULL)) OR ((date('now','-1 day') = substr(Flights.EndTime,1,4)||"-"||substr(Flights.EndTime,6,2)||"-"||substr(Flights.EndTime,9,2))) OR ((date('now','-1 day') = substr(Flights.StartTime,1,4)||"-"||substr(Flights.StartTime,6,2)||"-"||substr(Flights.StartTime,9,2)))
ORDER BY Aircraft.Registration,Flights.EndTime,Aircraft.ModeS

Hi All,

I fear that my question is snowed under of the big number of questions in the topic about the version 3.0. On the other hand I doubt or questions like this belongs there. So I will give it a new try with this new topic:

I just tried the above SQL that I get from, great helpfull, Tarbat.

Information: "Invalid token "Fllights" at position 1 of line 2."
SQL Error: near "Flights": syntax error

Any help will be very welcome!

[tarbat]

Try commas at the end of each line:

SELECT DISTINCT Aircraft.Registration AS "Reg",Flights.Callsign AS "Flight ID",Flights.Route AS "Route",Aircraft.AircraftTypeSmall AS "ICAO Type",Aircraft.Airline AS "Airline",Aircraft.AircraftTypeLong AS "Aircraft",substr(Flights.EndTime,1,16) AS "Flight Ended",Flights.StartAltitude AS "Start Alt",Flights.EndAltitude AS "End Alt",Flights.MsgCount AS "Count",Aircraft.ModeS AS "Mode S" FROM Aircraft LEFT OUTER JOIN Flights ON (Aircraft.ModeS=Flights.ModeS) WHERE ((date('now','-1 day') = substr(Aircraft.LastTime,1,4)||"-"||substr(Aircraft.LastTime,6,2)||"-"||substr(Aircraft.LastTime,9,2)) AND (Flights.StartTime IS NULL)) OR ((date('now','-1 day') = substr(Flights.EndTime,1,4)||"-"||substr(Flights.EndTime,6,2)||"-"||substr(Flights.EndTime,9,2))) OR ((date('now','-1 day') = substr(Flights.StartTime,1,4)||"-"||substr(Flights.StartTime,6,2)||"-"||substr(Flights.StartTime,9,2))) ORDER BY Aircraft.Registration,Flights.EndTime,Aircraft.ModeS;

[John Racars]

Try commas at the end of each line:

I did. Looks succesfully but:

1. the message "Invalid token 'date' at position ........

2. SQL made a report anyway.

3. This report shows the same problems (duplicates) as you could see in my yesterday report.

So, nothing helps at this moment to solve "my" problem at the moment. Very strange in my opinion because I never seen this in the previous version.

For me it is a big mystery. Preliminary for me no (reliable) reports. Tarbat, thank you again for all the help until sofar!

[tarbat]

I guess it depends what you're expecting to see in the daily log.  If you just want a list of aircraft and FlightIDs, try removing the Flight Ended, Start Alt, End Alt, and Count fields.

SELECT DISTINCT Aircraft.Registration AS "Reg",Flights.Callsign AS "Flight ID",Flights.Route AS "Route",Aircraft.AircraftTypeSmall AS "ICAO Type",Aircraft.Airline AS "Airline",Aircraft.AircraftTypeLong AS "Aircraft",Aircraft.ModeS AS "Mode S" FROM Aircraft LEFT OUTER JOIN Flights ON (Aircraft.ModeS=Flights.ModeS) WHERE ((date('now','-1 day') = substr(Aircraft.LastTime,1,4)||"-"||substr(Aircraft.LastTime,6,2)||"-"||substr(Aircraft.LastTime,9,2)) AND (Flights.StartTime IS NULL)) OR ((date('now','-1 day') = substr(Flights.EndTime,1,4)||"-"||substr(Flights.EndTime,6,2)||"-"||substr(Flights.EndTime,9,2))) OR ((date('now','-1 day') = substr(Flights.StartTime,1,4)||"-"||substr(Flights.StartTime,6,2)||"-"||substr(Flights.StartTime,9,2))) ORDER BY Aircraft.Registration,Flights.EndTime,Aircraft.ModeS

[John Racars]

No report at all in this case. Only the message "Invalid token 'date' at position...." and "SQL Error: near "FROM": syntax error".

BTW: what I expect to see in my report is very simple I think:

ALL flight movements in my area of reception during one day of 24h. including: Starttime, Callsign, Routing, Aircraftreg, ICAO type of aircraft. That is all I need....

[malc41]

Works OK at this end