Lazarus

Programming => Databases => Topic started by: madref on March 17, 2015, 05:42:49 pm

Title: [SOLVED] Can't create view (query)
Post by: madref on March 17, 2015, 05:42:49 pm

I have created a few tables and one of them is:

Code: [Select]

    // 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.

Quote

Project ... raised exception class 'EDatabaseError' with message: no such column tbl_Wedstrijden.Wed_ID

Code: [Select]

// 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.

Title: Re: Can't create view (query)
Post by: eny on March 17, 2015, 06:56:01 pm

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.

Title: Re: Can't create view (query)
Post by: taazz on March 17, 2015, 07:02:15 pm

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.

Title: Re: Can't create view (query)
Post by: eny on March 17, 2015, 07:05:12 pm

Quote from: taazz on March 17, 2015, 07:02:15 pm

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.

Title: Re: Can't create view (query)
Post by: madref on March 17, 2015, 08:30:42 pm

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 :(

Title: Re: Can't create view (query)
Post by: LacaK on March 17, 2015, 08:49:46 pm

Quote from: madref on March 17, 2015, 08:30:42 pm

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

Title: Re: Can't create view (query)
Post by: madref on March 17, 2015, 09:41:42 pm

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?

Title: Re: Can't create view (query)
Post by: Cyrax on March 18, 2015, 12:44:48 am

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.

Title: Re: Can't create view (query)
Post by: madref on March 18, 2015, 12:49:17 am

Then why isn't it working....
I am at a total loss

Title: Re: Can't create view (query)
Post by: engkin on March 18, 2015, 01:12:14 am

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.

Title: Re: Can't create view (query)
Post by: kapibara on March 18, 2015, 02:19:50 am

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) ->

Code: [Select]

  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.

Title: Re: Can't create view (query)
Post by: madref on March 18, 2015, 07:40:53 am

These are the tables I use

Code: [Select]

// 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

Code: [Select]

    // 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);


Title: Re: Can't create view (query)
Post by: eny on March 18, 2015, 02:03:45 pm

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.txt

Code: [Select]

SELECT <whatever>
  FROM tbl_xxxx
2. Load the tested query dynamically, do not include it:

Code: [Select]

with TStringList.Create do
begin
  LoadFromFile('qry_wedstrijden.txt');
  cSQL := TEXT;
  free;
end;
// etc...

Title: Re: Can't create view (query)
Post by: rvk on March 18, 2015, 04:04:31 pm

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:

Code: [Select]

SELECT
  mytable1.field1
FROM
  (SELECT field1 from mytable1) as subselecttable


However this will:

Code: [Select]

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.

Code: [Select]

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


Title: Re: Can't create view (query)
Post by: madref on March 18, 2015, 04:21:25 pm

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

Title: Re: Can't create view (query)
Post by: rvk on March 18, 2015, 04:23:28 pm

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.

Title: Re: Can't create view (query)
Post by: kapibara on March 18, 2015, 07:21:30 pm

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.

Title: Re: Can't create view (query)
Post by: madref on March 23, 2015, 08:08:09 am

@kapibara:
This project creates an error called : SQLTransaction_RefereeDB.Options: Unknown property "Options".

This keeps getting weirder

Title: Re: Can't create view (query)
Post by: LacaK on March 23, 2015, 12:02:14 pm

May be, that kapibara uses newer version of FPC (TRUNK 3.0) as you (2.6.4)
As TSQLTransaction.Options property was added recently ...

Title: Re: Can't create view (query)
Post by: kapibara on March 23, 2015, 11:07:51 pm

@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. ;)

Title: Re: Can't create view (query)
Post by: madref on March 24, 2015, 10:45:37 am

$%^&^% GRRRRRR........Still get the same error :(

Title: Re: Can't create view (query)
Post by: rvk on March 24, 2015, 11:18:29 am

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.

Title: Re: Can't create view (query)
Post by: madref on March 24, 2015, 01:32:27 pm

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?

Title: Re: Can't create view (query)
Post by: rvk on March 24, 2015, 01:42:04 pm

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:

Code: [Select]

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" ??

Title: Re: Can't create view (query)
Post by: rvk on March 24, 2015, 02:00:46 pm

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:

Code: [Select]

-- 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.

Title: Re: Can't create view (query)
Post by: madref on March 24, 2015, 03:07:13 pm

How would you make this query then?

Title: Re: Can't create view (query)
Post by: rvk on March 24, 2015, 03:31:14 pm

Quote from: madref on March 24, 2015, 03:07:13 pm

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.)

Code: [Select]

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)

Title: Re: Can't create view (query)
Post by: madref on March 24, 2015, 06:49:14 pm

Wow now that it is written this way it works.....

Can you help me rewrite this one too?

Code: [Select]

// 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.

Title: Re: Can't create view (query)
Post by: rvk on March 24, 2015, 08:36:53 pm

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.

Code: [Select]

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)

Title: Re: Can't create view (query)
Post by: madref on March 24, 2015, 09:44:13 pm

It's a 1-on-1 from MS Access.

I will try it and let you know.

Title: Re: Can't create view (query)
Post by: madref on March 24, 2015, 10:22:46 pm

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

Title: Re: Can't create view (query)
Post by: rvk on March 24, 2015, 10:24:23 pm

Quote from: madref on March 24, 2015, 09:44:13 pm

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 :)