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

[cgwaters]

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?

[former_member208449]

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.

[cgwaters]

Thanks for the response! Your logic is intriguing ... but it doesn't seem to address the scenario where neither the current row nor the immediately preceding row has a Ship To; i.e., the logic doesn't go back *multiple* rows to the first row that has a Ship To. See line items 000020, 000030, and 000040 in the following example.

Line Item    Ship To
000010       1234567890
000020       <blank>
000030       <blank>
000040       <blank>
000050       0987654321
000060       <blank>
000070       <blank>
000080       6789012345
000090       <blank>
000100       <blank>

For line item 000020, the preceding row (line item 000010) has a Ship To; but for line items 000030 and 000040, the preceding row (line items 000020 and 000030, respectively) doesn't have a Ship To. To obtain the Ship To for both of these rows, the SQL needs to go back to line item 000010.

The same scenario applies to line items 000070 and 000100.

Appreciate any suggestions as to how to implement.

[former_member208449]

Hi Chris

Based on the requirement you have mentioned in the 2nd para of your question, I suggested a mechanism to get the preceding line item's SH when available and when it isn't you have to fallback to SH corresponding to POSNR = '000000'.

[former_member208449]

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 🙂