如何修改SQL查询关联alpha_country表转换商户国家编码？

原SQL查询返回的merchant_country是3位数字编码，需要关联alpha_country表，将其替换为对应的2位字母国家编码。

原查询语句 #

SELECT oc.card_number,
       opt.oper_date AS "TRANSACTION_DATE",
       op.account_number,
       CASE vf.sttl_currency
         WHEN '840' THEN
          'USD'
       END AS "SOURCE_AMOUNT_CURRENCY",
       CASE opt.oper_type
         WHEN 'OPTP0000' THEN
          -vf.sttl_amount
         ELSE
          vf.sttl_amount
       END AS "SOURCE_AMOUNT",
       op.auth_code,
       opt.mcc,
       opt.terminal_number AS "TERMINAL_ID",
       vf.pos_terminal_cap,
       vf.pos_entry_mode,
       opt.oper_type,
       opt.part_key AS "CAPTURE_DATE",
       opt.oper_amount AS "TRANSACTION_AMOUNT",
       opt.oper_currency AS "TRANSACTION_CURRENCY",
       opt.merchant_name,
       opt.merchant_country,
       opt.clearing_sequence_num,
       opt.clearing_sequence_count,
       CASE opt.oper_type
         WHEN 'OPTP0000' THEN
          '05-PURCHASE'
         WHEN 'OPTP0020' THEN
          '06-CREDIT VOUCHER'
         ELSE
          'other'
       END AS Transaction_Type
  FROM opr_operation   opt,
       opr_participant op,
       opr_card        oc,
       vis_fin_message vf
 WHERE op.oper_id = opt.id
   AND opt.id = vf.id
   AND opt.clearing_sequence_num > 1
   AND opt.part_key >= '28-MAR-24'
   AND op.card_id = vf.card_id
   AND op.participant_type = 'PRTYISS'
   AND oc.oper_id = opt.id
   AND opt.msg_type IN ('MSGTPAMC', 'MSGTPACC');

修改后的查询语句 #

推荐使用显式JOIN语法（比旧的逗号连接更清晰），同时关联alpha_country表：

SELECT oc.card_number,
       opt.oper_date AS "TRANSACTION_DATE",
       op.account_number,
       CASE vf.sttl_currency
         WHEN '840' THEN
          'USD'
       END AS "SOURCE_AMOUNT_CURRENCY",
       CASE opt.oper_type
         WHEN 'OPTP0000' THEN
          -vf.sttl_amount
         ELSE
          vf.sttl_amount
       END AS "SOURCE_AMOUNT",
       op.auth_code,
       opt.mcc,
       opt.terminal_number AS "TERMINAL_ID",
       vf.pos_terminal_cap,
       vf.pos_entry_mode,
       opt.oper_type,
       opt.part_key AS "CAPTURE_DATE",
       opt.oper_amount AS "TRANSACTION_AMOUNT",
       opt.oper_currency AS "TRANSACTION_CURRENCY",
       opt.merchant_name,
       -- 替换为2位字母编码，保留原字段别名
       ac.ALPHA_COUNTRY_CODE AS "MERCHANT_COUNTRY",
       opt.clearing_sequence_num,
       opt.clearing_sequence_count,
       CASE opt.oper_type
         WHEN 'OPTP0000' THEN
          '05-PURCHASE'
         WHEN 'OPTP0020' THEN
          '06-CREDIT VOUCHER'
         ELSE
          'other'
       END AS Transaction_Type
FROM opr_operation opt
JOIN opr_participant op ON op.oper_id = opt.id
JOIN opr_card oc ON oc.oper_id = opt.id
JOIN vis_fin_message vf ON opt.id = vf.id
-- 关联alpha_country表，匹配3位数字编码
JOIN alpha_country ac ON opt.merchant_country = ac.CODE
WHERE opt.clearing_sequence_num > 1
  AND opt.part_key >= '28-MAR-24'
  AND op.card_id = vf.card_id
  AND op.participant_type = 'PRTYISS'
  AND opt.msg_type IN ('MSGTPAMC', 'MSGTPACC');

可选调整：保留无匹配的记录 #

如果希望即使alpha_country表中没有对应编码，也保留原交易记录（此时MERCHANT_COUNTRY会显示为NULL），可以把JOIN alpha_country ac改成LEFT JOIN alpha_country ac。

内容的提问来源于stack exchange，提问作者headspace