Pushing forward a value to following rows.

[JOHN_]

I have a dataset that looks like this:

A B 

0 1
0 1
0 1
1 1
1 1
1 1
0 1
0 1
0 1
0 1
0 1
0 1
0 0
0 0
0 0
1 0
1 0

I want to create a variable that equals 1 when A and B are 1 or B equals 1 and A has equaled 1 since B has equaled 1. Essentially we need to remember if A has equaled 1 in the past.

A B C

0 1 0
0 1 0
0 1 0
1 1 1
1 1 1
1 1 1
0 1 1
0 1 1 
0 1 1
0 1 1
0 1 1
0 1 1
0 0 0
0 0 0
0 0 0
1 0 0
1 0 0 

[Fred]

Assuming you have at least one other column (call it seq_nbr) that gives you the necessary row ordering, an OLAP function with RESET WHEN can determine if A is / has been 1:

SELECT a, b, 
CASE WHEN b=1 AND (MAX(a) OVER (ORDER BY seq_nbr RESET WHEN b=0 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW))=1 THEN 1 ELSE 0 END AS c
FROM theTable
ORDER BY seq_nbr;

[Waldar]

Hi JOHN_,

Slightly different from Fred's answer but with the same logic.

Data

create multiset volatile table mvt_data, no log
( mvt_PI    byteint
, colA      byteint
, colB      byteint
, ordr      byteint
)
primary index (mvt_PI)
on commit preserve rows;

insert into mvt_data values (0, 0, 1,  1);
insert into mvt_data values (0, 0, 1,  2);
insert into mvt_data values (0, 0, 1,  3);
insert into mvt_data values (0, 1, 1,  4);
insert into mvt_data values (0, 1, 1,  5);
insert into mvt_data values (0, 1, 1,  6);
insert into mvt_data values (0, 0, 1,  7);
insert into mvt_data values (0, 0, 1,  8);
insert into mvt_data values (0, 0, 1,  9);
insert into mvt_data values (0, 0, 1, 10);
insert into mvt_data values (0, 0, 1, 11);
insert into mvt_data values (0, 0, 1, 12);
insert into mvt_data values (0, 0, 0, 13);
insert into mvt_data values (0, 0, 0, 14);
insert into mvt_data values (0, 0, 0, 15);
insert into mvt_data values (0, 1, 0, 16);
insert into mvt_data values (0, 1, 0, 17);

Query

  select ordr
       , colA
       , colB
       , max(colA * colB) over(order by ordr asc
                               reset when colB = 0
                               rows unbounded preceding) as colC
    from mvt_data
   where mvt_PI = 0
order by ordr asc;

ordr  colA  colB  colC
----  ----  ----  ----
   1     0     1     0
   2     0     1     0
   3     0     1     0
   4     1     1     1
   5     1     1     1
   6     1     1     1
   7     0     1     1
   8     0     1     1
   9     0     1     1
  10     0     1     1
  11     0     1     1
  12     0     1     1
  13     0     0     0
  14     0     0     0
  15     0     0     0
  16     1     0     0
  17     1     0     0

[JOHN_]

Hi Fred,

That's great thanks!

I'm having a problem when using this derived variable C in a where statement. 

WHERE c=1

I get the error:

SELECT Failed. [5479] Ordered Analytical Functions not allowed in WHERE Clause.

Any work arounds to this?

Thanks, John.

[Waldar]

You can filter on analytical functions using the QUALIFY clause.

[Fred]

Or use a derived table expression:

SELECT * FROM ( query without the WHERE clause ) AS Q WHERE c=1

[dnoeth]

For the example data there's no need to use RESET WHEN, it depends on the expected result when the next row has a B 1 again :-)