使用AND而不是OR：

WHERE C.SP_AGTNMBR1 IS NOT NULL
AND   C.SP_AGTNMBR1 != '00000'
AND   C.SP_AGTNMBR2 IS NOT NULL
AND   C.SP_AGTNMBR2 != '00000'
AND   C.SP_AGTNMBR3 IS NOT NULL
AND   C.SP_AGTNMBR3 != '00000'

由于!=对于NULL值不为真，因此可以简化为：

WHERE C.SP_AGTNMBR1 != '00000'
AND   C.SP_AGTNMBR2 != '00000'
AND   C.SP_AGTNMBR3 != '00000'

如果你想使用OR，那么测试相等性并否定OR的艾德结果：

WHERE NOT (C.SP_AGTNMBR1 IS NULL OR C.SP_AGTNMBR1 = '00000') 
AND   NOT (C.SP_AGTNMBR2 IS NULL OR C.SP_AGTNMBR2 = '00000') 
AND   NOT (C.SP_AGTNMBR3 IS NULL OR C.SP_AGTNMBR3 = '00000')

但是，如果要将该过滤器作为OUTER JOIN的一部分应用于外部连接表，则该过滤器应位于ON子句中（而不是WHERE子句中）。
如果你把过滤器放在WHERE子句中，那么你就是在隐式地把OUTER JOIN转换成INNER JOIN。
所以要么：

SELECT TRIM(UPPER(M.SC_CNT_PREF) )|| TRIM(UPPER(M.SC_CNT_NO) )|| TRIM(UPPER(M.SC_CNT_SUF) ) AS POLICY_NUMBER,
       'CL000' || C.SP_AGTNMBR1 AS AGENT_NUMBER_1,
       TO_NUMBER(C.SP_AGTPCNT1) AS AGNT_PCT_RT_1,
       'CL000' || C.SP_AGTNMBR2 AS AGENT_NUMBER_2,
       TO_NUMBER(C.SP_AGTPCNT2) AS AGNT_PCT_RT_2,
       'CL000' || C.SP_AGTNMBR3 AS AGENT_NUMBER_3,
       TO_NUMBER(C.SP_AGTPCNT3) AS AGNT_PCT_RT_3,
       NULL AS SITUATION
FROM   EODS_STG.STG1_EODS_SCIS_MASTER M
       LEFT OUTER JOIN EODS_STG.STG1_EODS_SCIS_SPIA_CONTRACT C
       ON (
             M.SC_CNT_PREF = C.SP_CNTRPREF
         AND M.SC_CNT_NO = C.SP_CNTRNMBR
         AND M.SC_CNT_SUF = C.SP_CNTRSUFF
         AND C.SP_AGTNMBR1 != '00000'
         AND C.SP_AGTNMBR2 != '00000'
         AND C.SP_AGTNMBR3 != '00000'
       );

或：

SELECT TRIM(UPPER(M.SC_CNT_PREF) )|| TRIM(UPPER(M.SC_CNT_NO) )|| TRIM(UPPER(M.SC_CNT_SUF) ) AS POLICY_NUMBER,
       'CL000' || C.SP_AGTNMBR1 AS AGENT_NUMBER_1,
       TO_NUMBER(C.SP_AGTPCNT1) AS AGNT_PCT_RT_1,
       'CL000' || C.SP_AGTNMBR2 AS AGENT_NUMBER_2,
       TO_NUMBER(C.SP_AGTPCNT2) AS AGNT_PCT_RT_2,
       'CL000' || C.SP_AGTNMBR3 AS AGENT_NUMBER_3,
       TO_NUMBER(C.SP_AGTPCNT3) AS AGNT_PCT_RT_3,
       NULL AS SITUATION
FROM   EODS_STG.STG1_EODS_SCIS_MASTER M
       INNER JOIN EODS_STG.STG1_EODS_SCIS_SPIA_CONTRACT C
       ON (
             M.SC_CNT_PREF = C.SP_CNTRPREF
         AND M.SC_CNT_NO = C.SP_CNTRNMBR
         AND M.SC_CNT_SUF = C.SP_CNTRSUFF
       )
WHERE  C.SP_AGTNMBR1 != '00000'
AND    C.SP_AGTNMBR2 != '00000'
AND    C.SP_AGTNMBR3 != '00000';