我有这样的记录：

INSERT INTO MY_TABLE(JOB_DEFINITION) VALUES ('JobDefinition://company/apps/team/crmCommon/workMgmt/assignmentMgr/ObjectShareBatchAssignRequest');
INSERT INTO MY_TABLE(JOB_DEFINITION) VALUES ('JobDefinition://company/apps/team/financials/commonModules/metrics/GenerateFinMetrics');
INSERT INTO MY_TABLE(JOB_DEFINITION) VALUES ('JobDefinition://company/apps/team/financials/subledgerAccounting/shared/XLAOTEC');
INSERT INTO MY_TABLE(JOB_DEFINITION) VALUES ('JobDefinition://company/apps/team/fnd/applcore/FndIDCSSyncNotifyServiceJob');
INSERT INTO MY_TABLE(JOB_DEFINITION) VALUES ('JobDefinition://company/apps/team/fnd/applcore/FndOSCSAttachmentIngestJob');
INSERT INTO MY_TABLE(JOB_DEFINITION) VALUES ('JobDefinition://company.apps.team.biccc/BICloudConnectorJobDefinition');

我需要在这些值上运行一个正则表达式，然后在正则表达式上进行查找/Map，正是查找产生了令人困惑的结果。
因此，使用regex查询：

select JOB_DEFINITION, 
regexp_replace(JOB_DEFINITION, 'Job.*://company[/\.][a-z]+[/\.]team[/\.]([a-zA-Z_]+)/?(.*)$', '\1') as ep
from MY_TABLE;

产生：
| 作业_定义|欧洲药典|
| - ------|- ------|
| 作业定义：//公司/应用程序/团队/财务/公共模块/指标/生成财务指标|财务|
| 工作定义：//公司/应用程序/团队/财务/子分类账会计/共享/XLAOTEC|财务|
| 作业定义：//公司/应用程序/团队/fnd/应用程序核心/FndIDCSSync通知服务作业|芬得|
| 作业定义：//公司/应用程序/团队/fnd/应用程序核心/FndOSC附件安装作业|芬得|
| 作业定义：//公司.应用程序.团队.biccc/BICloudConnector作业定义|比奇卡|
| 作业定义：//公司/应用程序/团队/cdm/数据管理/批处理充实过程团队/计划帐户充实作业|清洁发展机制|
到目前为止，一切顺利。
现在问题出现了，我想使用ep并执行静态查找：

select met.job_definition, regex.ep,lookup.family
from (select NVL(regexp_replace(JOB_DEFINITION, 'Job.*://company[/\.][a-z]+[/\.]team[/\.]([a-zA-Z_]+)/?(.*)$', '\1'),'TBD') as ep
      from MY_TABLE) regex,
     (select 'cdm' as ExtractedProduct, 'CRM' as Family
      from dual
      union all
      select 'crm' as ExtractedProduct, 'CRM' as Family
      from dual
      union all
      select 'crmCommon' as ExtractedProduct, 'CRM' as Family
      from dual
      union all
      select 'financials' as ExtractedProduct, 'ERP' as Family
      from dual
      union all
      select 'custom' as ExtractedProduct, 'Custom' as Family
      from dual
      union all
      select 'fnd' as ExtractedProduct, 'ApplCore' as Family
      from dual
      union all
      select 'hcm' as ExtractedProduct, 'HCM' as Family
      from dual
      union all
      select 'incentiveCompensation' as ExtractedProduct, 'ERP' as Family
      from dual
      union all
      select 'marketing' as ExtractedProduct, 'CRM' as Family
      from dual
      union all
      select 'prc' as ExtractedProduct, 'SCM' as Family
      from dual
      union all
      select 'projects' as ExtractedProduct, 'ERP' as Family
      from dual
      union all
      select 'sales' as ExtractedProduct, 'CRM' as Family
      from dual
      union all
      select 'scm' as ExtractedProduct, 'SCM' as Family
      from dual
      union all
      select 'search' as ExtractedProduct, 'ApplCore' as Family
      from dual
      union all
      select 'psc' as ExtractedProduct, 'ERP' as Family
      from dual
      union all
      select 'contracts' as ExtractedProduct, 'CRM' as Family
      from dual
      union all
      select 'extn' as ExtractedProduct, 'Custom' as Family
      from dual) lookup,
     MY_TABLE met
where regex.ep = lookup.ExtractedProduct

其产生：
| 作业_定义|欧洲药典|家庭|
| - ------|- ------|- ------|
| 作业定义：//公司/应用程序/团队/hcm/批处理/核心/归档写入作业|财务|ERP系统|
这显然是不对的这应该是hcm
我是不是做错了这个连接？
我基本上有3组数据：

• 给定数据的正则表达式
• 原始数据本身
• 所述查找