What is wrong with my SQL?

[chiashin]

Hi people,

I’m using the DB Looping node to extract the data from Database based on the column lists of another table.

Here is the SQL code:

SELECT * FROM #table# AS `table`
WHERE (`id`, `somenumber`) IN ($ID$, $SOMENUMBER$)

Explanation: Basically, row will be retrieved from Database, if the value in id column AND somenumber column match with the value of (ID, SOMENUMBER). Both id and somenumber have to be matched.

I tried others like:

SELECT * FROM #table# AS `table`
WHERE (`id`, `somenumber`) IN (($ID$, $SOMENUMBER$))

SELECT * FROM #table# AS `table`
WHERE (`id`, `somenumber`) IN ((($ID$), ($SOMENUMBER$)))

Still, the same ERROR:
WARN DB Looping 3:228 Cannot retrieve an output data specification. Caused by Syntax error: Exception parsing query near ‘NULL’

Does anyone know how to fix this error?

Thank you.

---

For reference, the following is what I expected.
Retrieved from w3schools:

SELECT * FROM Customers
WHERE (City, Country) IN (('Berlin', 'Germany'), ('London', 'UK'));

[AlexanderFillbrunn]

Hi,
How do your ID and SOMENUMBER variables look like? Are they properly quoted? What if you copy the values of your variables and replace the placeholders in your SQL, replace #table# by the actual table name and then run the query in some other tool? Does it work then?
Kind regards,
Alexander

[chiashin]

Hi @AlexanderFillbrunn , thanks for the reply.

ID column consists of String value and SOMENUMBER consists of Integer value.

If they are quote:

SELECT * FROM #table# AS `table`
WHERE (`id`, `somenumber`) IN ('$ID$', '$SOMENUMBER$')

Return ERROR:

WARN DB Looping 3:228 Cannot retrieve an output data specification. Caused by This statement has no parameters

There are over 1000 rows of data, I don’t think it’s good to directly copy the values of variables and replace the placeholders in the SQL.

Also, I tried to replace #table# by the actual table name, still return the same ERROR:

WARN DB Looping 3:228 Cannot retrieve an output data specification. Caused by Syntax error: Exception parsing query near ‘NULL’

[AlexanderFillbrunn]

Hi,
Sorry, maybe I was not clear. In KNIME, the #table#, $ID$, and $SOMENUMBER$ placeholders are replaced by some values when they are executed. So I assume in the end your query should look something like this:

SELECT * FROM yourtable AS `table`
WHERE (`id`, `somenumber`) IN (('A', 'B', 'C'), (1,2,3))

But to make sure this is what is happening, I am asking you to assemble this query manually by copy&pasting the values into your query template. Then you can inspect the query and run it from other tools to find out what is wrong. E.g.: does $ID$ have the right format, i.e. (, , …), including the parentheses? If this is not the case, your query after copy&pasting in the values would look like this:

SELECT * FROM yourtable AS `table`
WHERE (`id`, `somenumber`) IN ('A', 'B', 'C', (1,2,3))

and this is obviously wrong.
Kind regards,
Alexander

[chiashin]

Hi @AlexanderFillbrunn ,

Based on the example you gave, I think I understand why there is ERROR:

SELECT * FROM yourtable AS `table`
WHERE (`id`, `somenumber`) IN (('A', 'B', 'C'), (1,2,3))

Obviously this is not what I intend. The following is what I expected:

SELECT * FROM yourtable AS `table`
WHERE (`id`, `somenumber`) IN (('A',1), ('B', 2), ('C', 3))

Imagine, the dataset has (‘A’,1), (‘A’,2), (‘A’,3), … but only row with (‘A’,1) returned. Same for B and C.

Do you know how to implement this in DB Looping node? using:

$ID$ and $SOMENUMBER$

---

Answer to your question, I do try to do it manually in other node called DB SQL Executor:

SELECT * FROM yourtable AS `table`
WHERE (`id`, `somenumber`) IN (('A',1), ('B', 2), ('C', 3))

However, ERROR returned:

ERROR DB SQL Executor 3:231 Execute failed: Syntax error: Exception parsing query near ‘’’

[AlexanderFillbrunn]

Hi,
Which database are you using? I could run the following query in SQLite:

