"DB2" maps the results based on the mapping table

First step: Create syntax




CREATE TABLE oliver_map (ID int,com_type varchar (+), Com_name varchar (+), sr_up decimal (18,2), Sr_lim decimal (18,2), RES VARCHAR (+); INSERT into Oliver_map (id,com_type,com_name,sr_up,sr_lim,res) VALUES (1, 'A1‘,‘Type a small company', 3000000,null, ' asINSERT into Oliver_map (id,com_type,com_name,sr_up,sr_lim,res) VALUES (2, 'A2‘,‘Type a small company', 3000000,null, ' asINSERT into Oliver_map (id,com_type,com_name,sr_up,sr_lim,res) VALUES (3, 'B1‘,‘Type B Large companies', 100000000,null, 'BBINSERT into Oliver_map (id,com_type,com_name,sr_up,sr_lim,res) VALUES (4, 'B2‘,‘Type B Large companies', 100000000,null, 'BBINSERT into Oliver_map (id,com_type,com_name,sr_up,sr_lim,res) VALUES (5,null,null,null,null, 'O‘);

Step Two: Create test data for testing

CREATE TABLE oliver_01 (ID int,com_type varchar (+), SR DECIMAL (18,2), YQFL VARCHAR); INSERT into oliver_01 VALUES (1, 'A1', 30000000, 'OINSERT into Oliver_01 VALUES (2, 'A2', 30000000, 'OINSERT into Oliver_01 VALUES (3, 'B1', 100000000, 'BBINSERT into Oliver_01 VALUES (4, 'B2', 100000000, 'BBINSERT into Oliver_01 VALUES (5, 'C', 100000000, 'OINSERT into Oliver_01 VALUES (6, 'A1', NULL, 'OINSERT into Oliver_01 VALUES (7, 'A2', NULL, 'OINSERT into oliver_01 VALUES (8, 'B1', NULL, 'OINSERT into Oliver_01 VALUES (9, 'B2', NULL, 'OINSERT into Oliver_01 VALUES (10, 'A1', 30000001, 'O‘);

Third Step: Query results

Select T.*, (select RES from Oliver_map awhere (A.com_type was null or A.com_type=t.com_type) and (a.sr_up is null or A.SR_UP >=T.SR) and (A.sr_lim is NULL OR a.sr_lim<t.sr) ORDER by a.id FETCH first ROW only) as Resfrom oliver_01 T

OLIVER_01 table Data

Mapping table Data

Query structure

This case applies to the presence of the mapping table and then results from the mapping table match!

"DB2" maps the results based on the mapping table