Question

How to Execute SQL Query using Components

  • 25 February 2022
  • 4 replies
  • 112 views

Badge

Hi Team, I have bunch of queries sitting in my Oracle tables(queries in one of the column). I am trying to see if I can read the oracle table as source and use the column values(which has sql queries ) to be sent to database & get return value. Is there a component that can help me achieve that?

 

jdbc reader, sql execute components expect you to hard code the query. But in my case the query will come as value from one the table. 

 

eg)

 

Col1 Col2 

1 select count(*) from tab A 

2 select count(*) from tab B

 

In the above example I wanted to use col2 as input and pass the values from Col2 to DB and assign the output to next transformation. Hope its clear.


4 replies

Badge

@DannyRyan 

This is not something I can check right now on my end, but I believe that you can actually pass an existing query to the SQL Execute step if it’s in one of your data columns. The notation, I believe, is this:

 

Badge

Thank you @tombolek . You are exactly rite, If it allows me to run using SQL Execute, it would totally match my scenario.

 

When i tried running using SQL Execute with the option suggested, I am getting below errors, Could you please check if this is what you see as well ? 

 

execute (${SOURCE_SQL}) - syntax error line 1 at position 8 unexpected '('.

 

execute ${SOURCE_SQL} - syntax error line 1 at position 8 unexpected '?'.

 

 

Badge

Hello Siva,

This case is handled directly with the Ataccama Support team, but for transparency I am writing here that the error message is related to the syntax of your query. In such situations the suggested approach is going through the whole query and checking for any mistakes. 

 

Regards,

Maksim 

Reply