Skip to Content
0

HANA SDI FLOWGRAPH - How to pass dynamic value to variable in Filter

Nov 24, 2017 at 02:34 PM

314

avatar image

Hello,

I have a remote table with a field DateCreated and want to filter and transfer the records from the last 10 days only. When I use constant date in the Flowgraph Filter transformation everything is OK, but I must calculate the period dynamically from current date. I tried using ADD_DAYS(CURRENT_DATE,-10) in FilterExpression, but it was not successfull - there was no where clause in the remote SQL and all records are returned.

Any help with a sample code will be apreciated.Thank you!

Ivaylo Ivanov

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

1 Answer

avatar image
Former Member Nov 30, 2017 at 11:05 AM
0

Interesting, This should work...

Are you using XSA or classic flowgraph?

In fact, what you can do more is to enable debug mode in DPAgent. In the framework.trc will clearly show you which statement has been pushed down to the source. For example when I input filter VBELN = '0000000001' the log will show

2017-11-30 10:22:44,660 [DEBUG] ABAPAdapter | TableLoaderABAPTable$1.handleRequest - Pushed down whereClause = 2017-11-30 10:22:44,660 [DEBUG] ABAPAdapter | TableLoaderABAPTable$1.handleRequest - options line = ( 2017-11-30 10:22:44,660 [DEBUG] ABAPAdapter | TableLoaderABAPTable$1.handleRequest - options line = VBELN 2017-11-30 10:22:44,660 [DEBUG] ABAPAdapter | TableLoaderABAPTable$1.handleRequest - options line = = 2017-11-30 10:22:44,660 [DEBUG] ABAPAdapter | TableLoaderABAPTable$1.handleRequest - options line = '0000000001' 2017-11-30 10:22:44,660 [DEBUG] ABAPAdapter | TableLoaderABAPTable$1.handleRequest - options line = ) 2017-11-30 10:22:44,691 [TRACE] ABAPAdapter | TableLoaderABAPTable$2.handleRequest - Data callback with num or rows = 1 2017-11-30 10:22:44,691 [DEBUG] ABAPAdapter | TableLoaderABAPTable$2.handleRequest - Responded with end. 2017-11-30 10:22:44,691 [DEBUG] DPFramework | AsyncHandler.processStreamingExecuteStatement - AH(4440): Stream Processing Complete: No Error, Not Closed

I tried the same statement that you use on the filter node

"ERDAT" < ADD_DAYS(CURRENT_DATE,-10)

However, It seems this statement cannot pushed down to the source. I'm opening incident to ask SAP.

If I know the answer will let you know :)

Show 5 Share
10 |10000 characters needed characters left characters exceeded

Hi Attaphon,

My flowgraph is classic.

Thank you for your engagement!Please, let me know what SAP will answer.

BR,

Ivaylo

0
Former Member

Ivaylo,

It's become more clear to me that dynamic filter statement cant be pushed down to the source.

You can select * from virtual table where "abcd" < ADD_DAYS(...), right click and explain plan.

Compare with select * from virtual table where "abcd" = '....'

This seems to be current limitation of SDI.

You may try create stored procedure and pass filter parameter as an variable to the flowgraph. This is to ensure that the filter is pushed down.

Anyway I haven't get feedback from SAP :(

0

Hi Attaphon,

After investigation of the explain plan's in the two cases,I complete agree with you.

Could you help me with sample code for a stored procedure, especially how to pass the calculated value to flowgraph's variable?

Thank you for your effort!

Ivaylo

0
Former Member

Sorry for late reply.

Answer from SAP is to create stored procedure with the code like this

----------------------------------------------------------------------

Declare checkDate nvarchar(10):= to_nvarchar (ADD_DAYS(CURRENT_DATE , -1), 'YYYYMMDD');

Then use it in the query within the stored procedure:

querySTR := ' ********** where ERDAT > ''' || :FROMDAY || ''' ' ;

exec querySTR ;

0

Thank you!

I'll try this.

0