By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table table1 (call, c1, c2) as
select 'c001', 'a', 10 from dual
union all select 'c001', 'a', 11 from dual
union all select 'c001', 'b', 13 from dual
union all select 'c001', 'b', 25 from dual
union all select 'c001', 'c', 13 from dual
union all select 'c001', 'c', 13 from dual;
6 rows affected
create table table2 (call, description, symbol, limit) as
select 'c001', 'Gain', '>', 10 from dual
union all select 'c002', 'loss', '<', 1 from dual
union all select 'c003', 'safe', '=', 0 from dual;
3 rows affected
select t1a.call, t1a.c1, t1a.c2, t1b.c2,
t1a.c2 - t1b.c2 as diff, t2.symbol, t2.limit,
case t2.symbol
when '>' then
case when t1a.c2-t1b.c2 > t2.limit then 'PASS' else 'FAIL' end
when '<' then
case when t1a.c2-t1b.c2 < t2.limit then 'PASS' else 'FAIL' end
when '=' then
case when t1a.c2-t1b.c2 = t2.limit then 'PASS' else 'FAIL' end
end as status
from table1 t1a
join table1 t1b on t1b.call = t1a.call and t1b.c1 = t1a.c1 and t1b.c2 < t1a.c2
join table2 t2 on t2.call = t1a.call
order by t1a.call, t1b.c1;
| CALL | C1 | C2 | C2 | DIFF | SYMBOL | LIMIT | STATUS |
|---|---|---|---|---|---|---|---|
| c001 | a | 11 | 10 | 1 | > | 10 | FAIL |
| c001 | b | 25 | 13 | 12 | > | 10 | PASS |
select t1a.call, t1a.c1, t1a.c2, t1b.c2,
t1a.c2 - t1b.c2 as diff, t2.symbol, t2.limit,
xmlquery('/ROWSET/ROW/*/text()'
passing xmltype(
dbms_xmlgen.getxml('select case when ' || (t1a.c2-t1b.c2) || t2.symbol || t2.limit
|| ' then ''PASS'' else ''FAIL'' end from dual')
)
returning content) as status
from table1 t1a
join table1 t1b on t1b.call = t1a.call and t1b.c1 = t1a.c1 and t1b.c2 < t1a.c2
join table2 t2 on t2.call = t1a.call
order by t1a.call, t1b.c1;
| CALL | C1 | C2 | C2 | DIFF | SYMBOL | LIMIT | STATUS |
|---|---|---|---|---|---|---|---|
| c001 | a | 11 | 10 | 1 | > | 10 | FAIL |
| c001 | b | 25 | 13 | 12 | > | 10 | PASS |
select t1a.call, t1a.c1, t1a.c2, t1b.c2,
t1a.c2 - t1b.c2 as diff, t2.symbol, t2.limit,
xmlquery('/ROWSET/ROW/*/text()'
passing xmltype(
dbms_xmlgen.getxml('select case when ' || (t1a.c2-t1b.c2) || t2.symbol || t2.limit
|| ' then ''PASS'' else ''FAIL'' end from dual')
)
returning content) as status
from table1 t1a
join table1 t1b on t1b.call = t1a.call
and t1b.c1 = t1a.c1
and t1b.c2 <= t1a.c2
and (t1b.c2 < t1a.c2 or t1b.rowid < t1a.rowid)
join table2 t2 on t2.call = t1a.call
order by t1a.call, t1b.c1;
| CALL | C1 | C2 | C2 | DIFF | SYMBOL | LIMIT | STATUS |
|---|---|---|---|---|---|---|---|
| c001 | a | 11 | 10 | 1 | > | 10 | FAIL |
| c001 | b | 25 | 13 | 12 | > | 10 | PASS |
| c001 | c | 13 | 13 | 0 | > | 10 | FAIL |