Calculated column value based on rows above SQL

Question

I have this stupid table in our program db. Program is not ours so there is no way to change table contents. The table stores SQL like expressions in unreadable codes. I am translating those values back to SQL expressions to run these expressions. So far, the translating part goes very well. Result so far is a table like the following:

+--------+--------+---------+----------------+-----------------+
| CODE   | SEQNUM | LOGICAL | EXPRESSION     | EXPRESSIONVALUE |
+--------+--------+---------+----------------+-----------------+
| A23000 | 1      | NULL    | OTHERCODE LIKE | 'A522%'         |
+--------+--------+---------+----------------+-----------------+
| A23000 | 2      | OR      | OTHERCODE =    | 'A62342'        |
+--------+--------+---------+----------------+-----------------+
| A23000 | 3      | OR      | OTHERCODE =    | 'A62343'        |
+--------+--------+---------+----------------+-----------------+
| B43000 | 1      | NULL    | OTHERCODE IN   | 'B34324'        |
+--------+--------+---------+----------------+-----------------+
| B43000 | 2      | NULL    | NULL           | 'B92338'        |
+--------+--------+---------+----------------+-----------------+
| B43000 | 3      | NULL    | NULL           | 'B92342'        |
+--------+--------+---------+----------------+-----------------+
| B43000 | 4      | NULL    | NULL           | 'B02349'        |
+--------+--------+---------+----------------+-----------------+
| B43000 | 5      | OR      | OTHERCODE =    | 'B32443'        |
+--------+--------+---------+----------------+-----------------+

Or order for me to have valid SQL statements, I need to have commas behind the rows where there is an 'in-expression'. I my opinion this needs to be based on earlier rows. I need to have a table like:

+--------+--------+---------+----------------+-----------------+------------+
| CODE   | SEQNUM | LOGICAL | EXPRESSION     | EXPRESSIONVALUE | CALCULETED |
+--------+--------+---------+----------------+-----------------+------------+
| A23000 | 1      | NULL    | OTHERCODE LIKE | 'A522%'         | NULL       |
+--------+--------+---------+----------------+-----------------+------------+
| A23000 | 2      | OR      | OTHERCODE =    | 'A62342'        | NULL       |
+--------+--------+---------+----------------+-----------------+------------+
| A23000 | 3      | OR      | OTHERCODE =    | 'A62343'        | NULL       |
+--------+--------+---------+----------------+-----------------+------------+
| B43000 | 1      | NULL    | OTHERCODE IN   | 'B34324'        | ','        |
+--------+--------+---------+----------------+-----------------+------------+
| B43000 | 2      | NULL    | NULL           | 'B92338'        | ','        |
+--------+--------+---------+----------------+-----------------+------------+
| B43000 | 3      | NULL    | NULL           | 'B92342'        | ','        |
+--------+--------+---------+----------------+-----------------+------------+
| B43000 | 4      | NULL    | NULL           | 'B02349'        | NULL       |
+--------+--------+---------+----------------+-----------------+------------+
| B43000 | 5      | OR      | OTHERCODE =    | 'B32443'        | NULL       |
+--------+--------+---------+----------------+-----------------+------------+

Is this possible to do with SQL?

score 1 · Answer 1 · answered Dec 08 '19 at 12:17

Would this help?

SQL> with test (code, seqnum, logical, expression, value) as
  2    (select 'a23', 1, null, 'othercode like', 'a522%'  from dual union all
  3     select 'a23', 2, 'or', 'othercode ='   , 'a62342' from dual union all
  4     select 'a23', 3, 'or', 'othercode ='   , 'a62343' from dual union all
  5     --
  6     select 'b43', 1, null, 'othercode in'  , 'b34324' from dual union all
  7     select 'b43', 2, null, 'null'          , 'b92342' from dual union all
  8     select 'b43', 3, null, 'null'          , 'b02349' from dual union all
  9     select 'b43', 4, 'or', 'othercode ='   , 'b34243' from dual
 10    ),
 11  poc as
 12    -- SEQNUM where "IN" begins
 13   (select code, min(seqnum) minseq
 14    from test
 15    where expression = 'othercode in'
 16    group by code
 17    ),
 18  zav as
 19    -- SEQNUM where "IN" part ends
 20    (select t.code, max(t.seqnum) maxseq
 21     from test t join poc p on p.code = t.code
 22     where t.seqnum > p.minseq
 23       and t.expression <> 'null'
 24     group by t.code
 25    )
 26  select t.code, t.seqnum, t.logical, t.expression, t.value,
 27    case when t.seqnum >= p.minseq and t.seqnum < z.maxseq then ','
 28         else null
 29    end calc
 30  from test t left join poc p on p.code = t.code
 31              left join zav z on z.code = t.code
 32  order by code, seqnum;

COD     SEQNUM LO EXPRESSION     VALUE   C
--- ---------- -- -------------- ------- -
a23          1    othercode like a522%
a23          2 or othercode =    a62342
a23          3 or othercode =    a62343
b43          1    othercode in   b34324  ,
b43          2    null           b92342  ,
b43          3    null           b02349  ,
b43          4 or othercode =    b342443

7 rows selected.

SQL>

score 0 · Answer 2 · answered Dec 08 '19 at 12:58

You can do this logic with analytic functions:

select t.*,
       (case when coalesce(expression, lag(expression ignore nulls) over (partition by code order by seqnum)) like '% in' and
                  ((lead(logical) over (partition by code order by seqnum) is null and
                    lead(seqnum) over (partition by code order by seqnum) is not null
                   ) or
                   lead(seqnum) over (partition by code order by seqnum) is not null
                  )
             then ','
        end) as calculated
from test t;

The logic implemented identifies the rows associated with in logic -- that is the lag(ignore nulls).

All of these rows get a comma, except for the last one. This is tricky, because the last row might be the end of the expression (the last row seqnum for the code). Or, a new boolean expression might follow.

I have added more test cases to show that this works:

No expression after the in.
Only one value in the in list.
Another expression before the in.

Here is the db<>fiddle.

Calculated column value based on rows above SQL

2 Answers2