Exception using SQL code to access TDV TIBCO

[Aldemir]

The TI’s support said the problem is at limit 10. I don’t get it. I didn’t put this limit in the SQL’s code.

The SQL’s code:

The error:
An exception occurred when executing the following query: “SELECT * FROM (SELECT CD_DOC_FATURAMENTO, DT_ANO_MES, CD_NOTA_FISCAL, CD_DOC_VENDAS, QN_VOLUME, QN_FATURADA, VL_FATURADO, CD_DOC_FATURAMENTO__F, DT_ANO_MES__F, CD_NOTA_FISCAL__F, CD_DOC_VENDAS__F FROM BW.VENDAS WHERE DT_ANO_MES__F > ‘201201’ ) AS “tempTable_4842091935925292634” LIMIT 10”. Cause: Unable to parse query text: Incorrect syntax near “FROM”, found “LIMIT”. On line 4, column 39. [parser-2904201]
com.compositesw.common.UserException: Unable to parse query text: Incorrect syntax near “FROM”, found “LIMIT”. On line 4, column 39. [parser-2904201]
at com.compositesw.common.UserException$Builder.build(UserException.java:165)
at com.compositesw.server.dbchannel.AbstractCommand.sendError(AbstractCommand.java:195)
at com.compositesw.server.dbchannel.AbstractCommand.sendError(AbstractCommand.java:184)
at com.compositesw.server.dbchannel.ServerCommand.exec(ServerCommand.java:786)
at com.compositesw.server.dbchannel.ServerCommand.dispatchCommand(ServerCommand.java:159)
at com.compositesw.server.dbchannel.ServerCommand.run(ServerCommand.java:86)
at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1128)
at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628)
at java.base/java.lang.Thread.run(Thread.java:834)
Caused by: com.compositesw.cdms.services.parser.ParserException: Unable to parse query text: Incorrect syntax near “FROM”, found “LIMIT”. On line 4, column 39. [parser-2904201]
at com.compositesw.cdms.services.parser.ParserImpl.parseSqlOrDsl(ParserImpl.java:231)
at com.compositesw.cdms.services.parser.ParserImpl.parseSqlOrDsl(ParserImpl.java:147)
at com.compositesw.server.qe.QueryEngine.a(SourceFile:1583)
at com.compositesw.server.qe.QueryEngine.a(SourceFile:263)
at com.compositesw.server.qe.QueryEngine.execute(SourceFile:199)
at com.compositesw.server.qe.QESqlRequest.execute(SourceFile:152)
at com.compositesw.server.request.HookSqlRequest.execute(HookSqlRequest.java:65)
at com.compositesw.server.dbchannel.CsExec.exec(CsExec.java:121)
at com.compositesw.server.dbchannel.CsExec.exec(CsExec.java:72)
at com.compositesw.server.dbchannel.ServerCommand.exec(ServerCommand.java:756)
… 5 more
Caused by: line 4:39: Incorrect syntax near “FROM”, found “LIMIT”
at com.compositesw.cdms.services.parser.DdlParser.createException(DdlParser.java:89)
at com.compositesw.cdms.services.parser.DdlParser.createException(DdlParser.java:64)
at com.compositesw.cdms.services.parser.DdlParser.from_clause(DdlParser.java:20479)
at com.compositesw.cdms.services.parser.DdlParser.table_expression(DdlParser.java:13486)
at com.compositesw.cdms.services.parser.DdlParser.query_specification(DdlParser.java:18165)
at com.compositesw.cdms.services.parser.DdlParser.simple_table(DdlParser.java:18029)
at com.compositesw.cdms.services.parser.DdlParser.non_join_query_primary(DdlParser.java:17993)
at com.compositesw.cdms.services.parser.DdlParser.non_join_query_term(DdlParser.java:17844)
at com.compositesw.cdms.services.parser.DdlParser.non_join_query_expression(DdlParser.java:17329)
at com.compositesw.cdms.services.parser.DdlParser.query_expression(DdlParser.java:11376)
at com.compositesw.cdms.services.parser.DdlParser.direct_select_statement_multiple_rows(DdlParser.java:5192)
at com.compositesw.cdms.services.parser.DdlParser.direct_sql_data_statement(DdlParser.java:16793)
at com.compositesw.cdms.services.parser.DdlParser.directly_executable_statement(DdlParser.java:16410)
at com.compositesw.cdms.services.parser.DdlParser.start_rule(DdlParser.java:247)
at com.compositesw.cdms.services.parser.DdlParser.sql_or_dsl_start_rule(DdlParser.java:204)
at com.compositesw.cdms.services.parser.ParserImpl.parseSqlOrDsl(ParserImpl.java:213)
… 14 more
[jdbc-1906510]

The solution using Dbeaver:

[Aldemir]

Problem resolved but I still need to use SQL to reduce the volume of data. I removed SQL and loaded the entire table. There is something that makes SQL do a LIMIT and then gives error.

[ScottF]

Hi @Aldemir -

Thanks for posting. I’m checking internally to see if this is a a bug we need to follow up on.

[tobias.koetter]

Hello @Aldemir ,
when you click on the evaluate button in the custom query area KNIME will automatically surround the input statement with a limit statement to minimize the amount of data to read from the db. This is also shown in the Parsed Statement section where you see your altered input query. Unfortunately the DB you are connecting to is unknown to KNIME AP and does not support the standard LIMIT syntax which is why the evaluation fails.
As a solution, do not use the Evaluate button but close the node dialog and execute the node once you have entered your custom query. This way KNIME is not altering the statement and the node should execute fine.
Also if you do not want to alter the query further but simply want to get the result into KNIME you can paste the query in the DB Query Reader node.
Bye
Tobias

[Aldemir]

Thanks! Solved! I Understood your explanation!