Skip to Content

HANA SQL to obtain Ship To for billing document line items?

I'm trying to write HANA SQL to obtain the Ship To partner (from ECC.VBPA) for every billing document line item (ECC.VBRP). Tables VBRP and VBPA are joined on fields VBELN (billing document number) and POSNR (billing document line item). Ship To partners are indicated by VBPA.PARVW = 'SH'.

The challenge is that there's not a Ship To record in VBPA for every billing document line item record. In such cases, the Ship To is to be that of the first line item *preceding* the current line item that has a Ship To. (If there are no preceding line items that have a Ship To, the Ship To is to come from the header record; i.e., where POSNR = '000000'.)

I'm using the HANA SQL "lag" function to obtain the Ship To of the line item immediately preceding the current line item; that's working well. But when the immediately preceding line item doesn't have a Ship To, I can't figure out how to go back *multiple* preceding line items to the first line item that has a Ship To.

Any suggestions?

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

1 Answer

  • Dec 12, 2017 at 04:28 AM

    Hi Chris

    Please check if the following steps would help -

    1) Filter on the actual result set that has only values where SH is available

    2) Apply Lag function on the original result set, this would help us get values for few line items that might have preceding line item and then filter only those where SH is available

    3) UNION 1) & 2)

    4) Create a calculated column that gives the SH of POSNR = '000000' for all rows. When SH from 3) is null, fallback to this calculated column's value.

    Add comment
    10|10000 characters needed characters exceeded

    • Hi Chris

      Another approach that you can give a try is to use Arrays to handle the same in SQLScript.

      1) Take the ship to column values into an array by ordering on line item (including the null values)

      2) Iterate the array over its length and whenever there isn't a value in the preceding array element, replace it with previous value.

      3) Finally after getting the desired result, UNNEST the array into table variable and use it.

      Hope it helps :)