By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
CREATE TABLE "MAP_TBL" ("DB_KEY" VARCHAR2(200) , "COUNTRY" VARCHAR2(200),"FIELD3" VARCHAR2(200) , "ICODE" VARCHAR2(200) , "TCODE" VARCHAR2(200) , "RCODE" VARCHAR2(200) );
create TABLE "STD_TBL" ("DB_KEY" VARCHAR2(200),"SNO" VARCHAR2(200), "FIELD3" VARCHAR2(200),"COUNTRY" VARCHAR2(200),"ICODE" VARCHAR2(200));
BEGIN
Insert into MAP_TBL (DB_KEY,COUNTRY,FIELD3,ICODE,TCODE,RCODE) values ('ABE','BE','02','1','BD','AT');
Insert into MAP_TBL (DB_KEY,COUNTRY,FIELD3,ICODE,TCODE,RCODE) values ('ABE','BE','08','1','BD','AT');
Insert into MAP_TBL (DB_KEY,COUNTRY,FIELD3,ICODE,TCODE,RCODE) values ('ABE','BE','09','1','BD','AT');
Insert into MAP_TBL (DB_KEY,COUNTRY,FIELD3,ICODE,TCODE,RCODE) values ('ABE','BE','11','1','BD','AT');
Insert into MAP_TBL (DB_KEY,COUNTRY,FIELD3,ICODE,TCODE,RCODE) values ('ABE','BE','OTHER','1','LD','AT');
Insert into MAP_TBL (DB_KEY,COUNTRY,FIELD3,ICODE,TCODE,RCODE) values ('ABE','BE','02','3','AD','VT');
Insert into MAP_TBL (DB_KEY,COUNTRY,FIELD3,ICODE,TCODE,RCODE) values ('ABE','BE','08','3','AD','VT');
Insert into MAP_TBL (DB_KEY,COUNTRY,FIELD3,ICODE,TCODE,RCODE) values ('ABE','BE','09','3','AD','VT');
Insert into MAP_TBL (DB_KEY,COUNTRY,FIELD3,ICODE,TCODE,RCODE) values ('ABE','BE','11','3','AD','VT');
Insert into MAP_TBL (DB_KEY,COUNTRY,FIELD3,ICODE,TCODE,RCODE) values ('ABE','BE','OTHER','3','FD','FT');
Insert into STD_TBL (DB_KEY,SNO,FIELD3,COUNTRY,ICODE) values ('ABE','123','02','BE','1');
Insert into STD_TBL (DB_KEY,SNO,FIELD3,COUNTRY,ICODE) values ('ABE','124','01','BE','1');
Insert into STD_TBL (DB_KEY,SNO,FIELD3,COUNTRY,ICODE) values ('ABE','125','02','BE','3');
Insert into STD_TBL (DB_KEY,SNO,FIELD3,COUNTRY,ICODE) values ('ABE','126','03','BE','3');
COMMIT;
END;
/
1 rows affected
SELECT s.*,
m.tcode,
m.rcode
FROM std_tbl s
LEFT OUTER JOIN map_tbl m
ON ( s.db_key = m.db_key
AND s.country = m.country
AND s.icode = m.icode
AND DECODE(s.field3, '02', '02',
'08', '08',
'11', '11',
'09', '09',
'OTHER') = m.field3
)
| DB_KEY | SNO | FIELD3 | COUNTRY | ICODE | TCODE | RCODE |
|---|---|---|---|---|---|---|
| ABE | 123 | 02 | BE | 1 | BD | AT |
| ABE | 124 | 01 | BE | 1 | LD | AT |
| ABE | 125 | 02 | BE | 3 | AD | VT |
| ABE | 126 | 03 | BE | 3 | FD | FT |
SELECT s.*,
m.tcode,
m.rcode
FROM std_tbl s
LEFT OUTER JOIN map_tbl m
ON ( s.db_key = m.db_key
AND s.country = m.country
AND s.icode = m.icode
AND CASE
WHEN s.field3 NOT IN ('02', '08', '11', '09')
THEN 'OTHER'
ELSE s.field3
END = m.field3
)
| DB_KEY | SNO | FIELD3 | COUNTRY | ICODE | TCODE | RCODE |
|---|---|---|---|---|---|---|
| ABE | 123 | 02 | BE | 1 | BD | AT |
| ABE | 124 | 01 | BE | 1 | LD | AT |
| ABE | 125 | 02 | BE | 3 | AD | VT |
| ABE | 126 | 03 | BE | 3 | FD | FT |