I am new to CloverETL and I want to execute a SQL statement that results in additional select statements to then be executed. The first statement dynamically retrieves the schemas in a Vertica database. The result statements are select statements for each schema that insert audit information into a Vertica system table.
The first statement is:
select distinct 'select audit('''||table_schema||''');' from tables;
The results from the first query are, for example:
select audit('schema1');
select audit('schema2');
select audit('schema3');
select audit('schema4');
select audit('schema5');
I am using a DBInputTable component to execute the first query then sending the output as a discrete query to a DBExecute component. I am getting an error "A RowCount was expected but not generated from query". I was reading on the forums that DBExecute does not take select statements even though in this particular case it inserts records into a system table. What can I use to execute the resulting select statements?
The first statement is:
select distinct 'select audit('''||table_schema||''');' from tables;
The results from the first query are, for example:
select audit('schema1');
select audit('schema2');
select audit('schema3');
select audit('schema4');
select audit('schema5');
I am using a DBInputTable component to execute the first query then sending the output as a discrete query to a DBExecute component. I am getting an error "A RowCount was expected but not generated from query". I was reading on the forums that DBExecute does not take select statements even though in this particular case it inserts records into a system table. What can I use to execute the resulting select statements?
-
Hi,
I would recommend that instead of using DBExecute you use another DBInputTable to process your queries. The DBinputTable should have the following settings:- DB connection should have your Vertica connection.
- In the Query URL click on Port tab and select your input port field (make sure your Processing Type is set to discrete).
- Run your graph
-
Hi,
I would recommend that instead of using DBExecute you use another DBInputTable to process your queries. The DBinputTable should have the following settings:- DB connection should have your Vertica connection.
- In the Query URL click on Port tab and select your input port field (make sure your Processing Type is set to discrete).
- Run your graph
Hi Pedro, I'm trying to do this but it wont let me set the processing type to discrete for some reason.
Please sign in to leave a comment.
Comments 4