bods: how to implement subquery

[former_member186160]

hi all,

i am having very complicated query to be implemented in bods.

usage of sql transform is strictly prohibited in teh project.

there are several subqueries and several selfjoin etc required, please suggest how to implement this in bods.

[former_member186160]

converting the sql to bods components became a huge dataflow, i am starting to test if the efforts are really worth the performance 🙂

[Former Member · Accepted solution]

Hi Swetha N,

say you have a subquery like

select id,name from TABLE_A where id in (select id from TABLE_B);

step 1:- Take TABLE_A and TABLE_B as sources in a dataflow,

step 2:- Take a Query transform after TABLE_B and map only ID column in the schema_out.

step 3:- Take a Query transform after TABLE_A and map ID, NALE in the schema out.

step 4:- join Query of TABLE_A and Query of TABLE_B on column TABLE_A.ID=TABLE_A.ID.

in case of self join, take the table 2 times as sources in Dataflow and join.

Split that complicated query into several dataflows in BODS for better performance, dont try to do in a single dataflow.

If data is huge and you want to pushdown the query to database, then perform step 2 above in a dataflow and stage data in a table, and step 3 in a dataflow and stage data in a table and step 4 in a dataflow where you can join the the tables of step 2 and step 3 and full pushdown is possible.

Thanks,

Ravi kiran.

[former_member187605]

Don't start from the SQL statement, but from the logic behind it. What are the source data? What are the desired results? Once you've understood, you can easily implement that logic in a data flow.

[former_member186160]

Hi Dirk,

thanks for the suggestion, i will check on this direction.

[former_member186160]

thanks Dirk and Ravi for your answers.

[former_member186160]

Hi Ravi,

i have many segments like in the below part of the code being repeated multiple times in my main query.

Please suggest the best approach to implement this in bods.

please note that the same table CL is called B and used again as Y.

Regards,

Swetha

[Former Member]

Hi,

If the inner query is repeated multiple times, you can use script and call sql function and store the value in variable.

Thanks

[Former Member]

Hi,

If the inner query is repeated multiple times, you can use script to call sql function and store the value in a variable. you can then use this variable.

Thanks

[former_member186160]

Hi Sweekriti,

thanks for your suggestion.

i meant same pattern is repeated but not the same text, so i cannot use the function call here.