Use Dynamic content in Data Services Controls

dataservices job design
1

I would like to know if there is a way to use dynamic content in Data Services Controls.

For example in a query I would like to be able to read the where clause from a database table, I tried with lookup_ext but DS does not accept this.

Any pointers ?

wayra_ar (BOB member since 2010-04-23)

2

Hi,
Consider this a pointer, rather than a definitive answer. I have to say I have never done it but from what I have read I think the pushdown_sql function sounds like it ought to do the job for you.
Would be interested to know if you get it to work!

twtw (BOB member since 2010-05-10)

3

Hi

Thanks a lot for the hint !
I’ve been trying it, it works but has limitations

This first variation works:

Script - DF ( Table - Q1 - Q2 - TempTable)

Script initializes variables with where clauses read from the DB
Q1 applies a first selection with pushdown_sql where clause from a first variable
Q2 applies a 2nd selection with pushdown_sql where clause from a 2nd variable

This 2nd variation does not work:

Script - DF ( Table - Q1 - Q2 - TempTable1)
- Q3 - TempTable2)

Here the intention was to apply in parallel 2 further selections to the result of the 1st selection, but this does not work, I get the error message:

" View contains a call to function <pushdown_sql>, which attempts to push to the underlying database the SQL String passed to the function. This function cannot be pushed down. Please reorganize the data flow components so that it can be pushed down "

[/img]

wayra_ar (BOB member since 2010-04-23)

4

That’s interesting and I’m pleased that at least you have a solution. But I’m afraid I can’t help any further - I guess it’s just a limitation of the product. You now know more about it than me!

twtw (BOB member since 2010-05-10)

5

The pushdown_sql() function works only in those cases where something is pushed down. Imagine a dataflow like

Source -> Hierarchy_Flattening -> Query_with_pushdown_sql -> …

How should we push the text of the function into the reader sql? So it only works if all queries can be combined. In your case you are lucky with the first two, but the third is executed by the engine hence no pushdown_sql() possible anymore.

In other words, I would call it a requirement that all pushdown_sql() functions are in the first query. If that’s not possible, I have no solution for you either other than creating a database view of same name but different where clauses in the script and the read from this view in the dataflow.

Werner Daehn :de: (BOB member since 2004-12-17)