Complex SQL (Create View) Using In-DB

[JonPoole83]

Hello everyone.

I am trying to run a complex SQL query in Designer using the Connect In-DB tool. The query contains multiple "Create View" statements which seem to be an issue using this tool. I can connect to my database and run simple queries that do not contain any "Create View" statements and are just used for reading and pulling data.  The error message I get is below:

Is there a way to change the connection so that it is not read only so that the "Create View" statements will work properly?

[BrandonB]

Hi @JonPoole83 

The Connect In-DB and the Input Data tools tend to be a bit picky on what data can be read in because the objective is to pull data down into a workflow. With the Input Data tool you can use Pre and Post SQL which is much more flexible on the types of queries that can be run. Could you possibly use an Input Data tool where you run the PreSQL to create the view and then query it with the main query in the Input Data tool? 

[simonaubert_bd]

Hello @JonPoole83 

You can use a little trick by using the pre-sql statement of an input data tool. I do it inside a macro. Cf attachment.

Best regards,

Simon