背景为动态维度增量同步,判读是否存在增量的SQL为:
SELECT A.L_SERIAL_ID, A.VC_TRADE_NAME
FROM TM_HS_TTRADETYPES A
WHERE A.C_TRADE_TYPE = '0'
AND A.L_SERIAL_ID
NOT
IN
(SELECT TO_NUMBER(REGEXP_REPLACE(C.C_SOURCE_CODE ,'[^0-9]',''))
FROM TR_DYNAMIC_BOUND C
WHERE C.C_SOURCE_TYPE = '2'
)
执行之后发现无数据,在TR_DYNAMIC_BOUND 删除一条数据后,再执行这个条SQL,还是为空,没有查询出结果
根据排查发现第二句SQL的表中结果存在null值,就是TO_NUMBER(REGEXP_REPLACE(C.C_SOURCE_CODE ,'[^0-9]',''))的列存在null
导致使用条件not in时,会查询不到结果
解决办法:
SQL中将TO_NUMBER(REGEXP_REPLACE(C.C_SOURCE_CODE ,'[^0-9]',''))
进行空值处理,使用nvl, NVL(TO_NUMBER(REGEXP_REPLACE(C.C_SOURCE_CODE ,'[^0-9]','')),0)
当TO_NUMBER(REGEXP_REPLACE(C.C_SOURCE_CODE ,'[^0-9]',''))为空时,就给他0
SQL如下:
SELECT A.L_SERIAL_ID, A.VC_TRADE_NAME
FROM TM_HS_TTRADETYPES A
WHERE A.C_TRADE_TYPE = '0'
AND a.l_serial_id = '500'
AND A.L_SERIAL_ID
NOT
IN
(SELECT NVL(TO_NUMBER(REGEXP_REPLACE(C.C_SOURCE_CODE ,'[^0-9]','')),0)
FROM TR_DYNAMIC_BOUND C
WHERE C.C_SOURCE_TYPE = '2'
--AND c.c_source_code = '500'
)
就能查询出结果了。