I want to split my difficult SQL queries into several Database Query Nodes. Input table in Database Query Node designated by #table#. I need to write in this node queries like this:
SELECT * FROM #table# t1
WHERE NOT EXISTS (
SELECT * FROM #table# t2
WHERE t1.Name = t2.Name
AND t1.Version = t2.Version
AND t1.Parent = t2.Parent
But there are no possibility to use aliases (like t1 in the example over). I tried to use several syntax variants: as t1, as ‘t1’, as “t1”, AS t1, AS ‘t1’, AS “t1”, t1, ‘t1’, “t1”. No one works. Please advise how to solve this issue.
By the way, Database Connector Node allow to use alises for tables. And now I use difficult SQL queries in Database connector Node.
the statement looks good to me with the exception that it will never return a single row if you replace the #table# placeholder with the same database table.
What is the error message you encounter performing the query and what database are you using?
Yes, this query will never return a single row. But it is a simple example. My real queries have additional conditions. I get the following messages “WARN DatabaseReaderConnection PreparedStatment not support by database: [Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near ‘t1’.
WARN Database Query java.sql.SQLException: [Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near ‘t1’.” Database: Microsoft SQL Server. And I use standard database driver in KNIME - sun.jdbc.odbc.JdbcOdbcDriver.
I have tryed a similar query using odbc with an oracle db (unfortunately I don’t have SQL server) and had no problems with the table alias. Have you tryed the same statement using another db client? Can you post the problematic sql statment or try a simpler statment to check if the table alias is the problem.
The following simple query “SELECT * FROM #table# t1” caused an error “WARN DatabaseReaderConnection PreparedStatment not support by database: [Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near ‘t1’. WARN Database Query java.sql.SQLException: [Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near ‘t1’”. The similar query without table alias “SELECT * FROM #table#” works good. I used MS SQL Server via ODBC with driver “sun.jdbc.odbc.JdbcOdbcDriver”. I can not check it in Oracle database. And I don’t know which version of SQL Server I am using exaclty. But I suppose it is 2005 or 2008 SQL Server. Which driver you are using? If you are using the same drivers then the problem laid out of driver.
I made a mistake. I tryed your statement in the Database Connector where it works. In the Database Query node the statement does not work because the node adds internal a table alias to the statment e.g. “select * from #table#” is internal transformed to “select * from (previouse query) tableAlias” to support query node chaining. I will talk to Thomas if we can add table alias support in the query node for the next KNIME release.
A workaround would be to use the Database Connector node instead of the Database Query node if possible. This node does not modify the entered sql string and thus supports tabel aliases.
I know that table aliases works in Database Connector. I wanted to use such queries exactly in Database Query node because it makes queries simplier. There are several stages of data transformation in my queries. It’s big queries about several dozens SLOC. I wanted to split such complex queries into several Database Query nodes. It can’t be done cause to database aliases issue. In other hand several database Connector nodes can’t be used sequentally cause they give data from database.
So I beleive this issue will be fixed in future releases.
Hi all, just a short update. The database alias issue will be fixed in our up-coming minor release. Thanks again, Thomas
I am facing few issue in knime as below.
1. i am trying to convert Value Filepath to a Variable using "Value selection Quickform" under quickform node, and am feeding it to variable based file reader to read file in from the input path. but when i do this, for the first time, Output from "Value selection Quickform" reflects the correct value as input. However in next iteration, if the input value(Filepath) changes, output of "Value selection Quickform" still throw Previous Value(filepath). Which means the output of the same is not dyanamically changes the input changes.
please let me know if there are any nodes which convert input value to variable dyanamically as input changes. so that i can use variable based file reader.
2. In my work flow i have many CSV output to track the data. However when i give this entire workflow to my client, the Output path has to be configured once again for individual CSV output. Is there any way to use flow variable to write CSV as output? so that i need not configure all CSV output as per clients path.
3. When i transfer entire workflow to some other desktop, XLS reader will be correpted. It will fail to read the file if at all we browse file from correct location. At this point of time we will have to delete current XLS reader an will have to use new XLS reader.