Skip to Content
0

bods: how to implement subquery

Oct 05, 2017 at 12:30 PM

254

avatar image
Former Member

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.

10 |10000 characters needed characters left characters exceeded
Former Member

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

0
* Please Login or Register to Answer, Follow or Comment.

3 Answers

Dirk Venken
Oct 10, 2017 at 11:03 AM
1

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.

Show 1 Share
10 |10000 characters needed characters left characters exceeded
Former Member

Hi Dirk,

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

0
Ravi kiran Pagidi Oct 05, 2017 at 03:22 PM
0

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.

Share
10 |10000 characters needed characters left characters exceeded
avatar image
Former Member Oct 05, 2017 at 04:46 PM
0

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


zzzz.jpg (32.4 kB)
Show 3 Share
10 |10000 characters needed characters left characters exceeded
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

0
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

0
Former Member

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.

0