oracle 左外连接问题，该这么办

Question

SELECT COUNT(*)
          FROM [email protected]  a,t_code_segment b,
               [email protected] c
          WHERE substr(nvl(c."fFrom","DesAddr"),1,7)=b.fprefix
           AND a."ExtID"=c."fid"(+)
           AND a."fTaskID"=0
           AND (c."fTime">=to_date('2008-09-23','yyyy-mm-dd') and c."fTime"<to_date('2008-09-26','yyyy-mm-dd')） OR(c."fTime" IS NULL)这个语句有错误，a 和c 关联的时候，如果加上(c."fTime">=to_date('2008-09-23','yyyy-mm-dd') and c."fTime"<to_date('2008-09-26','yyyy-mm-dd')） 会把，a中有记录c中没有记录的这个情况给过滤，这么做才能不过滤这个情况

Answer 1

SELECT COUNT(*) 
          FROM t_code_segment b, [email protected]  a left join
              [email protected] c on a."ExtID"=c."fid"
          WHERE substr(nvl(c."fFrom","DesAddr"),1,7)=b.fprefix 
          AND a."fTaskID"=0 
          AND (c."fTime">=to_date('2008-09-23','yyyy-mm-dd') and c."fTime" <to_date('2008-09-26','yyyy-mm-dd')） OR(c."fTime" IS NULL) 

Answer 2

TRY IT..
SELECT SUM(CASE WHEN C."FTIME">=TO_DATE('2008-09-23','YYYY-MM-DD') AND C."FTIME" <TO_DATE('2008-09-26','YYYY-MM-DD') OR C."FTIME" IS NULL
                THEN 1
                ELSE 0
            END) "COUNT"
  FROM [email protected]  A,
       T_CODE_SEGMENT B, 
       [email protected] C 
 WHERE SUBSTR(NVL(C."FFROM","DESADDR"),1,7) = B.FPREFIX 
   AND A."EXTID"=C."FID"(+) 
   AND A."FTASKID"=0;

Answer 3

SELECT 
     sum(case when (c."fTime">=to_date('2008-09-23','yyyy-mm-dd') and c."fTime" < to_date('2008-09-26','yyyy-mm-dd')） OR(c."fTime" IS NULL) 
        then 1
        else 0
        end) as f_count
FROM [email protected]  a
     join  [email protected] c on  a."ExtID"=c."fid"(+)
     join  t_code_segment b on substr(nvl(c."fFrom","DesAddr"),1,7)=b.fprefix
where a."fTaskID"=0