SELECT * FROM #table# AS "table"
WHERE (id, somenumber) IN (VALUES ('A',1), ('B', 2))

Kind regards,
Alexander

[takbb]

Hi @chiashin,

I was going to start this by asking the same question that @AlexanderFillbrunn raised about which database you are using, as I noted that in some of your query examples you have apostrophes in it rather than double quotes around table and column identifiers, which would in itself be invalid syntax for many databases.

In terms of making the looping node work, here is an example from an Oracle database

Suppose I have a database table consisting of city information, each with a city_id, country_id and a city_name

I have a table creator, with a table consisting of two columns: cityid, countryid which are the set of ID tuples I wish to retrieve from the database table using the looping node, with the IN statement

The table creator node contains the following:

I configure the looping node as follows:

Note I have unticked “Read all”. I think you should too. (Just go with it, because I have no idea when it should be ticked as I have yet to run a query where ticking that works! Suggestions on a postcard! )

The query I have used is this:

SELECT * FROM #table# "table"
WHERE ("CITY_ID","COUNTRY_ID") in ( ($cityid$, $countryid$) )

Note that I have double-quotes around my identifiers, rather than apostrophes

If one of my columns had been a string instead of numeric, (maybe I had it referencing COUNTRY_NAME instead of COUNTRY_ID), I would have used the following:

SELECT * FROM #table# "table"
WHERE ("CITY_ID","COUNTRY_NAME") in ( ($cityid$, $countryname$) )

(WITHOUT single quotes around the $countryname$ token).

Note that this is unusual and a little inconsistent … there is a difference between the way String tokens from the “Column List” are included in the query, and the way String flow-variables are included.

Those from the column list are NOT placed inside single quotes, even when they contain String data, but flow-variables MUST be placed in single-quotes if they are representing String values.

e.g, supposing in addition to the column list, we also had a flow variable containing a country to be excluded from the returned query:

We could write this as follows:

SELECT * FROM #table# "table"
WHERE ("CITY_ID","COUNTRY_NAME") in ( ($cityid$,$countryname$) )
and COUNTRY_NAME <> '$${Sexclude_countryname}$$'

See how one is quoted and the other isn’t.

Of course if syntactically, quotes weren’t to be added (such as if the flow-variable represented a string to be used to dynamically replace a column in the select statement) then you wouldn’t include the quotes around it, but for most everyday use cases, you would include the quotes.

This is just something you need to be aware of.

[chiashin]

Hi @AlexanderFillbrunn and @takbb ,

For your reference,

I have tried the SQL code provided by both of you, still return ERROR:

WARN DB Looping 3:238 Cannot retrieve an output data specification. Caused by Syntax error: Exception parsing query near ‘NULL’

and

WARN DB Looping 3:238 Cannot retrieve an output data specification. Caused by Syntax error: Exception parsing query near ‘VALUES’

[takbb]

Hi @chiashin , well it is evident that you have syntax errors (because that is what the message is saying in both cases) and this can be any number of things which we obviously won’t he able to identify without being able to see what you have typed in the DB Looping node.

It is quite possible that the queries require minor adjustment to work with your MySql database and certainly the sql I wrote did not have the right column names etc as it was just an example.

It could also be down to the data that is being passed to the node. For example you may get errors if the values being passed contained single quotes.

But you will actually need to give us something to go on here… .

Database workflows are a little more difficult to debug remotely because obviously you can’t just send us a copy of your database with a workflow but some screenshots would be of assistance. Please can you post screenshots of the config of the DB Looping node (similar to the ones I posted) so that we can see the actual statements written that cause each of the errors that you have posted.

Phase also post screenshots showing a sample of the data table being passed to the upper port of the DB looping node.

Also please post the MySql table definition for the table being queried.

In all cases please ensure there is nothing sensitive in the screenshots.

One other screenshot would be useful at this point. Can you send a picture of the “advanced” settings tab in the MySql Connector node you are using.

[chiashin]

Hi @takbb ,

For your reference, the data looks like:
Dataset - Upper Port:
ID | SOMENUMBER | OTHER
AA12345 | 21 | A2145B
BB11234 | 11 | B2187K
AA12345 | 3 | T2187K
DD12456 | 6 | U6271L
BB11234 | 10 | O2517P

