I have a complex sql ‘with statement’ that I enter into DB SQL executor. I’m using this node because DB Query Reader doesn’t recognize this “complex” statement.
My next pass through is to take the output from DB SQL executor and pass it through a series of String Replacer nodes to eliminate the CR/LR. (I know I can write sql queries for this, but i think it’s important for me to learn some workarounds and use the nodes available.
- Microsoft SQL Server Connector
- DB Executor
- String Replacer (trying to connect this to DB Executor / figure out a way to do this easily)
SQL query that is executable within DB SQL Executor, but not in DB Query Reader
With STEP1 as (
select * from table_one
select * from step1
left join table_two
on a.common = b.common
this is a simple version
The DB SQL Executor will execute the SQL on the server. If you want to use the result you would have to state so, typically by creating a table:
CREATE TABLE my_schema.my_table AS
Then you can use other DB nodes. If this was your question.
As @mlauber71 rightly says (and you’ve found) the DB Sql Executor isn’t going to return you the results directly as it is for execution only. However, I’m interested to know how complex your DB Query Reader query is, as when I put together a workflow using the construct of your example query, it worked for me with a MySQL database.
It’s quite possible that your query, being much more complex than this example, is breaking something though. In my previous life, I had a similar issue with the Alteryx product in that it had trouble with some more complex sql constructs such as “With” clauses. Are you absolutely certain though that the query isn’t failing for some other reason, and you’ve presumably tried the query outside of Knime?
If @mlauber71’s suggestion of creating a table containing your data is not an option, then an alternative and similar solution might be creating a view on the database containing your complex query and then querying that view from a DB Query Reader.
thanks to the both of you for the quick replies. in the middle of the night is not the best time for quality assurance. DB query reader did handle the SQL statement. turns out i just needed to remove the ;
at the end of the statement and it worked perfectly.
This topic was automatically closed 182 days after the last reply. New replies are no longer allowed.