Solved

How to Execute SQL Query using Components

  • 25 February 2022
  • 5 replies
  • 236 views

Userlevel 2
Badge +3

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.

icon

Best answer by Maksim Zhelyazkov 18 March 2022, 10:16

View original

5 replies

Userlevel 2
Badge +3

@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:

 

Userlevel 2
Badge +3

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 '?'.

 

 

Userlevel 2
Badge +4

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 

Userlevel 2
Badge +3

Closing the loop. This feature is not available in Attacama yet per the support. You need to provide the full query hardcoded and cannot use $(parameter)

Reply