Problems trying to modify a SQL query using DB Query node with Oracle

[szawadski]

Hello!
I am experiencing errors using the DB Query node.

Use case
I am trying to add a calculated column to a table at DB level (like for instance a substring as a new column).

Problem
I have followed the example in the documentation.
Unfortunately, I am not able to execute the workflow as I get an error message from the Oracle DB

Error messages
Depending whether I put the additional column before or after the * of the select statement, I am getting a different error message

Same behaviour applies when I try to modify the query in the DB table selector.

Generated SQL
The generated SQL seems not compliant with the Oracle syntax
SELECT *
FROM (SELECT Substr(item_code, 1, 3) AS subcode,
*
FROM (SELECT *
FROM tma_read.fact_events
WHERE event_creation_date_yyyymmdd BETWEEN
20170101 AND 20190731)
“table”) tempTable_3806047056944389584
WHERE rownum <= 10
while we should have
SELECT *
FROM (SELECT Substr(item_code, 1, 3) AS subcode,
tempTable_3806047056944389584.*
FROM (SELECT *
FROM tma_read.fact_events
WHERE event_creation_date_yyyymmdd BETWEEN
20170101 AND 20190731)
“table”) tempTable_3806047056944389584
WHERE rownum <= 10

I could solve it by prefixing the “star” in the DB Query node.

But I do not know whether I am supposed to do so or whether the DB Query node should build a query compatible with the used database.

[marten_kose]

Hi @szawadski,

Thanks for hunting down this problem and providing the solution. Oracle DB expects the very specific identifier here. As we do not perform any checks on the inserted SQL statement from the editor, one has to make sure that the statement is compliant with Oracle syntax, so the solution you provided is the way to go here.

Best,
Marten