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.