SELECT * FROM #table# AS “table”
WHERE (“id”, “somenumber”) IN ( ($ID$, $SOMENUMBER$) )

I already changed the Database Dialect to Oracle. The setting of DB Looping node is same as yours. I have checked that the values inside the dataset are not quoted neither single nor double. But, still the same ERROR returned:

WARN DB Looping 3:30 Cannot retrieve an output data specification. Caused by Syntax error: Exception parsing query near ‘NULL’

What do you mean by this?

Also please post the MySql table definition for the table being queried.

“Advanced” settings:

[takbb]

Hi @chiashin , thanks for the additional information.

The reason for asking for the screenshot of the DB Looping node is that I wanted to see exactly what was entered as the query rather than there being any chance of the text changing on writing it here to the forum. But if the query you have posted above is exactly what you have in the node, then it is invalid because it contains “smart quotes” instead of double quotes.

When you said you “changed the database dialect to Oracle”, why did you do that? Your database dialect should reflect the database you are using.

[chiashin]

Hi @takbb ,

The meaning of changing the Database Dialect is this:

From MySQL to Oracle

[mlauber71]

@chiashin

WHERE (“id”, “somenumber”) IN ( ($ID$, $SOMENUMBER$) )

have you considerd:

WHERE ("id") IN ( ($ID$, $SOMENUMBER$) )
AND ("somenumber") IN ( ($ID$, $SOMENUMBER$) )

or variations?

[takbb]

@chiashin

The meaning of changing the Database Dialect is this:

From MySQL to Oracle

Yes… But why change it ?

[takbb]

Hi @mlauber71 , that example would have to be

WHERE "id" IN ( $ID$ )
AND "somenumber" IN ( $SOMENUMBER$)

as otherwise it is comparing single columns to tuples consisting of ($ID$, $SOMENUMBER$)

[chiashin]

Hi @takbb ,

I’m just testing. Just ignored that. I’ll switch back to MySQL coz Oracle is having some issue.

[takbb]

Hi @chiashin , more importantly can you confirm you definitely don’t have smart quotes in the query?

i.e. Which of the following does your query look like?

SELECT * FROM #table# AS "table"
WHERE ("id", "somenumber") IN ( ($ID$, $SOMENUMBER$) )

and not

SELECT * FROM #table# AS “table”
WHERE (“id”, “somenumber”) IN ( ($ID$, $SOMENUMBER$) )

(the lower one is what you pasted above)

[takbb]

Hi @chiashin

I just realised from that screenshot that the Database isn’t MySQL… It’s Denodo! Ok, testing different dialects might make sense, as I have no prior experience of Denodo and so I have no idea whether its sql syntax conforms to a particular “dialect”.

Let’s go back to basics. Does Denodo have a place (either in an app or a browser page) where you can open some kind of sql worksheet and type commands in?

I’m going back to using @AlexanderFillbrunn example to prove the syntax… does the following query work if your denodo database is queried directly (outside of KNIME)?

SELECT * FROM yourtable AS "table"
WHERE ( "id", "somenumber" ) IN ( ( 'AA12345' ), ( 21 ) )

because if that doesn’t work in Denodo, then it isn’t going to work using KNIME. What we need to find out is what syntax works with your database.

If it doesn’t work, then taking @mlauber71 's suggestion, does the following work directly against the Denodo database?

SELECT * FROM yourtable AS "table"
WHERE "id" IN ( 'AA12345' )
AND  "somenumber" IN (  21 )

[mlauber71]

@chiashin Ok here is an example using MySQL with the syntax created like this using group by and string manipulation:

SELECT * FROM (SELECT * FROM `db_knime`.`my_table_00`
) AS `table`
WHERE `ID` IN ('AA12345', 'DD12456')
AND `SOMENUMBER` IN (21, 3, 6)

The string values are enclosed in single quotes and are concatenated thru group by node.

create table in MySQL and create a dynamic WHERE clause with integers and...

kn_example_db_mysql_where_clause.knwf (76.0 KB)

[denisfi]

Hi guys,

Just a question for a step before all of this…

Can exist “null” as string value or a empty data from the source? because maybe you can’t search something that not exists or not match at all… You try to search “null” and this result can cause the error? what do you think about it?

OR can you make the search as string operation from the fields? try it too to see the output… if you don’t have much rows to explore it.

Tks,

Denis