SQL WITH Clause and OR operator

Question

I am trying to fetch some records based on some conditions as given below -

WITH temp_data AS(SELECT t1.acc_name,t1.f_data,t1.EXPIRY_DATE,t1.f_days
            FROM t1_table t1, t2_table t2 where 
            t1.acc_name = t2.acc_name
            AND t2.FLAG = 2)            
SELECT tmp_data.acc_name 
FROM    temp_data tmp_data ,t3_table t3  where
( tmp_data.f_data = 'N' AND tmp_data.EXPIRY_DATE <= SYSDATE)
OR 
( tmp_data.f_data = 'Y' AND tmp_data.acc_name =  t3.acc_name 
              AND
              (
                (t3.f_interval_period <>-1 AND t3.f_DATE <= SYSDATE)
              OR (t3.f_interval_period =-1 AND  t3.f_DATE+t1.f_days <= SYSDATE)
              ))

When tmp_data.f_data = 'N' AND tmp_data.EXPIRY_DATE <= SYSDATE condition satisfies , I am getting repetitive 180 records for 1 account name(e.g acc_name1 180 times) , when I am running the 'N' and 'Y' conditions separately one by one using with clause I get correct result(e.g. acc_name1 only once), for e.g

  WITH temp_data AS(SELECT t1.acc_name,t1.f_data,t1.EXPIRY_DATE,t1.f_days
                    FROM t1_table t1, t2_table t2 where 
                    t1.acc_name = t2.acc_name
                    AND t2.FLAG = 2)            
        SELECT tmp_data.acc_name 
        FROM    temp_data tmp_data  where
        ( tmp_data.f_data = 'N' AND tmp_data.EXPIRY_DATE <= SYSDATE)

==> this is without the addition of t3_table and gives only 1 account name and works fine, but merging these two (tmp_data.f_data = 'N' , 'Y')with OR , I am not getting expected result and same account_number gets repeated multiple times.

Answer 1

I suppose you are missing the join condition, which I guess is tmp_data.acc_name = t3.acc_name for 'N' condition. So, when you combine the results you might get cartesian product of tmp_data and t3 datasets which is blowing your result. Just a guess, you have to change somehow like that:

....
FROM   temp_data tmp_data, t3_table t3
where
   tmp_data.acc_name =  t3.acc_name and
   (
      ( tmp_data.f_data = 'N' AND tmp_data.EXPIRY_DATE <= SYSDATE)
      OR 
      (
        tmp_data.f_data = 'Y'
        AND
        (
           (t3.f_interval_period <> -1 AND  t3.f_DATE <= SYSDATE)
           OR
           (t3.f_interval_period =  -1 AND  t3.f_DATE + t1.f_days <= SYSDATE)
        )
      )
   )

Update 1: Thinking about left outer join, if I remember it correctly in Oracle (+) was used

....
FROM   temp_data tmp_data, t3_table t3
where
   tmp_data.acc_name =  t3.acc_name (+) and
   (
      ( tmp_data.f_data = 'N' AND tmp_data.EXPIRY_DATE <= SYSDATE)
      OR 
      (
        tmp_data.f_data = 'Y' and t3.acc_name is not null
        AND
        (
           (t3.f_interval_period <> -1 AND  t3.f_DATE <= SYSDATE)
           OR
           (t3.f_interval_period =  -1 AND  t3.f_DATE + t1.f_days <= SYSDATE)
        )
      )
   )

Take attention where you require the acc_name, specify conditon t3.acc_name is not null

Update 2: If your version of Oracle supports join keyword, most probably it does

WITH temp_data AS(SELECT t1.acc_name,t1.f_data,t1.EXPIRY_DATE,t1.f_days
            FROM t1_table t1 inner join t2_table t2 on t1.acc_name = t2.acc_name
            where  t2.FLAG = 2)            
SELECT tmp_data.acc_name 
FROM    temp_data tmp_data left join t3_table t3  on tmp_data.acc_name =  t3.acc_name 
where
( tmp_data.f_data = 'N' AND tmp_data.EXPIRY_DATE <= SYSDATE)
OR 
( tmp_data.f_data = 'Y' AND t3.acc_name is not null
              AND
              (
                (t3.f_interval_period <>-1 AND t3.f_DATE <= SYSDATE)
              OR (t3.f_interval_period =-1 AND  t3.f_DATE+t1.f_days <= SYSDATE)
              ))