Save value in local variable HANA SQL Script

Question

I'm trying to take value from a non-empty row and overwrite it in the subsequent rows until another non-empty row appears and then write that in the subsequent rows. Coming from ABAP Background, I'm not sure how to accomplish this in HANA SQL Script. Here's a picture to show what the data looks like.

Source Data Image

Basically 'Doe, John' should be overwritten into all the empty rows until 'Doe, Jane' appears and then 'Doe, Jane' should be overwritten into empty rows until another name appears.

My idea is to store the non-empty row in a local variable, but I haven't had much success so far. Here's my code:

tempTab1 = SELECT

  CASE WHEN EMPLOYEE <> ''
    THEN lv_emp = EMPLOYEE
  ELSE EMPLOYEE
  END AS EMPLOYEE,
FROM :tempTab;

please provide executable DDL/DML code to reproduce your scenario - nobody wants to type in your screenshot data. — Lars Br., Sep 25 '20 at 05:23
The question is, is it possible to write the names in the empty rows in SQL Script? I am working on BW/4HANA and trying to accomplish this in a transformation between two InfoProviders. — melquiades, Sep 25 '20 at 10:24
How would the SQL Script code for this look like? Thanks in advance. — melquiades, Sep 29 '20 at 13:50

astentx · Answer 1 · 2020-09-27T00:15:17.400

In general, rows in dataset are unordered until you explicitly specify ORDER BY part of SQL. If you observe some order it may be a side-effect and can vary. So first of all you have to explicitly create a row number column (assume it's name is RECORD). Then you should go this way:

Select only rows with non-empty data in column.
Use LEAD(RECORD) over(order by RECORD) to identify the next non-empty record number.
Join your source dataset to dataset defined on step 3 on between condition for RECORD field.

with a as (
  select 1 as record, 'Val1' as field1 from dummy union
  select 2 as record, '' as field1 from dummy union
  select 3 as record, '' as field1 from dummy union
  select 4 as record, 'Val2' as field1 from dummy union
  select 5 as record, '' as field1 from dummy union
  select 6 as record, '' from dummy union
  select 7 as record, '' from dummy union
  select 8 as record, 'Val3' as field1 from dummy
)
, fill_base as (
  select field1, record, lead(record, 1, record) over(order by record asc) as next_record
  from a
  where field1 <> '' and field1 is not null
)
select
  a.record
  , case
      when a.field1 = '' or a.field1 is null
      then f.field1
      else a.field1
  end as field1
  , a.field1 as field1_original
from a
  left join fill_base as f
    on a.record > f.record
      and a.record < f.next_record

The performance in HANA may be bad in some cases since it process window functions very bad.

Here is another more elegant solution with two nested window functions than does not force you to write multiple selects for each column: How to make LAG() ignore NULLS in SQL Server?

score 0 · Answer 2 · answered Jan 21 '22 at 13:15

You can use window aggregate function LAST_VALUE to achieve the imputation of missing values.

Sample Data

CREATE TABLE sample (id integer, sort integer, value varchar(10));
INSERT INTO sample VALUES (4711, 1, 'Hello');
INSERT INTO sample VALUES (4712, 2, null);
INSERT INTO sample VALUES (4713, 3, null);
INSERT INTO sample VALUES (4714, 4, 'World');
INSERT INTO sample VALUES (4715, 5, null);
INSERT INTO sample VALUES (4716, 6, '!');

Generate a new column with imputed values

SELECT base.*, LAST_VALUE(fill.value ORDER BY fill.sort) AS value_imputed
FROM sample base
LEFT JOIN sample fill ON fill.sort <= base.sort AND fill.value IS NOT NULL
GROUP BY base.id, base.sort, base.value
ORDER BY base.id, base.sort

Result

Note that sort could be anything determining the order (e.g. a timestamp).

Save value in local variable HANA SQL Script

2 Answers2