Using Custom Function in Where Clause

[Former Member]

Hi ,

Can we use Custom Function in WHERE clause with an IN.

Like the Below example in the Where clause-

Table.FieldName in (  CustomFunction ( )  )

Basically i want to send Multiple values in the Where Clause through a Custom Function dynamically.

can we achieve this in BODS?

Thanks

Ashiq

[Former Member]

Hi Ashique,

         we can achieve this in BODS by Dynamic where clause.

         Use this below Built-in function.for Dynamic where clause.

         pushdown_sql('DATASTORE_NAME','COLOUMN_NAME  in ({$variable_name})')

Note:For details of this function refer this link http://help.sap.com/businessobject/product_guides/sboDS41/en/sbo411_ds_tech_manuals_en.pdf

Pageno:1714

For this i have created a small job as below

   

in Script

call the custom function and assign the return value of custom function to Variable

$i

for custom function we have to give input parameter like 3 or 4 or 11 according to your req

custom function i have created is to understand ,how to use custom function in dynamic where clause .we cannot see these type of custom functions in real time scenarios.

while($count >0)

begin

$str =$str||','|| gen_row_num( );

$count =$count -1;

end

$len =length( $str);

$str =substr( $str ,2,$len-1);

return $str ;

output:

if count =3

return value of custom function will be like this 1,2,3

if count=5

return value of custom function will be like this 1,2,3,4,5

so that we can specify the count as per req

here we can see the dataflow

in my example i have taken flat file as source ,if you use flat file as source pushdown_sql does not push down the  where clause to database

so to push down i have used data transfer

in Query transform where clause is like pushdown_sql('ds','ENO in({$i})')

if you give input parameter $count=5 ,the return value of custom function will be like this 1,2,3,4,5

in script i have assigned this value to variable $i

i have used this variable $i in pushdown_sql() function like this pushdown_sql('ds','ENO in({$i})')

while execution where clause will be like this ENO in (1,2,3,4,5).

the output will be like this

[former_member202087]

Without knowing the details of your custom function I believe this can be done, however, the "return" from the custom function would have to bring back the data as it would look in a typical IN statement.

i.e. ('field1','field2','field3'...)