Hi
Maybe it is a bug, maybe I am just not clever enough. I am doing the L4-BD and run in the first exercise into troubles.
01 Practicing with Traditional Databases.knwf (26.6 KB)
First, I was puzzled, that dragging in a DB Table Creator did only sport a DB Input port. I created a second port using the magic … and was worried that it was KNIME Data Table port (filled triangle) and not some kind of KNIME Data Table Specs port (empty triangle) as shown in the video of the course (Database Structure Manipulation - YouTube). I choose to ignore that, thinking I need the data anyway.
Then, I executed the table creator, and wanted to see what happened to the database, to notice, I could not find the table.
But surely, I would get an error when inserting the data… I did not. And the data looks clean, too.
Ignoring that, I created a DB Row Filter. However, KNIME tells me, there was an error. I did take a moment, until I remembered to look in the console. And there is the error message, put quite noisily, and it triggered a firework of questions.
WARN DB Row Filter 7:63 org.h2.jdbc.JdbcSQLException: Syntax Fehler in SQL Befehl "SELECT * FROM (SELECT * FROM ( SELECT * FROM ""airline"" ) AS ""tempTable_4655089729863673237""
WHERE ( ""Cancelled"" = 0 AND ""Dest"" = ''AUS[*]'' )
) AS ""tempTable_6673901315614516116"" WHERE 1 = 0"; erwartet "[, ::, *, /, %, +, -, ||, ~, !~, NOT, LIKE, ILIKE, REGEXP, IS, IN, BETWEEN, AND, OR, ,, )"
Syntax error in SQL statement "SELECT * FROM (SELECT * FROM ( SELECT * FROM ""airline"" ) AS ""tempTable_4655089729863673237""
WHERE ( ""Cancelled"" = 0 AND ""Dest"" = ''AUS[*]'' )
) AS ""tempTable_6673901315614516116"" WHERE 1 = 0"; expected "[, ::, *, /, %, +, -, ||, ~, !~, NOT, LIKE, ILIKE, REGEXP, IS, IN, BETWEEN, AND, OR, ,, )"; SQL statement:
SELECT * FROM (SELECT * FROM ( SELECT * FROM "airline" ) AS "tempTable_4655089729863673237"
WHERE ( "Cancelled" = 0 AND "Dest" = ''AUS'' )
) AS "tempTable_6673901315614516116" WHERE 1 = 0 [42001-196]
WARN DB Row Filter 7:63 org.h2.jdbc.JdbcSQLException: Syntax Fehler in SQL Befehl "SELECT * FROM (SELECT * FROM ( SELECT * FROM ""airline"" ) AS ""tempTable_5898744010872012163""
WHERE ( ""Cancelled"" = 0 AND ""Dest"" = ''AUS[*]'' )
) AS ""tempTable_7328043405671067671"" WHERE 1 = 0"; erwartet "[, ::, *, /, %, +, -, ||, ~, !~, NOT, LIKE, ILIKE, REGEXP, IS, IN, BETWEEN, AND, OR, ,, )"
Syntax error in SQL statement "SELECT * FROM (SELECT * FROM ( SELECT * FROM ""airline"" ) AS ""tempTable_5898744010872012163""
WHERE ( ""Cancelled"" = 0 AND ""Dest"" = ''AUS[*]'' )
) AS ""tempTable_7328043405671067671"" WHERE 1 = 0"; expected "[, ::, *, /, %, +, -, ||, ~, !~, NOT, LIKE, ILIKE, REGEXP, IS, IN, BETWEEN, AND, OR, ,, )"; SQL statement:
SELECT * FROM (SELECT * FROM ( SELECT * FROM "airline" ) AS "tempTable_5898744010872012163"
WHERE ( "Cancelled" = 0 AND "Dest" = ''AUS'' )
) AS "tempTable_7328043405671067671" WHERE 1 = 0 [42001-196]
WARN DB Row Filter 7:63 org.h2.jdbc.JdbcSQLException: Syntax Fehler in SQL Befehl "SELECT * FROM (SELECT * FROM ( SELECT * FROM ""airline"" ) AS ""tempTable_2859893531269370669""
WHERE ( ""Cancelled"" = 0 AND ""Dest"" = ''AUS[*]'' )
) AS ""tempTable_1060529789303461657"" WHERE 1 = 0"; erwartet "[, ::, *, /, %, +, -, ||, ~, !~, NOT, LIKE, ILIKE, REGEXP, IS, IN, BETWEEN, AND, OR, ,, )"
Syntax error in SQL statement "SELECT * FROM (SELECT * FROM ( SELECT * FROM ""airline"" ) AS ""tempTable_2859893531269370669""
WHERE ( ""Cancelled"" = 0 AND ""Dest"" = ''AUS[*]'' )
) AS ""tempTable_1060529789303461657"" WHERE 1 = 0"; expected "[, ::, *, /, %, +, -, ||, ~, !~, NOT, LIKE, ILIKE, REGEXP, IS, IN, BETWEEN, AND, OR, ,, )"; SQL statement:
SELECT * FROM (SELECT * FROM ( SELECT * FROM "airline" ) AS "tempTable_2859893531269370669"
WHERE ( "Cancelled" = 0 AND "Dest" = ''AUS'' )
) AS "tempTable_1060529789303461657" WHERE 1 = 0 [42001-196]
Why is the SQL multiple times there? Where does the [*] after AUS come from when it was not configured like that?
Why is the SQL not always the same? The last one does not sport this [*].
Why is the single quote doubled as well when the whole SQL is put into double quotes?
Would it be possible to get the SQL straight (no doubling/escaping of stuff) so we can copy, paste and run it in our DB tool of choice without having to fiddle with it?
And the showstopper for me. If I remove the doubling and run the query, it runs without syntax error. Having that said, because I do not know where the table is supposed to have been created, it complains about the table not being there, to little surprise.
Well, here I am stuck. I did not mean to rant, and my apologies, if you feel that way. It is just a bunch of question marks having popped up over my head.
Kind regards
Thiemo