What’s the appropriate syntax for using an Oracle db link (e.g. schema.tablename@dblink )? Using a table selector, the only way to inject the link into the table name is to include it in the table input field, but it still isn’t connecting correctly.
SELECT * FROM (SELECT * FROM “schema”.“tablename@dblink”) “tempTable_1046208286391023629” WHERE rownum <= 10
ORA-00942: table or view does not exist
Also, if you have fairly complex queries with many table joins is it really necessary to deconstruct and rebuild them using separate table selects and joiner nodes?
Hello @DataSciGuy,
regarding the db link, I’m not to familiar with Oracle db links but you should be able to use them by appending the remote db link name using the ‘@’ to the table name so it would look like this in the DB Table Selector node:
Please notice that KNIME by default delimits all identifier so make sure to use the right cases e.g. write everything in upper case which is the default in Oracle. Yo can also disable the identifier delimiting via the Advanced tab of the Oracle Connector node.
If you are familiar with SQL you can also simply paste your SELECT statement into the DB Table Selector node by enabling the “Custom Query” option.
This will allow you to use other DB nodes e.g. DB GroupBy or DB Row Filter downstream to work with the selected table. If you directly want to execute the query and retrieve the data you can use the DB Query Reader node.
Regarding complex queries, yes if you are not familiar with SQL you have to deconstruct the query into several DB Table Selector and DB Joiner nodes. However if you are comfortable writing your own SQL KNIME offers you many ways to incorporate this into your workflow e.g. via the mentioned DB Query Reader node or embedded into native KNIME DB nodes using the DB Query node.
I was not able to link by putting the @linkeddb in the table field. However I was able to get the link to execute by checking the Custom Query box in the DB Table Selector node and typing the SQL command in (e.g. SELECT * FROM SCHEMA.TABLE@LINKEDDB)
Hi @ceagle,
I’m glad you made it work via the custom query. Could you maybe try again to append the @linkeddb to the table name and execute the Table Selector node but before doing so disable the identifier delimiting via the Advanced tab of the Oracle Connector node by removing the opening and closing delimiter. It should look like this:
Thanks for breaking that step out.