3 Replies Latest reply: Aug 5, 2013 5:29 PM by 922365 RSS

    Want to avoid cartesian Product but cannot avoid it some how.

    922365

      Hi,

      I am using oracle 10G .

       

      trying to draft a query which is resulting in absurd results.

       

      select i.corr_acc_no, sum(amtsign.amount_6) from item i,

      (select corr_Acc_no,flag_2,decode(fund_code,'C',amount_6,amount_6*-1) amount_6 from item

      where corr_AcC_no in (select corr_acc_no from bank where local_Acc_no in ('MBL-SEG-NZD'))and flag_2=0) AmtSign

      where i.corr_acc_no=amtsign.corr_acc_no

      and i.flag_2=0

      group by  i.corr_acc_no

      ;

       

      Basically the inner query, which i am using as the second table in the join, gives 13 results. I want to sum the amount_6 field in main select clause which I want to get from results of

      (select corr_Acc_no,flag_2,decode(fund_code,'C',amount_6,amount_6*-1) amount_6 from item

      where corr_AcC_no in (select corr_acc_no from bank where local_Acc_no in ('MBL-SEG-NZD'))and flag_2=0)

       

      The sum took too much time so i drafted simple query to check how many rows are returned by removing sum and i noticed it gives a Cartesian product with 169 rows instead of just 13. I have 2 join conditions but the value for corr_Acc_no is same for all 13 rows, i can't use amount_6 as join becuase i am changing it's sign  in the inner query. Please advise.