Lazarus
Programming => Databases => Topic started by: madref on March 17, 2015, 05:42:49 pm
-
I have created a few tables and one of them is:
// tbl_Wedstrijden
cSQL := '';
cSQL := cSQL + 'CREATE TABLE tbl_Wedstrijden ';
cSQL := cSQL + '(';
cSQL := cSQL + 'Wed_ID Word,';
cSQL := cSQL + 'Wed_Seizoen_ID Word,';
cSQL := cSQL + 'Wed_Datum Date,';
cSQL := cSQL + 'Wed_Tijd Time,';
cSQL := cSQL + 'Wed_Plaats Word,';
cSQL := cSQL + 'Wed_TeamA Word,';
cSQL := cSQL + 'Wed_TeamB Word,';
cSQL := cSQL + 'Wed_Scheids_1 Word,';
cSQL := cSQL + 'Wed_Scheids_2 Word,';
cSQL := cSQL + 'Wed_Scheids_3 Word,';
cSQL := cSQL + 'Wed_Scheids_4 Word,';
cSQL := cSQL + 'Wed_Divisie Word,';
cSQL := cSQL + 'Wed_Begeleiding Boolean,';
cSQL := cSQL + 'Wed_SPV_ID Word,';
cSQL := cSQL + 'Wed_Opmerkingen varchar, ';
cSQL := cSQL + 'Wed_Meetellen Boolean,';
cSQL := cSQL + 'Wed_Show Boolean,';
cSQL := cSQL + 'Wed_Outlook Boolean';
cSQL := cSQL + ');';
Now i am trying to create query's (views). The views qry_Teams and qry_Scheidsrechters have been made the same way.
But this one gives an error.
Project ... raised exception class 'EDatabaseError' with message: no such column tbl_Wedstrijden.Wed_ID
// qry_Overzicht_Wedstrijden
cSQL := '';
cSQL := 'SELECT '+
'tbl_Wedstrijden.Wed_ID, tbl_Seizoenen.SEI_ID AS Seizoen, Wed_Datum AS Datum, ' +
'Wed_Tijd AS Tijd, tbl_Divisie.Divisie_Tekst AS Divisie, qry_Teams.Team AS [Team A], ' +
'qry_Teams_1.Team AS [Team B],qry_Scheidsrechters.Scheidsrechter AS [Scheidsrechter 1], ' +
'qry_Scheidsrechters_1.Scheidsrechter AS [Scheidsrechter 2], ' +
'qry_Scheidsrechters_2.Scheidsrechter AS [Scheidsrechter 3], ' +
'qry_Scheidsrechters_3.Scheidsrechter AS [Scheidsrechter 4], ' +
'CASE WHEN Wed_Begeleiding THEN "x" ELSE NULL END AS B, ' +
'CASE WHEN Wed_Meetellen THEN "x" ELSE NULL END AS M, ' +
'CASE WHEN Wed_Show THEN "x" ELSE NULL END AS W, ' +
'CASE WHEN Wed_Outlook THEN "x" ELSE NULL END AS O, ' +
'tbl_Wedstrijden.Wed_Scheids_1, tbl_Wedstrijden.Wed_Scheids_2, ' +
'tbl_Wedstrijden.Wed_Scheids_3, tbl_Wedstrijden.Wed_Scheids_4, ' +
'tbl_Wedstrijden.Wed_Divisie AS Div, tbl_Wedstrijden.Wed_Meetellen, ' +
'tbl_Wedstrijden.Wed_TeamA, tbl_Wedstrijden.Wed_TeamB, tbl_Wedstrijden.Wed_Show, ' +
'qry_Teams.Team_Plaats AS Speelplaats, tbl_Wedstrijden.Wed_Plaats, ' +
'tbl_IJsbanen.IJS_Plaats, tbl_Wedstrijden.Wed_Datum, tbl_Wedstrijden.Wed_Tijd, ' +
'tbl_IJsbanen.IJS_Kilometers, tbl_Wedstrijden.Wed_Opmerkingen, ' +
'2*[IJS_Kilometers]*0.28 AS Euro, tbl_Divisie.Divisie_Vergoeding, ' +
'tbl_IJsbanen.IJS_Parkeren ' +
'FROM ' +
'tbl_Divisie INNER JOIN (tbl_Seizoenen INNER JOIN ((tbl_IJsbanen ' +
'INNER JOIN ((((qry_Teams AS qry_Teams_1 INNER JOIN (qry_Teams ' +
'INNER JOIN tbl_Wedstrijden ON qry_Teams.Team_ID = tbl_Wedstrijden.Wed_TeamA) ' +
'ON qry_Teams_1.Team_ID = tbl_Wedstrijden.Wed_TeamB) ' +
'LEFT JOIN qry_Scheidsrechters ON tbl_Wedstrijden.Wed_Scheids_1 = qry_Scheidsrechters.Scheids_ID) ' +
'LEFT JOIN qry_Scheidsrechters AS qry_Scheidsrechters_1 ON ' +
'tbl_Wedstrijden.Wed_Scheids_2 = qry_Scheidsrechters_1.Scheids_ID) ' +
'LEFT JOIN qry_Scheidsrechters AS qry_Scheidsrechters_2 ON ' +
'tbl_Wedstrijden.Wed_Scheids_3 = qry_Scheidsrechters_2.Scheids_ID) ' +
'ON tbl_IJsbanen.IJS_ID = tbl_Wedstrijden.Wed_Plaats) ' +
'LEFT JOIN qry_Scheidsrechters AS qry_Scheidsrechters_3 ON ' +
'tbl_Wedstrijden.Wed_Scheids_4 = qry_Scheidsrechters_3.Scheids_ID) ON ' +
'tbl_Seizoenen.SEI_ID = tbl_Wedstrijden.Wed_Seizoen_ID) ' +
'ON tbl_Divisie.Divisie_ID = tbl_Wedstrijden.Wed_Divisie ' +
'ORDER BY ' +
'tbl_Seizoenen.SEI_ID DESC , tbl_Wedstrijden.Wed_Datum DESC , ' +
'tbl_Wedstrijden.Wed_Tijd DESC';
SQLite3Connection_RefereeDB.ExecuteDirect('' +
'CREATE VIEW IF NOT EXISTS qry_Overzicht_Wedstrijden AS ' + cSQL);
What am i doing wrong.
-
First recommendation is to put the queries in a resource file or even a simple textfile, in the way they are supposed to be executed.
Then you can read the textfile in a TStringList and use executedirect on the text in the stringlist.
That makes all much easier to debug, test and if necessary modify.
That way you can also use indentation to make the query understandable.
-
Which SQL server supports double quotes for strings instead of single quotes? replace all "x" with ''x'' first then please specify the sql server you are using to see if we can debuf it for you.
-
Which SQL server supports double quotes for strings instead of single quotes? replace all "x" with ''x'' first then please specify the sql server you are using to see if we can debuf it for you.
Based on the code I suspect SQLite.
-
This is already a textfile which i include with the {$Include...}-directive.
And yes it's SQLite, which is supported on ALL machines regardless of OS
And if you are wondering where it comes from an app i have written for the iPad in Embarcadero Delphi XE7.
Now i am making the same app for Mac OS X. But i am doing this for the learning curve on the Mac version of Lazarus.
This code works (some what adjusted) very well in Delphi XE7.
But still no answer to my question :(
-
This code works (some what adjusted) very well in Delphi XE7.
But still no answer to my question :(
There must be error in your SQL. (error you receive is IMO primary returned by SQLite not by Lazarus or FPC)
Try extract SQL and run it against SQLite using SQLite command line utility or any management tool
-
There is nothing wrong with the SQL-code. This works fine.
cSQL is defined as a String and I thing Lazarus has a max of 255 characters for a string.
Is there an other way to tackle this problem?
-
Nope, not Lazarus. The underlying compiler : FPC. AnsiString (which is enabled by default) have limit of 2^32-1 characters. ShortString does have limit of 255 characters.
-
Then why isn't it working....
I am at a total loss
-
The mode (http://www.freepascal.org/docs-html/prog/progsu105.html#x113-1140001.3.22) you are using affects the meaning of string. Make sure to add {$H+} (http://www.freepascal.org/docs-html/prog/progsu25.html#x32-310001.2.25) on the top of your file.
-
First, please make sure that the table tbl_Wedstrijden exists in the SQLite database file you are actually working with.
Check also that column 'Wed_ID' really exists in table 'tbl_Wedstrijden'? I think you can get messages like "No column" if you work on an empty database or missing tables. SQLite autocreates empty db files on Connection.Open.
I recreated tbl_Wedstrijden using the code you showed and then created a View using only that table. (Since I dont have your other tables) ->
cSQL := 'SELECT tbl_Wedstrijden.Wed_ID FROM tbl_Wedstrijden';
Conn.ExecuteDirect('CREATE VIEW qry_Overzicht_Wedstrijden AS ' + cSQL);
TX.Commit;
There was no complaint about missing column tbl_Wedstrijden.Wed_ID. If I try create a View with your full original statement, that does not give an error message about Wed_ID either. It does complain about tbl_Divisie, but thats because that table is missing for me. Since tbl_Wedstrijden.Wed_ID is used only once and it works here, you might have mixed up the content of you db file?
You could also show us the code to create the other tables so we can run the whole thing.
-
These are the tables I use
// tbl_Wedstrijden
cSQL := '';
cSQL := cSQL + 'CREATE TABLE tbl_Wedstrijden ';
cSQL := cSQL + '(';
cSQL := cSQL + 'Wed_ID Word,';
cSQL := cSQL + 'Wed_Seizoen_ID Word,';
cSQL := cSQL + 'Wed_Datum Date,';
cSQL := cSQL + 'Wed_Tijd Time,';
cSQL := cSQL + 'Wed_Plaats Word,';
cSQL := cSQL + 'Wed_TeamA Word,';
cSQL := cSQL + 'Wed_TeamB Word,';
cSQL := cSQL + 'Wed_Scheids_1 Word,';
cSQL := cSQL + 'Wed_Scheids_2 Word,';
cSQL := cSQL + 'Wed_Scheids_3 Word,';
cSQL := cSQL + 'Wed_Scheids_4 Word,';
cSQL := cSQL + 'Wed_Divisie Word,';
cSQL := cSQL + 'Wed_Begeleiding Boolean,';
cSQL := cSQL + 'Wed_SPV_ID Word,';
cSQL := cSQL + 'Wed_Opmerkingen varchar, ';
cSQL := cSQL + 'Wed_Meetellen Boolean,';
cSQL := cSQL + 'Wed_Show Boolean,';
cSQL := cSQL + 'Wed_Outlook Boolean';
cSQL := cSQL + ');';
SQLite3Connection_RefereeDB.ExecuteDirect(cSQL);
// tbl_Divisies
cSQL := '';
cSQL := cSQL + 'CREATE TABLE tbl_Divisie ';
cSQL := cSQL + '(';
cSQL := cSQL + 'Divisie_ID Word,';
cSQL := cSQL + 'Divisie_Tekst varchar(25),';
cSQL := cSQL + 'Divisie_Gebruiken Boolean,';
cSQL := cSQL + 'Divisie_Volgorde Word,';
cSQL := cSQL + 'Divisie_Voluit varchar(75),';
cSQL := cSQL + 'Divisie_Vergoeding Float';
cSQL := cSQL + ');';
SQLite3Connection_RefereeDB.ExecuteDirect(cSQL);
// tbl_Evaluatie_Linesman
cSQL := '';
cSQL := cSQL + 'CREATE TABLE tbl_Evaluatie_Linesman ';
cSQL := cSQL + '(';
cSQL := cSQL + 'Evo_ID Word,';
cSQL := cSQL + 'Evo_Scheids_ID Word,';
cSQL := cSQL + 'Evo_Wed_ID Word,';
cSQL := cSQL + 'Evo_Systeem Word,';
cSQL := cSQL + 'Evo_SoortWedstrijd Word,';
cSQL := cSQL + 'Evo_Supervisor Word,';
cSQL := cSQL + 'Evo_Goal_TeamA_Per1 Word,';
cSQL := cSQL + 'Evo_Goal_TeamA_Per2 Word,';
cSQL := cSQL + 'Evo_Goal_TeamA_Per3 Word,';
cSQL := cSQL + 'Evo_Goal_TeamA_Per4 Word,';
cSQL := cSQL + 'Evo_Goal_TeamB_Per1 Word,';
cSQL := cSQL + 'Evo_Goal_TeamB_Per2 Word,';
cSQL := cSQL + 'Evo_Goal_TeamB_Per3 Word,';
cSQL := cSQL + 'Evo_Goal_TeamB_Per4 Word,';
cSQL := cSQL + 'Evo_Straf_TeamA_Per1 Word,';
cSQL := cSQL + 'Evo_Straf_TeamA_Per2 Word,';
cSQL := cSQL + 'Evo_Straf_TeamA_Per3 Word,';
cSQL := cSQL + 'Evo_Straf_TeamA_Per4 Word,';
cSQL := cSQL + 'Evo_Straf_TeamB_Per1 Word,';
cSQL := cSQL + 'Evo_Straf_TeamB_Per2 Word,';
cSQL := cSQL + 'Evo_Straf_TeamB_Per3 Word,';
cSQL := cSQL + 'Evo_Straf_TeamB_Per4 Word,';
cSQL := cSQL + 'Evo_Fitheid Word,';
cSQL := cSQL + 'Evo_Tekens Word,';
cSQL := cSQL + 'Evo_Positie_Eindvak Word,';
cSQL := cSQL + 'Evo_FaceOffs Word,';
cSQL := cSQL + 'Evo_GameManagement Word,';
cSQL := cSQL + 'Evo_Aanwezigheid Word,';
cSQL := cSQL + 'Evo_Oplettendheid Word,';
cSQL := cSQL + 'Evo_Communicatie Word,';
cSQL := cSQL + 'Evo_Teamwork Word,';
cSQL := cSQL + 'Evo_Standaard_Per1 Word,';
cSQL := cSQL + 'Evo_Standaard_Per2 Word,';
cSQL := cSQL + 'Evo_Standaard_Per3 Word,';
cSQL := cSQL + 'Evo_Standaard_Per4 Word,';
cSQL := cSQL + 'Evo_Straffen_Gemist Word,';
cSQL := cSQL + 'Evo_Straffen_Cheap Word,';
cSQL := cSQL + 'Evo_Icings_Gemist Word,';
cSQL := cSQL + 'Evo_Icings_Twijfel Word,';
cSQL := cSQL + 'Evo_SterkePunten varchar,';
cSQL := cSQL + 'Evo_VerbeterPunten varchar,';
cSQL := cSQL + 'Evo_Feedback varchar';
cSQL := cSQL + ');';
SQLite3Connection_RefereeDB.ExecuteDirect(cSQL);
// tbl_Evaluatie_Referee
cSQL := '';
cSQL := cSQL + 'CREATE TABLE tbl_Evaluatie_Referee ';
cSQL := cSQL + '(';
cSQL := cSQL + 'Evo_ID Word,';
cSQL := cSQL + 'Evo_Scheids_ID Word,';
cSQL := cSQL + 'Evo_Wed_ID Word,';
cSQL := cSQL + 'Evo_Systeem Word,';
cSQL := cSQL + 'Evo_SoortWedstrijd Word,';
cSQL := cSQL + 'Evo_Supervisor Word,';
cSQL := cSQL + 'Evo_Goal_TeamA_Per1 Word,';
cSQL := cSQL + 'Evo_Goal_TeamA_Per2 Word,';
cSQL := cSQL + 'Evo_Goal_TeamA_Per3 Word,';
cSQL := cSQL + 'Evo_Goal_TeamA_Per4 Word,';
cSQL := cSQL + 'Evo_Goal_TeamB_Per1 Word,';
cSQL := cSQL + 'Evo_Goal_TeamB_Per2 Word,';
cSQL := cSQL + 'Evo_Goal_TeamB_Per3 Word,';
cSQL := cSQL + 'Evo_Goal_TeamB_Per4 Word,';
cSQL := cSQL + 'Evo_Straf_TeamA_Per1 Word,';
cSQL := cSQL + 'Evo_Straf_TeamA_Per2 Word,';
cSQL := cSQL + 'Evo_Straf_TeamA_Per3 Word,';
cSQL := cSQL + 'Evo_Straf_TeamA_Per4 Word,';
cSQL := cSQL + 'Evo_Straf_TeamB_Per1 Word,';
cSQL := cSQL + 'Evo_Straf_TeamB_Per2 Word,';
cSQL := cSQL + 'Evo_Straf_TeamB_Per3 Word,';
cSQL := cSQL + 'Evo_Straf_TeamB_Per4 Word,';
cSQL := cSQL + 'Evo_Fitheid Word,';
cSQL := cSQL + 'Evo_Tekens Word,';
cSQL := cSQL + 'Evo_Positie_Eindvak Word,';
cSQL := cSQL + 'Evo_Positie_Spel Word,';
cSQL := cSQL + 'Evo_GameManagement Word,';
cSQL := cSQL + 'Evo_Aanwezigheid Word,';
cSQL := cSQL + 'Evo_Oplettendheid Word,';
cSQL := cSQL + 'Evo_Communicatie Word,';
cSQL := cSQL + 'Evo_Teamwork Word,';
cSQL := cSQL + 'Evo_Standaard_Per1 Word,';
cSQL := cSQL + 'Evo_Standaard_Per2 Word,';
cSQL := cSQL + 'Evo_Standaard_Per3 Word,';
cSQL := cSQL + 'Evo_Standaard_Per4 Word,';
cSQL := cSQL + 'Evo_Straffen_Gemist Word,';
cSQL := cSQL + 'Evo_Straffen_Cheap Word,';
cSQL := cSQL + 'Evo_Icings_Gemist Word,';
cSQL := cSQL + 'Evo_Icings_Twijfel Word,';
cSQL := cSQL + 'Evo_SterkePunten varchar,';
cSQL := cSQL + 'Evo_VerbeterPunten varchar,';
cSQL := cSQL + 'Evo_Feedback varchar';
cSQL := cSQL + ');';
SQLite3Connection_RefereeDB.ExecuteDirect(cSQL);
// tbl_IJsbanen
cSQL := '';
cSQL := cSQL + 'CREATE TABLE tbl_IJsbanen ';
cSQL := cSQL + '(';
cSQL := cSQL + 'IJS_ID Word,';
cSQL := cSQL + 'IJS_Plaats varchar(50),';
cSQL := cSQL + 'IJS_Kilometers Word,';
cSQL := cSQL + 'IJS_Naam varchar(50),';
cSQL := cSQL + 'IJS_Parkeren Float';
cSQL := cSQL + ');';
SQLite3Connection_RefereeDB.ExecuteDirect(cSQL);
// tbl_Licenties aanmaken
cSQL := '';
cSQL := cSQL + 'CREATE TABLE tbl_Licenties ';
cSQL := cSQL + '(';
cSQL := cSQL + 'Fluit_ID Word,';
cSQL := cSQL + 'Fluit_Seizoen_ID Word,';
cSQL := cSQL + 'Fluit_Scheids_ID Word,';
cSQL := cSQL + 'Fluit_Licentie_ID Word,';
cSQL := cSQL + 'Fluit_Cursus Boolean,';
cSQL := cSQL + 'Fluit_Opmerking varchar,';
cSQL := cSQL + 'Fluit_Vergoeding Boolean,';
cSQL := cSQL + 'Fluit_Bedrag Float';
cSQL := cSQL + ');';
SQLite3Connection_RefereeDB.ExecuteDirect(cSQL);
// tbl_Licenties_Keuze aanmaken
cSQL := '';
cSQL := cSQL + 'CREATE TABLE tbl_Licenties_Keuze ';
cSQL := cSQL + '(';
cSQL := cSQL + 'Licentie_ID Word,';
cSQL := cSQL + 'Licentie varchar(10),';
cSQL := cSQL + 'Licentie_Uitleg varchar(250)';
cSQL := cSQL + ');';
SQLite3Connection_RefereeDB.ExecuteDirect(cSQL);
// tbl_Licenties_Keuze vullen met de juiste licenties
cSQL := '';
cSQL := cSQL + 'INSERT INTO tbl_Licenties_Keuze ';
cSQL := cSQL + 'VALUES (1,"-","Geen licentie aangevraagd")';
SQLite3Connection_RefereeDB.ExecuteDirect(cSQL);
cSQL := '';
cSQL := cSQL + 'INSERT INTO tbl_Licenties_Keuze ';
cSQL := cSQL + 'VALUES (2,"A","Ere Divisie")';
SQLite3Connection_RefereeDB.ExecuteDirect(cSQL);
cSQL := '';
cSQL := cSQL + 'INSERT INTO tbl_Licenties_Keuze ';
cSQL := cSQL + 'VALUES (3,"B","Eerste Divisie")';
SQLite3Connection_RefereeDB.ExecuteDirect(cSQL);
cSQL := '';
cSQL := cSQL + 'INSERT INTO tbl_Licenties_Keuze ';
cSQL := cSQL + 'VALUES (4,"C","U-20")';
SQLite3Connection_RefereeDB.ExecuteDirect(cSQL);
cSQL := '';
cSQL := cSQL + 'INSERT INTO tbl_Licenties_Keuze ';
cSQL := cSQL + 'VALUES (5,"D","U-17")';
SQLite3Connection_RefereeDB.ExecuteDirect(cSQL);
cSQL := '';
cSQL := cSQL + 'INSERT INTO tbl_Licenties_Keuze ';
cSQL := cSQL + 'VALUES (6,"E","U-14")';
SQLite3Connection_RefereeDB.ExecuteDirect(cSQL);
cSQL := '';
cSQL := cSQL + 'INSERT INTO tbl_Licenties_Keuze ';
cSQL := cSQL + 'VALUES (7,"F","U-12")';
SQLite3Connection_RefereeDB.ExecuteDirect(cSQL);
cSQL := '';
cSQL := cSQL + 'INSERT INTO tbl_Licenties_Keuze ';
cSQL := cSQL + 'VALUES (8,"G","Mini/Welpen")';
SQLite3Connection_RefereeDB.ExecuteDirect(cSQL);
// tbl_Scheidsrechters aanmaken
cSQL := '';
cSQL := cSQL + 'CREATE TABLE tbl_Scheidsrechters ';
cSQL := cSQL + '(';
cSQL := cSQL + 'Scheids_ID Word,';
cSQL := cSQL + 'Voornaam varchar(20),';
cSQL := cSQL + 'Tussenvoegsel varchar(10),';
cSQL := cSQL + 'Achternaam varchar(50),';
cSQL := cSQL + 'Adres varchar(60),';
cSQL := cSQL + 'Postcode varchar(6),';
cSQL := cSQL + 'Woonplaats varchar(40),';
cSQL := cSQL + 'Telefoon varchar(11),';
cSQL := cSQL + 'Mobiel varchar(11),';
cSQL := cSQL + 'Geboortedatum date,';
cSQL := cSQL + 'Email varchar(250)';
cSQL := cSQL + ');';
SQLite3Connection_RefereeDB.ExecuteDirect(cSQL);
// tbl_Seizoenen
cSQL := '';
cSQL := cSQL + 'CREATE TABLE tbl_Seizoenen ';
cSQL := cSQL + '(';
cSQL := cSQL + 'Sei_ID Word,';
cSQL := cSQL + 'SEI_Periode varchar(9),';
cSQL := cSQL + 'Sei_Van Word,';
cSQL := cSQL + 'Sei_Tot Word,';
cSQL := cSQL + 'Sei_Selected Boolean,';
cSQL := cSQL + 'Sei_Letter Char';
cSQL := cSQL + ');';
SQLite3Connection_RefereeDB.ExecuteDirect(cSQL);
// tbl_Seizoenen
cSQL := '';
cSQL := cSQL + 'CREATE TABLE tbl_Supervisors ';
cSQL := cSQL + '(';
cSQL := cSQL + 'SPV_ID Word,';
cSQL := cSQL + 'SPV_Naam varchar(255),';
cSQL := cSQL + 'SPV_Volgorde Word';
cSQL := cSQL + ');';
SQLite3Connection_RefereeDB.ExecuteDirect(cSQL);
// tbl_Supervisors vullen met namen
cSQL := '';
cSQL := cSQL + 'INSERT INTO tbl_Supervisors ';
cSQL := cSQL + 'VALUES (1,"",1)';
SQLite3Connection_RefereeDB.ExecuteDirect(cSQL);
cSQL := '';
cSQL := cSQL + 'INSERT INTO tbl_Supervisors ';
cSQL := cSQL + 'VALUES (2,"Aschwin van Loon",2)';
SQLite3Connection_RefereeDB.ExecuteDirect(cSQL);
cSQL := '';
cSQL := cSQL + 'INSERT INTO tbl_Supervisors ';
cSQL := cSQL + 'VALUES (3,"Noud van Berkel",3)';
SQLite3Connection_RefereeDB.ExecuteDirect(cSQL);
cSQL := '';
cSQL := cSQL + 'INSERT INTO tbl_Supervisors ';
cSQL := cSQL + 'VALUES (4,"Guus Sijmons",4)';
SQLite3Connection_RefereeDB.ExecuteDirect(cSQL);
cSQL := '';
cSQL := cSQL + 'INSERT INTO tbl_Supervisors ';
cSQL := cSQL + 'VALUES (5,"Jan Passchier",5)';
SQLite3Connection_RefereeDB.ExecuteDirect(cSQL);
cSQL := '';
cSQL := cSQL + 'INSERT INTO tbl_Supervisors ';
cSQL := cSQL + 'VALUES (6,"Ruud van Baast",7)';
SQLite3Connection_RefereeDB.ExecuteDirect(cSQL);
cSQL := '';
cSQL := cSQL + 'INSERT INTO tbl_Supervisors ';
cSQL := cSQL + 'VALUES (7,"Coen Hensing",7)';
SQLite3Connection_RefereeDB.ExecuteDirect(cSQL);
// tbl_Teams
cSQL := '';
cSQL := cSQL + 'CREATE TABLE tbl_Teams ';
cSQL := cSQL + '(';
cSQL := cSQL + 'Team_ID Word,';
cSQL := cSQL + 'Team_Naam varchar(50),';
cSQL := cSQL + 'Team_Tonen Boolean,';
cSQL := cSQL + 'Team_Divisie Word,';
cSQL := cSQL + 'Team_Plaats varchar(50),';
cSQL := cSQL + 'Team_Alias varchar(50)';
cSQL := cSQL + ');';
SQLite3Connection_RefereeDB.ExecuteDirect(cSQL);
SQLTransaction_RefereeDB.Commit;
and these are the 2 query's you also need
// qry_Scheidsrechters
cSQL := '';
cSQL := 'SELECT '+
'Scheids_ID, REPLACE(RTRIM(COALESCE(Voornaam || " ", "") || ' +
'COALESCE(Tussenvoegsel || " ", "") || COALESCE(Achternaam, "")), '+
'" ", " ") AS Scheidsrechter ' +
'FROM ' +
'tbl_Scheidsrechters;';
SQLite3Connection_RefereeDB.ExecuteDirect('' +
'CREATE VIEW IF NOT EXISTS qry_Scheidsrechters AS ' + cSQL);
// qry_Teams
cSQL := '';
cSQL := 'SELECT '+
'Team_ID, Team_Naam AS Team, Team_Tonen AS Tonen, Team_Divisie, ' +
'Divisie_Tekst AS Divisie, Team_Alias AS Alias, Team_Plaats ' +
'FROM ' +
'tbl_Divisie INNER JOIN tbl_Teams ON tbl_Divisie.Divisie_ID = tbl_Teams.Team_Divisie ' +
'ORDER BY ' +
'Team_Naam;';
SQLite3Connection_RefereeDB.ExecuteDirect('' +
'CREATE VIEW IF NOT EXISTS qry_Teams AS ' + cSQL);
-
Did you try engkin's suggestion?
Or put a 'ShowMessage(cSQL)' to show the contents of the string variable.
One way way to handle big queries :
1. Create a textfile with the exact query text so you can test it also directly in SQLite:
qry_wedstrijden.txtSELECT <whatever>
FROM tbl_xxxx
2. Load the tested query dynamically, do not include it:
with TStringList.Create do
begin
LoadFromFile('qry_wedstrijden.txt');
cSQL := TEXT;
free;
end;
// etc...
-
Below this post is your query (effectively):
I think the problems lies in the fact you want to query columns (like tbl_Wedstrijden, tbl_Seizoenen) which are enclosed in subqueries. You didn't name these INNER JOIN's (via an "as" construct).
For example this will not work:
SELECT
mytable1.field1
FROM
(SELECT field1 from mytable1) as subselecttable
However this will:
SELECT
subselecttable.field1
FROM
(SELECT field1 from mytable1) as subselecttable
So you need to either name those INNER JOIN's and use that name in your select-fields of re-structure your entire select another way.
SELECT
tbl_Wedstrijden.Wed_ID,
tbl_Seizoenen.SEI_ID AS Seizoen,
Wed_Datum AS Datum,
Wed_Tijd AS Tijd,
tbl_Divisie.Divisie_Tekst AS Divisie,
qry_Teams.Team AS [Team A],
qry_Teams_1.Team AS [Team B],
qry_Scheidsrechters.Scheidsrechter AS [Scheidsrechter 1],
qry_Scheidsrechters_1.Scheidsrechter AS [Scheidsrechter 2],
qry_Scheidsrechters_2.Scheidsrechter AS [Scheidsrechter 3],
qry_Scheidsrechters_3.Scheidsrechter AS [Scheidsrechter 4],
CASE WHEN Wed_Begeleiding THEN "x" ELSE NULL END AS B,
CASE WHEN Wed_Meetellen THEN "x" ELSE NULL END AS M,
CASE WHEN Wed_Show THEN "x" ELSE NULL END AS W,
CASE WHEN Wed_Outlook THEN "x" ELSE NULL END AS O,
tbl_Wedstrijden.Wed_Scheids_1,
tbl_Wedstrijden.Wed_Scheids_2,
tbl_Wedstrijden.Wed_Scheids_3,
tbl_Wedstrijden.Wed_Scheids_4,
tbl_Wedstrijden.Wed_Divisie AS Div,
tbl_Wedstrijden.Wed_Meetellen,
tbl_Wedstrijden.Wed_TeamA,
tbl_Wedstrijden.Wed_TeamB,
tbl_Wedstrijden.Wed_Show,
qry_Teams.Team_Plaats AS Speelplaats,
tbl_Wedstrijden.Wed_Plaats,
tbl_IJsbanen.IJS_Plaats,
tbl_Wedstrijden.Wed_Datum,
tbl_Wedstrijden.Wed_Tijd,
tbl_IJsbanen.IJS_Kilometers,
tbl_Wedstrijden.Wed_Opmerkingen,
2*[IJS_Kilometers]*0.28 AS Euro,
tbl_Divisie.Divisie_Vergoeding,
tbl_IJsbanen.IJS_Parkeren
FROM tbl_Divisie
INNER JOIN
(
tbl_Seizoenen
INNER JOIN
(
(
tbl_IJsbanen
INNER JOIN
(
(
(
(
qry_Teams AS qry_Teams_1
INNER JOIN
(
qry_Teams INNER JOIN tbl_Wedstrijden ON qry_Teams.Team_ID = tbl_Wedstrijden.Wed_TeamA
) ON qry_Teams_1.Team_ID = tbl_Wedstrijden.Wed_TeamB
)
LEFT JOIN qry_Scheidsrechters ON tbl_Wedstrijden.Wed_Scheids_1 = qry_Scheidsrechters.Scheids_ID
)
LEFT JOIN qry_Scheidsrechters AS qry_Scheidsrechters_1 ON tbl_Wedstrijden.Wed_Scheids_2 = qry_Scheidsrechters_1.Scheids_ID
)
LEFT JOIN qry_Scheidsrechters AS qry_Scheidsrechters_2 ON tbl_Wedstrijden.Wed_Scheids_3 = qry_Scheidsrechters_2.Scheids_ID
) ON tbl_IJsbanen.IJS_ID = tbl_Wedstrijden.Wed_Plaats
)
LEFT JOIN qry_Scheidsrechters AS qry_Scheidsrechters_3 ON tbl_Wedstrijden.Wed_Scheids_4 = qry_Scheidsrechters_3.Scheids_ID
) ON tbl_Seizoenen.SEI_ID = tbl_Wedstrijden.Wed_Seizoen_ID
) ON tbl_Divisie.Divisie_ID = tbl_Wedstrijden.Wed_Divisie
ORDER BY
tbl_Seizoenen.SEI_ID DESC,
tbl_Wedstrijden.Wed_Datum DESC,
tbl_Wedstrijden.Wed_Tijd DESC
-
That what you are saying i really don't understand
The original database was an MS Access Database and this query is correct.
It even works in Embarcadero Delphi XE7 and it works in DB Browser for SQLite on Mac OS X
So one would thing that if it works on 3 different platforms it should also work in Lazarus
-
You mean you pasted the select-query I mentioned in my post in "DB Browser for SQLite" and it gave you a result (and no error) ??
In SQLiteManager I got an error-message.
-
Your code actually works without changes in Lazarus. Run the attached project and press Create View and it will be created.
My guess is still that you somehow have created a database file that doesn't have the full set of tables or columns and thats why you get a message about missing column. Check the content of the actual sqlite dbfile **that is in use** manually with SQliteMan or similar.
-
@kapibara:
This project creates an error called : SQLTransaction_RefereeDB.Options: Unknown property "Options".
This keeps getting weirder
-
May be, that kapibara uses newer version of FPC (TRUNK 3.0) as you (2.6.4)
As TSQLTransaction.Options property was added recently ...
-
@Lacak: Right, I was using the SVN version and forgot that TSQLTransaction has been extended.
@madref: Tried it with Laz 1.24 release and the solution is simple. Follow these steps:
1) Open the project
2) Choose Continue Loading on "unknown property"
3) Delete SQLTransaction_RefereeDB from form
4) Drop a new TSQLTransaction on the form and name SQLTransaction_RefereeDB
5) Run the project
Now it works because you have removed the extended TSQLTransaction that doesnt exist yet in the released Lazarus. And we can all enjoy having learnt something new. ;)
-
$%^&^% GRRRRRR........Still get the same error :(
-
Still the "Unknown property" or your original "no such column tbl_Wedstrijden.Wed_ID" ?
The code from kapibara works correctly here. It uses the SQLTransaction_RefereeDB.Commit; after doing the CREATE VIEW (which I had forgotten) so the final CREATE VIEW in btnOriginalClick works perfect.
Did you check with SQliteMan or similar if the qry_Teams and qry_Scheidsrechters is really created (even without pressing the button)?
Or are you having other error-messages?
Another possible way to change the code if you're having trouble with that options-line is opening up uMain.lfm with a text-editor and removing the "Options = []" line from the SQLTransaction_RefereeDB object.
-
I still get the original error: no such column.....
Yes the other 2 query's are being made correctly (as they do in my own code).
Can it help if i close the database and reopen it and then try to add the query?
P.S. How do i do that?
-
Reopening the database should not make a difference because you're using the same transaction in all commands.
But you can try:
If you are sure your Madref.db3 database contains all your VIEWS (AND you checked this with SQliteMan) you could try removing the delete and create code from the FormCreate procedure:
procedure TForm1.FormCreate(Sender: TObject);
var
cSQL, DBFile: string;
begin
DBFile:= ExtractFilePath(Application.ExeName) +'Madref.db3';
// if FileExists(DBFile) then DeleteFile(DBFile); // <------ DO NOT DELETE
SQLite3Connection_RefereeDB.DatabaseName:= DBFile;
exit; // <---------------- This will keep the Madref.db3 and use it like it is.
<------------ So with all the VIEWS you checked with SQliteMan
//.... rest of code which will NOT execute
After this... what error-message do you get when pressing "Create View Overzicht Wedstrijden" ??
-
I just checked again.
The view is created but it seems the view qry_Overzicht_Wedstrijden doesn't give any columns back.
When checking the DLL for that view it is this:
-- Describe QRY_OVERZICHT_WEDSTRIJDEN
CREATE VIEW qry_Overzicht_Wedstrijden AS SELECT tbl_Wedstrijden.Wed_ID, tbl_Seizoenen.SEI_ID AS Seizoen, Wed_Datum AS Datum, Wed_Tijd AS Tijd, tbl_Divisie.Divisie_Tekst AS Divisie, qry_Teams.Team AS [Team A], qry_Teams_1.Team AS [Team B],qry_Scheidsrechters.Scheidsrechter AS [Scheidsrechter 1], qry_Scheidsrechters_1.Scheidsrechter AS [Scheidsrechter 2], qry_Scheidsrechters_2.Scheidsrechter AS [Scheidsrechter 3], qry_Scheidsrechters_3.Scheidsrechter AS [Scheidsrechter 4], CASE WHEN Wed_Begeleiding THEN "x" ELSE NULL END AS B, CASE WHEN Wed_Meetellen THEN "x" ELSE NULL END AS M, CASE WHEN Wed_Show THEN "x" ELSE NULL END AS W, CASE WHEN Wed_Outlook THEN "x" ELSE NULL END AS O, tbl_Wedstrijden.Wed_Scheids_1, tbl_Wedstrijden.Wed_Scheids_2, tbl_Wedstrijden.Wed_Scheids_3, tbl_Wedstrijden.Wed_Scheids_4, tbl_Wedstrijden.Wed_Divisie AS Div, tbl_Wedstrijden.Wed_Meetellen, tbl_Wedstrijden.Wed_TeamA, tbl_Wedstrijden.Wed_TeamB, tbl_Wedstrijden.Wed_Show, qry_Teams.Team_Plaats AS Speelplaats, tbl_Wedstrijden.Wed_Plaats, tbl_IJsbanen.IJS_Plaats, tbl_Wedstrijden.Wed_Datum, tbl_Wedstrijden.Wed_Tijd, tbl_IJsbanen.IJS_Kilometers, tbl_Wedstrijden.Wed_Opmerkingen, 2*[IJS_Kilometers]*0.28 AS Euro, tbl_Divisie.Divisie_Vergoeding, tbl_IJsbanen.IJS_Parkeren FROM tbl_Divisie INNER JOIN (tbl_Seizoenen INNER JOIN ((tbl_IJsbanen INNER JOIN ((((qry_Teams AS qry_Teams_1 INNER JOIN (qry_Teams INNER JOIN tbl_Wedstrijden ON qry_Teams.Team_ID = tbl_Wedstrijden.Wed_TeamA) ON qry_Teams_1.Team_ID = tbl_Wedstrijden.Wed_TeamB) LEFT JOIN qry_Scheidsrechters ON tbl_Wedstrijden.Wed_Scheids_1 = qry_Scheidsrechters.Scheids_ID) LEFT JOIN qry_Scheidsrechters AS qry_Scheidsrechters_1 ON tbl_Wedstrijden.Wed_Scheids_2 = qry_Scheidsrechters_1.Scheids_ID) LEFT JOIN qry_Scheidsrechters AS qry_Scheidsrechters_2 ON tbl_Wedstrijden.Wed_Scheids_3 = qry_Scheidsrechters_2.Scheids_ID) ON tbl_IJsbanen.IJS_ID = tbl_Wedstrijden.Wed_Plaats) LEFT JOIN qry_Scheidsrechters AS qry_Scheidsrechters_3 ON tbl_Wedstrijden.Wed_Scheids_4 = qry_Scheidsrechters_3.Scheids_ID) ON tbl_Seizoenen.SEI_ID = tbl_Wedstrijden.Wed_Seizoen_ID) ON tbl_Divisie.Divisie_ID = tbl_Wedstrijden.Wed_Divisie ORDER BY tbl_Seizoenen.SEI_ID DESC , tbl_Wedstrijden.Wed_Datum DESC , tbl_Wedstrijden.Wed_Tijd DESC
When running the SELECT-statement of that view it is invalid:
Query Error: no such column: tbl_Wedstrijden.Wed_ID Unable to execute statement
And with this I keep coming back to the problem that you can't address fields and tables defined in the INNER JOIN if that join isn't named.
Can you trace back what the exact VIEW-statement is in Embarcadero Delphi XE7 or provide us with a sample db3 database so we can check why it was working in your other environment?
(I'm puzzled as to why Lazarus/SQLite drivers did allow the creation of this VIEW while trying to create it manually it gives an error:
Query Error: no such column: tbl_Wedstrijden.Wed_ID Unable to execute statement)
Yeah, somehow it is possible to create "invalid" VIEWS with the SQLite driver. So the VIEW qry_Overzicht_Wedstrijden is created but will give an error on SELECT * FROM qry_Overzicht_Wedstrijden.
-
How would you make this query then?
-
How would you make this query then?
I would begin with unwrapping all those "enclosed" inner joins so their fields are directly accessible to the main SELECT-statement (which in your case they were not).
Below is one way. I don't have a database filled so I can't check if the result is exactly what you want but it will show all the games with fields from the other tables (like I assume you want). If this statement runs correctly for you, you can use it to make your view by putting "CREATE VIEW IF NOT EXISTS qry_Overzicht_Wedstrijden AS " before it.
This is the easiest way to just first check if the SELECT statement works correctly and then using it in a VIEW.
(Also not sure but maybe all your INNER JOIN (http://www.w3schools.com/sql/sql_join_inner.asp) could also be LEFT JOIN (http://www.w3schools.com/sql/sql_join_left.asp) but if all those fields referenced in the INNER JOIN have values in the main tbl_Wedstrijden table it shouldn't mater that much.)
SELECT
tbl_Wedstrijden.Wed_ID,
tbl_Seizoenen.SEI_ID AS Seizoen,
Wed_Datum AS Datum,
Wed_Tijd AS Tijd,
tbl_Divisie.Divisie_Tekst AS Divisie,
qry_Team_A.Team AS [Team A],
qry_Team_B.Team AS [Team B],
qry_Scheidsrechters_1.Scheidsrechter AS [Scheidsrechter 1],
qry_Scheidsrechters_2.Scheidsrechter AS [Scheidsrechter 2],
qry_Scheidsrechters_3.Scheidsrechter AS [Scheidsrechter 3],
qry_Scheidsrechters_4.Scheidsrechter AS [Scheidsrechter 4],
CASE WHEN Wed_Begeleiding THEN "x" ELSE NULL END AS B,
CASE WHEN Wed_Meetellen THEN "x" ELSE NULL END AS M,
CASE WHEN Wed_Show THEN "x" ELSE NULL END AS W,
CASE WHEN Wed_Outlook THEN "x" ELSE NULL END AS O,
tbl_Wedstrijden.Wed_Scheids_1,
tbl_Wedstrijden.Wed_Scheids_2,
tbl_Wedstrijden.Wed_Scheids_3,
tbl_Wedstrijden.Wed_Scheids_4,
tbl_Wedstrijden.Wed_Divisie AS Div,
tbl_Wedstrijden.Wed_Meetellen,
tbl_Wedstrijden.Wed_TeamA,
tbl_Wedstrijden.Wed_TeamB,
tbl_Wedstrijden.Wed_Show,
qry_Team_A.Team_Plaats AS Speelplaats,
tbl_Wedstrijden.Wed_Plaats,
tbl_IJsbanen.IJS_Plaats,
tbl_Wedstrijden.Wed_Datum,
tbl_Wedstrijden.Wed_Tijd,
tbl_IJsbanen.IJS_Kilometers,
tbl_Wedstrijden.Wed_Opmerkingen,
2*[IJS_Kilometers]*0.28 AS Euro,
tbl_Divisie.Divisie_Vergoeding,
tbl_IJsbanen.IJS_Parkeren
FROM tbl_Wedstrijden
INNER JOIN tbl_Divisie ON tbl_Divisie.Divisie_ID = tbl_Wedstrijden.Wed_Divisie
INNER JOIN tbl_Seizoenen ON tbl_Seizoenen.SEI_ID = tbl_Wedstrijden.Wed_Seizoen_ID
INNER JOIN tbl_IJsbanen ON tbl_IJsbanen.IJS_ID = tbl_Wedstrijden.Wed_Plaats
INNER JOIN qry_Teams AS qry_Team_A ON qry_Team_A.Team_ID = tbl_Wedstrijden.Wed_TeamA
INNER JOIN qry_Teams AS qry_Team_B ON qry_Team_B.Team_ID = tbl_Wedstrijden.Wed_TeamB
LEFT JOIN qry_Scheidsrechters AS qry_Scheidsrechters_1 ON tbl_Wedstrijden.Wed_Scheids_1 = qry_Scheidsrechters_1.Scheids_ID
LEFT JOIN qry_Scheidsrechters AS qry_Scheidsrechters_2 ON tbl_Wedstrijden.Wed_Scheids_2 = qry_Scheidsrechters_2.Scheids_ID
LEFT JOIN qry_Scheidsrechters AS qry_Scheidsrechters_3 ON tbl_Wedstrijden.Wed_Scheids_3 = qry_Scheidsrechters_3.Scheids_ID
LEFT JOIN qry_Scheidsrechters AS qry_Scheidsrechters_4 ON tbl_Wedstrijden.Wed_Scheids_4 = qry_Scheidsrechters_4.Scheids_ID
ORDER BY
tbl_Seizoenen.SEI_ID DESC,
tbl_Wedstrijden.Wed_Datum DESC,
tbl_Wedstrijden.Wed_Tijd DESC
(I'm still puzzled as to why this statement worked for you earlier in Delphi and SQLite on Mac OS X)
-
Wow now that it is written this way it works.....
Can you help me rewrite this one too?
// qry_Overzicht_Linesman
cSQL := '';
cSQL := 'SELECT ' +
'tbl_Evaluatie_Linesman.Evo_ID, tbl_Evaluatie_Linesman.Evo_Wed_ID, ' +
'qry_Scheidsrechters.Scheidsrechter, tbl_Wedstrijden.Wed_Datum, ' +
'tbl_Wedstrijden.Wed_Tijd, qry_Teams.Divisie, qry_Teams.Team AS [Team A], ' +
'qry_Teams_1.Team AS [Team B], tbl_Evaluatie_Linesman.Evo_Systeem, ' +
'tbl_Evaluatie_Linesman.Evo_SoortWedstrijd, tbl_Evaluatie_Linesman.Evo_Goal_TeamA_Per1, ' +
'tbl_Evaluatie_Linesman.Evo_Goal_TeamA_Per2, tbl_Evaluatie_Linesman.Evo_Goal_TeamA_Per3, ' +
'tbl_Evaluatie_Linesman.Evo_Goal_TeamA_Per4, [Evo_Goal_TeamA_Per1]+[Evo_Goal_TeamA_Per2]+' +
'[Evo_Goal_TeamA_Per3]+[Evo_Goal_TeamA_Per4] AS Goals_A, ' +
'tbl_Evaluatie_Linesman.Evo_Goal_TeamB_Per1, tbl_Evaluatie_Linesman.Evo_Goal_TeamB_Per2, ' +
'tbl_Evaluatie_Linesman.Evo_Goal_TeamB_Per3, tbl_Evaluatie_Linesman.Evo_Goal_TeamB_Per4, ' +
'[Evo_Goal_TeamB_Per1]+[Evo_Goal_TeamB_Per2]+[Evo_Goal_TeamB_Per3]+[Evo_Goal_TeamB_Per4] ' +
'AS Goals_B, tbl_Evaluatie_Linesman.Evo_Straf_TeamA_Per1, ' +
'tbl_Evaluatie_Linesman.Evo_Straf_TeamA_Per2, tbl_Evaluatie_Linesman.Evo_Straf_TeamA_Per3, ' +
'tbl_Evaluatie_Linesman.Evo_Straf_TeamA_Per4, [Evo_Straf_TeamA_Per1]+[Evo_Straf_TeamA_Per2]+' +
'[Evo_Straf_TeamA_Per3]+[Evo_Straf_TeamA_Per4] AS Straffen_A, ' +
'tbl_Evaluatie_Linesman.Evo_Straf_TeamB_Per1, tbl_Evaluatie_Linesman.Evo_Straf_TeamB_Per2, ' +
'tbl_Evaluatie_Linesman.Evo_Straf_TeamB_Per3, tbl_Evaluatie_Linesman.Evo_Straf_TeamB_Per4, ' +
'[Evo_Straf_TeamB_Per1]+[Evo_Straf_TeamB_Per2]+[Evo_Straf_TeamB_Per3]+[Evo_Straf_TeamB_Per4] ' +
'AS Straffen_B, tbl_Evaluatie_Linesman.Evo_Fitheid, tbl_Evaluatie_Linesman.Evo_Tekens, ' +
'tbl_Evaluatie_Linesman.Evo_Positie_Eindvak, tbl_Evaluatie_Linesman.Evo_FaceOffs, ' +
'tbl_Evaluatie_Linesman.Evo_GameManagement, tbl_Evaluatie_Linesman.Evo_Aanwezigheid, ' +
'tbl_Evaluatie_Linesman.Evo_Oplettendheid, tbl_Evaluatie_Linesman.Evo_Communicatie, ' +
'tbl_Evaluatie_Linesman.Evo_Teamwork, tbl_Evaluatie_Linesman.Evo_Standaard_Per1, ' +
'tbl_Evaluatie_Linesman.Evo_Standaard_Per2, tbl_Evaluatie_Linesman.Evo_Standaard_Per3, ' +
'tbl_Evaluatie_Linesman.Evo_Standaard_Per4, tbl_Evaluatie_Linesman.Evo_Straffen_Gemist, ' +
'tbl_Evaluatie_Linesman.Evo_Straffen_Cheap, tbl_Evaluatie_Linesman.Evo_Icings_Gemist, ' +
'tbl_Evaluatie_Linesman.Evo_Icings_Twijfel, tbl_Evaluatie_Linesman.Evo_SterkePunten, ' +
'tbl_Evaluatie_Linesman.Evo_VerbeterPunten, tbl_Evaluatie_Linesman.Evo_Feedback, ' +
'tbl_Supervisors.SPV_Naam ' +
'FROM ' +
'tbl_Supervisors INNER JOIN (((tbl_Wedstrijden INNER JOIN qry_Teams ON ' +
'tbl_Wedstrijden.Wed_TeamA = qry_Teams.Team_ID) INNER JOIN qry_Teams AS qry_Teams_1 ' +
'ON tbl_Wedstrijden.Wed_TeamB = qry_Teams_1.Team_ID) INNER JOIN ' +
'(tbl_Evaluatie_Linesman INNER JOIN qry_Scheidsrechters ON ' +
'tbl_Evaluatie_Linesman.Evo_Scheids_ID = qry_Scheidsrechters.Scheids_ID) ON ' +
'tbl_Wedstrijden.Wed_ID = tbl_Evaluatie_Linesman.Evo_Wed_ID) ON ' +
'tbl_Supervisors.SPV_ID = tbl_Wedstrijden.Wed_SPV_ID;';
It gives the same error: No such column tbl_Evaluatie_Linesman.Evo_ID.
-
Below is the new query (which you can use for creating a VIEW if you wish). How did you come to all those nested INNER JOIN's in your own statements??
See it like this:
I changed the "FROM tbl_Supervisors" to "FROM tbl_Wedstrijden" because I think it is what you take as starting point. (you want to list all games, I think). Then for every field which is a pointer to another table you just add "INNER JOIN table ON table.my_table_ID = wedstrijd.my_table_ID". Then you can add table.field1, table.field2 etc to you SELECT fields. You do that with all the tables you want info from. There is no need to use nested INNER JOIN's.
If all the fields are filled in properly then using INNER JOIN's wont give you a problem. If however for one game only 3 referees are present (for example ) and the 4th is left empty or is 0 then your INNER JOIN will result in no games in your result. If that's the case you should replace all the INNER JOIN with LEFT JOIN. But I expect that doesn't happen in your case.
SELECT
tbl_Evaluatie_Linesman.Evo_ID,
tbl_Evaluatie_Linesman.Evo_Wed_ID,
qry_Scheidsrechters.Scheidsrechter,
tbl_Wedstrijden.Wed_Datum,
tbl_Wedstrijden.Wed_Tijd,
qry_Team_A.Divisie,
qry_Team_A.Team AS [Team A],
qry_Team_B.Team AS [Team B],
tbl_Evaluatie_Linesman.Evo_Systeem,
tbl_Evaluatie_Linesman.Evo_SoortWedstrijd,
tbl_Evaluatie_Linesman.Evo_Goal_TeamA_Per1,
tbl_Evaluatie_Linesman.Evo_Goal_TeamA_Per2,
tbl_Evaluatie_Linesman.Evo_Goal_TeamA_Per3,
tbl_Evaluatie_Linesman.Evo_Goal_TeamA_Per4,
[Evo_Goal_TeamA_Per1]+[Evo_Goal_TeamA_Per2]+[Evo_Goal_TeamA_Per3]+[Evo_Goal_TeamA_Per4] AS Goals_A,
tbl_Evaluatie_Linesman.Evo_Goal_TeamB_Per1,
tbl_Evaluatie_Linesman.Evo_Goal_TeamB_Per2,
tbl_Evaluatie_Linesman.Evo_Goal_TeamB_Per3,
tbl_Evaluatie_Linesman.Evo_Goal_TeamB_Per4,
[Evo_Goal_TeamB_Per1]+[Evo_Goal_TeamB_Per2]+[Evo_Goal_TeamB_Per3]+[Evo_Goal_TeamB_Per4] AS Goals_B,
tbl_Evaluatie_Linesman.Evo_Straf_TeamA_Per1,
tbl_Evaluatie_Linesman.Evo_Straf_TeamA_Per2,
tbl_Evaluatie_Linesman.Evo_Straf_TeamA_Per3,
tbl_Evaluatie_Linesman.Evo_Straf_TeamA_Per4,
[Evo_Straf_TeamA_Per1]+[Evo_Straf_TeamA_Per2]+[Evo_Straf_TeamA_Per3]+[Evo_Straf_TeamA_Per4] AS Straffen_A,
tbl_Evaluatie_Linesman.Evo_Straf_TeamB_Per1,
tbl_Evaluatie_Linesman.Evo_Straf_TeamB_Per2,
tbl_Evaluatie_Linesman.Evo_Straf_TeamB_Per3,
tbl_Evaluatie_Linesman.Evo_Straf_TeamB_Per4,
[Evo_Straf_TeamB_Per1]+[Evo_Straf_TeamB_Per2]+[Evo_Straf_TeamB_Per3]+[Evo_Straf_TeamB_Per4] AS Straffen_B,
tbl_Evaluatie_Linesman.Evo_Fitheid,
tbl_Evaluatie_Linesman.Evo_Tekens,
tbl_Evaluatie_Linesman.Evo_Positie_Eindvak,
tbl_Evaluatie_Linesman.Evo_FaceOffs,
tbl_Evaluatie_Linesman.Evo_GameManagement,
tbl_Evaluatie_Linesman.Evo_Aanwezigheid,
tbl_Evaluatie_Linesman.Evo_Oplettendheid,
tbl_Evaluatie_Linesman.Evo_Communicatie,
tbl_Evaluatie_Linesman.Evo_Teamwork,
tbl_Evaluatie_Linesman.Evo_Standaard_Per1,
tbl_Evaluatie_Linesman.Evo_Standaard_Per2,
tbl_Evaluatie_Linesman.Evo_Standaard_Per3,
tbl_Evaluatie_Linesman.Evo_Standaard_Per4,
tbl_Evaluatie_Linesman.Evo_Straffen_Gemist,
tbl_Evaluatie_Linesman.Evo_Straffen_Cheap,
tbl_Evaluatie_Linesman.Evo_Icings_Gemist,
tbl_Evaluatie_Linesman.Evo_Icings_Twijfel,
tbl_Evaluatie_Linesman.Evo_SterkePunten,
tbl_Evaluatie_Linesman.Evo_VerbeterPunten,
tbl_Evaluatie_Linesman.Evo_Feedback,
tbl_Supervisors.SPV_Naam
FROM tbl_Wedstrijden
INNER JOIN tbl_Supervisors ON tbl_Wedstrijden.Wed_SPV_ID = tbl_Supervisors.SPV_ID
INNER JOIN qry_Teams AS qry_Team_A ON tbl_Wedstrijden.Wed_TeamA = qry_Team_A.Team_ID
INNER JOIN qry_Teams AS qry_Team_B ON tbl_Wedstrijden.Wed_TeamB = qry_Team_B.Team_ID
INNER JOIN tbl_Evaluatie_Linesman ON tbl_Wedstrijden.Wed_ID = tbl_Evaluatie_Linesman.Evo_Wed_ID
INNER JOIN qry_Scheidsrechters ON tbl_Evaluatie_Linesman.Evo_Scheids_ID = qry_Scheidsrechters.Scheids_ID
(again, I couldn't test this because I don't have functional data in my test-database)
-
It's a 1-on-1 from MS Access.
I will try it and let you know.
-
Well it worked.
Now i finally can move on to the next problem ;)
And what that is i will let you all know when i have arrived there :)
Thanx for all the help
-
It's a 1-on-1 from MS Access.
Aah, Ok. You probably used the Access' query designer to create those statements. It seems Access does require parentheses in it's statement at odd points. Unlike "normal" SQL. I've never used a query designer and always create queries "by hand" but it seems that Access' query designer makes it's queries quite difficult to read and somewhat incompatible with other SQL-engines (like SQLite and Firebird/Interbase). I even wonder if MSSQL could run that original query unchanged.
Good luck finding your next problem :)