Solved

Looping record by record in Ataccama One Desktop

  • 25 February 2022
  • 7 replies
  • 203 views
S
Badge

We have one use case where we are looking for a feature of looping the records and reading it record by record ..  Can you please share any template plan for the same.. 

icon

Best answer by farshid_mashagh 18 March 2022, 14:06

View original

7 replies

T
Rank

Hi @srikanth.gurram, could you please share some more details about what it is you are trying to do? Are you looking to implement some sort of for-cycle to trigger an action for every record? Or do you have some specific requirements for reading data from some special source?

I’d be happy to suggest a solution once I can understand this a little more.

 

S
Badge

Thanks  @tombolek for your reply.. 

 

Here is the use case : 

 

Below is the table details that we are using : 

Record Numbers :: Grp_Name :: sql_query

Record no = 1 | ‘DQ1’ | ‘select count(1) from tab1;’

Record no = 2 | ‘DQ1’ | ‘select count(1) from tab2;’

 

We want to loop through the records [ record by record ] and execute the query present in each row [sql_query] output the results into another table : 

 

Below is the output that we have expecting in the result table : 

 

Record Numbers :: Grp_Name :: sql_results

Record no = 1 | ‘DQ1’ | ‘200’ 

Record no = 2 | ‘DQ1’ | ‘300’

 

To summarize.. we are looking for a looping mechanism [ record by record ] and output the results into another table .

 

If you have any template or a sample plan, can you please share ..

 

Regards,

Srikanth Gurram

F
Rank
Badge +1

Dear Srikanth,

Unfortunately we don't have any templates or sample plans containing steps and procedures you are looking for. Your request will require billed effort from our professional services team in order to come up with the custom solution. Having said that, we tried to get some general guidance and expert opinion regarding your request and would like to share with you the opinion of one of our manager consultants regarding the use of SQL Execute / SQL Select in your use case. The use of SQL Execute is not likely as it doesn't return the dataset. It will have to be parameterized SQL Select step. Regarding this example: Record no = 1 | ‘DQ1’ | ‘select count(1) from tab1;’ Record no = 2 | ‘DQ1’ | ‘select count(1) from tab2;’ If it's really just about the tab1 and tab2 parameterization, it could work but we will have to test this.

Thanks and regards,

M
Userlevel 4
Badge +3

You could write a stored procedure with a little bit of dynamic code to loop the queries with a cursor.  Execute this from a workflow which contains the rest of the plans to execute. Would you know how to do this?

 

But some important info: Is the output of the queries always the same? If not you need a bit more complex code in the stored procedure, namely a pivot.

 

 

 

M
Userlevel 4
Badge +3

We have a sample plan (v13.7) for you, if you are interested. I see no way to share.

DannyRyan
Rank
Userlevel 5
Badge +9

Hi @Marnix Wisselaar 

I have enabled file upload/attachments. You should now be able to upload using the paperclip icon.
 

Upload Files

 

Danny | Did it answer your question? Mark it as the correct answer!
M
Userlevel 4
Badge +3

See Iterating rows of a table (Record-by-record - SQL Iterator) in a workflow | Community (ataccama.com)

Executing dynamic queries is another post you can find on the community.

Reply


Terms & ConditionsCookie settings