映射表设计与主表关联查询的实现咨询

Hi there! Let's walk through your mapping table and join requirement clearly. I see you've already set up your MAP_TBL and STD_TBL, and you're aiming to pull the correct TCODE and RCODE based on whether FIELD3 in the main table falls into a specific set of values or should default to the "OTHER" entry. Let's fix this properly.

First, let's recap your setup (formatted for clarity) #

Mapping Table (MAP_TBL) Creation & Data Insertion

CREATE TABLE "MAP_TBL" (
    "DB_KEY" VARCHAR2(200),
    "COUNTRY" VARCHAR2(200),
    "FIELD3" VARCHAR2(200),
    "ICODE" VARCHAR2(200),
    "TCODE" VARCHAR2(200),
    "RCODE" VARCHAR2(200)
);

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');

COMMIT;

Main Table (STD_TBL) Creation & Data Insertion

CREATE TABLE "STD_TBL" (
    "DB_KEY" VARCHAR2(200),
    "SNO" VARCHAR2(200),
    "FIELD3" VARCHAR2(200),
    "COUNTRY" VARCHAR2(200),
    "ICODE" VARCHAR2(200)
);

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;

Your Goal & Improved Solution #

You want to:

• Match STD_TBL.FIELD3 to MAP_TBL.FIELD3 if it's in ('02','08','09','11')
• Fall back to the OTHER entry in MAP_TBL for any other FIELD3 value

The issue with your original CASE WHEN in the WHERE clause (using Oracle's old outer join + syntax) is that it can be hard to read and might lead to unexpected results. Instead, use ANSI standard LEFT JOIN with the CASE logic directly in the join condition for clarity and reliability:

SELECT
    s.DB_KEY,
    s.SNO,
    s.FIELD3 AS original_field3,
    s.COUNTRY,
    s.ICODE,
    m.TCODE,
    m.RCODE
FROM STD_TBL s
LEFT JOIN MAP_TBL m
    ON s.DB_KEY = m.DB_KEY
    AND s.COUNTRY = m.COUNTRY
    AND s.ICODE = m.ICODE
    AND m.FIELD3 = CASE
        WHEN s.FIELD3 IN ('02','08','09','11') THEN s.FIELD3
        ELSE 'OTHER'
    END;

What This Does #

• For each row in STD_TBL, it first checks if FIELD3 is in your target set. If yes, it joins to the matching MAP_TBL row with that FIELD3 value.
• If FIELD3 is not in the set, it joins to the MAP_TBL row where FIELD3 = 'OTHER'.
• The join also ensures we match on DB_KEY, COUNTRY, and ICODE to get the correct context-specific mappings.

Expected Results #

Running this query against your sample data will return:

Note on Padding (If Needed) #

If you have FIELD3 values that might be single-digit (like '2' instead of '02'), you can adjust the CASE statement to pad with leading zeros:

AND m.FIELD3 = CASE
    WHEN LPAD(s.FIELD3, 2, '0') IN ('02','08','09','11') THEN LPAD(s.FIELD3, 2, '0')
    ELSE 'OTHER'
END;

备注：内容来源于stack exchange，提问作者Confused