未应用SQL字符串排除条件

Question

最近我一直在尝试优化一个代码，该代码用于从Oracle数据库中包含的大量数据中排除某些字符串。然而，我遇到了条件问题，因为我试图排除的字符串仍然存在。代码示例如下：

WITH
DUNS AS (
select '%PERSON' as DUNS from DUAL UNION ALL
select 'PH0%' as DUNS from DUAL UNION ALL
select 'PH1%' as DUNS from DUAL UNION ALL
select 'PH2%' as DUNS from DUAL UNION ALL
select 'PH3%' as DUNS from DUAL UNION ALL
select 'PH4%' as DUNS from DUAL UNION ALL
select 'PH5%' as DUNS from DUAL UNION ALL
select 'PH6%' as DUNS from DUAL UNION ALL
select 'PH7%' as DUNS from DUAL UNION ALL
select 'PH8%' as DUNS from DUAL UNION ALL
select 'PH9%' as DUNS from DUAL UNION ALL
select 'PH-PERSON' as DUNS from DUAL UNION ALL
select 'TRIALACCT' as DUNS from DUAL
),
ORGTYPE AS(
select 'Do Not Assign' as ORGTYPE from DUAL UNION ALL
select 'HGBU_Store_DIM' as ORGTYPE from DUAL UNION ALL
select 'HGBU%' as ORGTYPE from DUAL
),
CDQ AS(
select 
REGISTRY_ID, 
NVL(DUNS,0) DUNS, 
NVL(ORG_TYPE,0) ORG_TYPE, 
NVL(TECH_ORG_TYPE,0) TECH_ORG_TYPE, 
NVL(APPS_ORG_TYPE,0) APPS_ORG_TYPE
from gtm_cdq a
where not exists (select 1 from gtm_clean_accounts b where a.REGISTRY_ID=b.crm_registry_id)
)
select * 
from CDQ g, DUNS h, ORGTYPE i
where g.DUNS not like h.DUNS
AND (g.ORG_TYPE not like i.ORGTYPE AND g.TECH_ORG_TYPE not like i.ORGTYPE AND g.APPS_ORG_TYPE not like i.ORGTYPE)

在这段代码成功运行后，我查看结果表，看到的是：

REGISTRY_ID DUNS    ORG_TYPE    TECH_ORG_TYPE   APPS_ORG_TYPE   DUNS    ORGTYPE
11803270    PHtola  HGBU_ge     HGBU_ge           HGBU_ge     %PERSON   Do Not Assign
11803270    PHtola  HGBU_ge     HGBU_ge           HGBU_ge       PH0%    Do Not Assign
11803270    PHtola  HGBU_ge     HGBU_ge           HGBU_ge       PH1%    Do Not Assign
11803270    PHtola  HGBU_ge     HGBU_ge           HGBU_ge       PH2%    Do Not Assign
11803270    PHtola  HGBU_ge     HGBU_ge           HGBU_ge       PH3%    Do Not Assign
11803270    PHtola  HGBU_ge     HGBU_ge           HGBU_ge       PH4%    Do Not Assign
11803270    PHtola  HGBU_ge     HGBU_ge           HGBU_ge       PH5%    Do Not Assign

我有什么东西不见了吗？我认为这一切都来自于我指定Org_Type列不包含HGBU类型字符串的最后一行(条件(。不知怎么的，它根本没有得到处理。有什么想法吗？

真的很感激！

Answer 1

WHERE子句中的逻辑没有按照您的意愿或期望执行。这里有一个更简单的例子：

WITH
DUNS AS (
select '%A' as DUNS from DUAL UNION ALL
select 'B%' as DUNS from DUAL),
CDQ AS(
select 1 as registry_id, 'AA' as DUNS, 'a' as org_type,
'a' as tech_org_type, 'a' as apps_org_type
from dual
)
select g.duns, h.duns
from cdq g, duns h
where g.duns not like h.duns;

输出：

g.duns h.duns
AA     B%

带有where子句的联接将返回(笛卡尔(联接中与where子句不匹配的所有行。在您的示例中，如果您尝试去掉where子句，您会看到它只添加了几行——带有i.DUNS = 'HGBU%'的行

您需要一个NOT EXISTS而不是一个join。

WITH
DUNS AS (
select '%PERSON' as DUNS from DUAL UNION ALL
select 'PH0%' as DUNS from DUAL UNION ALL
select 'PH1%' as DUNS from DUAL UNION ALL
select 'PH2%' as DUNS from DUAL UNION ALL
select 'PH3%' as DUNS from DUAL UNION ALL
select 'PH4%' as DUNS from DUAL UNION ALL
select 'PH5%' as DUNS from DUAL UNION ALL
select 'PH6%' as DUNS from DUAL UNION ALL
select 'PH7%' as DUNS from DUAL UNION ALL
select 'PH8%' as DUNS from DUAL UNION ALL
select 'PH9%' as DUNS from DUAL UNION ALL
select 'PH-PERSON' as DUNS from DUAL UNION ALL
select 'TRIALACCT' as DUNS from DUAL
),
ORGTYPE AS(
select 'Do Not Assign' as ORGTYPE from DUAL UNION ALL
select 'HGBU_Store_DIM' as ORGTYPE from DUAL UNION ALL
select 'HGBU%' as ORGTYPE from DUAL
),
CDQ AS( -- I replaced the CDQ CTE with some dummy data so the query would run
select 11803270 as registry_id, 'PHtola' as DUNS, 'HGBU_ge' as org_type,
'HGBU_ge' as tech_org_type, 'HGBU_ge' as apps_org_type
from dual
)
select * 
from CDQ g
where not exists (select 1 from DUNS h where g.DUNS like h.DUNS)
and not exists (select 1 from ORGTYPE i where g.ORG_TYPE like i.ORGTYPE)
and not exists (select 1 from ORGTYPE i where g.TECH_ORG_TYPE like i.ORGTYPE)
and not exists (select 1 from ORGTYPE i where g.APPS_ORG_TYPE like i.ORGTYPE)
;