Question Re: bods: how to implement subquery in Technology Q&A

bods: how to implement subquery

former_member186160 — Thu, 05 Oct 2017 12:30:51 GMT

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.

Re: bods: how to implement subquery

Former Member — Thu, 05 Oct 2017 15:22:35 GMT

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.

Re: bods: how to implement subquery

former_member186160 — Thu, 05 Oct 2017 16:46:38 GMT

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

Re: bods: how to implement subquery

former_member187605 — Tue, 10 Oct 2017 11:03:15 GMT

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.

Re: bods: how to implement subquery

former_member186160 — Tue, 10 Oct 2017 16:33:33 GMT

Hi Dirk,

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

Re: bods: how to implement subquery

Former Member — Mon, 16 Oct 2017 08:57:55 GMT

Hi,

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

Thanks

Re: bods: how to implement subquery

Former Member — Mon, 16 Oct 2017 08:59:59 GMT

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

Re: bods: how to implement subquery

former_member186160 — Sun, 22 Oct 2017 12:39:55 GMT

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.

Re: bods: how to implement subquery

former_member186160 — Sun, 22 Oct 2017 12:40:34 GMT

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

Re: bods: how to implement subquery

former_member186160 — Tue, 05 Feb 2019 17:09:44 GMT

thanks Dirk and Ravi for your answers.