Recent

Author Topic: [SOLVED] Can't create view (query)  (Read 13994 times)

madref

  • Hero Member
  • *****
  • Posts: 949
  • ..... A day not Laughed is a day wasted !!
    • Nursing With Humour
[SOLVED] Can't create view (query)
« 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.
« Last Edit: April 04, 2015, 12:12:35 am by madref »
You treat a disease, you win, you lose.
You treat a person and I guarantee you, you win, no matter the outcome.

Lazarus 3.99 (rev main_3_99-649-ge13451a5ab) FPC 3.3.1 x86_64-darwin-cocoa
Mac OS X Monterey

eny

  • Hero Member
  • *****
  • Posts: 1634
Re: Can't create view (query)
« Reply #1 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.
All posts based on: Win10 (Win64); Lazarus 2.0.10 'stable' (x64) unless specified otherwise...

taazz

  • Hero Member
  • *****
  • Posts: 5368
Re: Can't create view (query)
« Reply #2 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.
Good judgement is the result of experience … Experience is the result of bad judgement.

OS : Windows 7 64 bit
Laz: Lazarus 1.4.4 FPC 2.6.4 i386-win32-win32/win64

eny

  • Hero Member
  • *****
  • Posts: 1634
Re: Can't create view (query)
« Reply #3 on: March 17, 2015, 07:05:12 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.
All posts based on: Win10 (Win64); Lazarus 2.0.10 'stable' (x64) unless specified otherwise...

madref

  • Hero Member
  • *****
  • Posts: 949
  • ..... A day not Laughed is a day wasted !!
    • Nursing With Humour
Re: Can't create view (query)
« Reply #4 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 :(
« Last Edit: March 17, 2015, 08:33:17 pm by madref »
You treat a disease, you win, you lose.
You treat a person and I guarantee you, you win, no matter the outcome.

Lazarus 3.99 (rev main_3_99-649-ge13451a5ab) FPC 3.3.1 x86_64-darwin-cocoa
Mac OS X Monterey

LacaK

  • Hero Member
  • *****
  • Posts: 691
Re: Can't create view (query)
« Reply #5 on: March 17, 2015, 08:49:46 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

madref

  • Hero Member
  • *****
  • Posts: 949
  • ..... A day not Laughed is a day wasted !!
    • Nursing With Humour
Re: Can't create view (query)
« Reply #6 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?
You treat a disease, you win, you lose.
You treat a person and I guarantee you, you win, no matter the outcome.

Lazarus 3.99 (rev main_3_99-649-ge13451a5ab) FPC 3.3.1 x86_64-darwin-cocoa
Mac OS X Monterey

Cyrax

  • Hero Member
  • *****
  • Posts: 836
Re: Can't create view (query)
« Reply #7 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.

madref

  • Hero Member
  • *****
  • Posts: 949
  • ..... A day not Laughed is a day wasted !!
    • Nursing With Humour
Re: Can't create view (query)
« Reply #8 on: March 18, 2015, 12:49:17 am »
Then why isn't it working....
I am at a total loss
You treat a disease, you win, you lose.
You treat a person and I guarantee you, you win, no matter the outcome.

Lazarus 3.99 (rev main_3_99-649-ge13451a5ab) FPC 3.3.1 x86_64-darwin-cocoa
Mac OS X Monterey

engkin

  • Hero Member
  • *****
  • Posts: 3112
Re: Can't create view (query)
« Reply #9 on: March 18, 2015, 01:12:14 am »
The mode you are using affects the meaning of string. Make sure to add {$H+} on the top of your file.
« Last Edit: March 18, 2015, 01:15:06 am by engkin »

kapibara

  • Hero Member
  • *****
  • Posts: 610
Re: Can't create view (query)
« Reply #10 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.
« Last Edit: March 18, 2015, 05:31:39 am by kapibara »
Lazarus trunk / fpc 3.2.2 / Kubuntu 22.04 - 64 bit

madref

  • Hero Member
  • *****
  • Posts: 949
  • ..... A day not Laughed is a day wasted !!
    • Nursing With Humour
Re: Can't create view (query)
« Reply #11 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);
You treat a disease, you win, you lose.
You treat a person and I guarantee you, you win, no matter the outcome.

Lazarus 3.99 (rev main_3_99-649-ge13451a5ab) FPC 3.3.1 x86_64-darwin-cocoa
Mac OS X Monterey

eny

  • Hero Member
  • *****
  • Posts: 1634
Re: Can't create view (query)
« Reply #12 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...
« Last Edit: March 18, 2015, 02:08:35 pm by eny »
All posts based on: Win10 (Win64); Lazarus 2.0.10 'stable' (x64) unless specified otherwise...

rvk

  • Hero Member
  • *****
  • Posts: 6111
Re: Can't create view (query)
« Reply #13 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

madref

  • Hero Member
  • *****
  • Posts: 949
  • ..... A day not Laughed is a day wasted !!
    • Nursing With Humour
Re: Can't create view (query)
« Reply #14 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
You treat a disease, you win, you lose.
You treat a person and I guarantee you, you win, no matter the outcome.

Lazarus 3.99 (rev main_3_99-649-ge13451a5ab) FPC 3.3.1 x86_64-darwin-cocoa
Mac OS X Monterey

 

TinyPortal © 2005-2018