Options
Command to Execute SQL String
I have a SQL statement that is dynamically created; that is, it is comprised of a static SQL string with some dynamic value appended to it. How do I go about executing this statement? Is there a command for this? This would be equivalent to "EXEC sp_executesql SqlString" in SQLServer.
Also, what is the syntax for looping through a result set? Is there such a command? If so, can you please share an example of this?
What i'm trying to do is:
Also, what is the syntax for looping through a result set? Is there such a command? If so, can you please share an example of this?
What i'm trying to do is:
- Run a SELECT statement
- Loop through the result set to create the SQL string
- Run the SQL string
0
Comments
In your case, If you want to do everything within vertica, I see External Procedure as best solution,
However, If your result set is going to be bigger( even more then 10 records) I would not suggest this.
You need to understand, Vertica is an OLAP database with no support for procedural languages, what you are trying to achieve is part and parcel of procedural languages.
Coming to your questions
For executing a SQL(dynamic)
What platform are you going to construct your dynamic sql , if its any scripting language, then its pretty simple to execute it making a connection string and establishing a connection.
If not, and you want something like EXEC then create an external procedure which will run your query in Vertica ( This external procedure will make that number of calls / connection to vertica which will be equal to number of dynamic sql's generated , not recommended )
What is the syntax for looping through resultset ?
As I said, Vertica does not have any inbuilt procedural language support.
The way I achieve this is using SELF JOINS and CROSS JOIN.
Coming to complete solution for your sequence.
Run a select statement
--Make this statement as a SELECT clause for CREATE LOCAL TEMP TABLE and store all results in this temp table
Loop through the result set to create SQL string
--The temp table created previously has to undergo a SELF join and comeup / form SQL statement as result set, you need to store this result set in another temp table.
Run the SQL String
--Once the temp table with your SELECT are ready, just open connection from any scripting language and retrieve all the Queries in some array and execute them using a loop
I know this may sound a difficult workaround, but just give it a try.
Hope this helps
NC