lead function equivalent in bods

[former_member205400]

All,

This snippet of code is part of the HANA mini-checks and we want to use it in a bods df, however we are against copying sql into a bods sql window and launching it, so we would like to use the bods sources, transforms, etc. so we get good advantage of the push-down functionality.

Additionally we have told our developers not to use the sql window either so I'd like not to break any rules we created.

Here is the sql snip:

SELECT   'COLUMN' AREA, 

SERVER_TIMESTAMP, 

SCHEMA_NAME, 

TABLE_NAME DETAIL,  

PART_ID,   HOST, 

PORT, 

RECORD_COUNT NUM_ROWS, 

MEMORY_SIZE_IN_TOTAL ALLOC_BYTE, 

MEMORY_SIZE_IN_TOTAL USED_BYTE, 

MEMORY_SIZE_IN_MAIN MAIN_BYTE, 

MEMORY_SIZE_IN_DELTA DELTA_BYTE, 

GREATEST(0, MERGE_COUNT - LEAD(MERGE_COUNT, 1) OVER     (PARTITION BY SCHEMA_NAME, TABLE_NAME, PART_ID, HOST ORDER BY SERVER_TIMESTAMP DESC)) MERGES

FROM   _SYS_STATISTICS.HOST_COLUMN_TABLES_PART_SIZE CT

So that lead function I need to remediate it in BODS.

Anyone familiar with how it should be remediated?

Thanks for your review.

Mike

[mageshwaran_subramanian · Accepted solution]

No.Data Services has previous_row_value() function but no next_row_value()

[former_member211387 · Accepted solution]

Why not sort descending and use the previous_row_value() function?