1 Reply Latest reply: Dec 9, 2013 7:40 AM by Marwim RSS

    Not getting desired output...

    Vijetha

      Hi all,

      Again stuck with a problem.

      I executed the following code:

      SELECT ACCT_FD_NO, old_fdr_no, ACCT_CUST_CODE, acct_opn_dt, maturity_date FROM KEC_FDACCT_MSTR WHERE staus='D' AND

      acct_type NOT  IN(1,2,3,4,5,6,13,14,15,16,17,18,25,26,27,28,29,30) AND old_fdr_no IS NOT NULL AND

      TO_CHAR(MATURITY_DATE,'YYYY')=TO_CHAR(TO_DATE(:X_PRM_YR_END_DT,'dd/mm/yyyy'),'YYYY');

       

      ACCT_FD_NO

      OLD_FDR_NO

      ACCT_CUST_CODE

      ACCT_OPN_DT

      MATURITY_DATE

      330360

      10023

      193

      30-Dec-10

      29-Dec-13

      330363

      10024

      194

      30-Dec-10

      29-Dec-13

      330362

      10025

      195

      30-Dec-10

      29-Dec-13

      330361

      10026

      196

      30-Dec-10

      29-Dec-13

      320117

      20006

      128

      21-Dec-11

      20-Dec-13

      330275

      40008

      55

      15-Dec-10

      14-Dec-13

      320094

      70058

      649

      12-Jan-11

      11-Jan-13

      320090

      70059

      650

      11-Jan-11

      10-Jan-13

      320091

      70060

      651

      12-Jan-11

      11-Jan-13

      320092

      70061

      652

      12-Jan-11

      11-Jan-13

      320093

      70062

      655

      12-Jan-11

      11-Jan-13

      320113

      80005

      116

      18-Dec-11

      17-Dec-13

      320109

      80026

      698

      31-Dec-11

      30-Dec-13

       

      I got the required records. As you can see, Maturity_date belongs to year 2013.

       

      But when i added the above to the below select code, that is

      SELECT ACCT_FD_NO,ACCT_CUST_CODE, AMOUNT,INT_RATE ,MATURITY_DATE,(AMOUNT*(INT_RATE/100)/365)no_of_days,

      (AMOUNT*(INT_RATE/100)/365)*((TO_DATE(:X_PRM_ST_DT)-TO_DATE(:X_PRM_YR_END_DT)))tot_int  FROM KEC_FDACCT_MSTR;

      Which is as below:

      SELECT ACCT_FD_NO,ACCT_CUST_CODE, AMOUNT,INT_RATE ,MATURITY_DATE,(AMOUNT*(INT_RATE/100)/365)no_of_days,

      (AMOUNT*(INT_RATE/100)/365)*((TO_DATE(:X_PRM_ST_DT)-TO_DATE(:X_PRM_YR_END_DT)))tot_int  FROM KEC_FDACCT_MSTR

      WHERE ACCT_FD_NO IN(SELECT OLD_FDR_NO FROM KEC_FDACCT_MSTR WHERE staus='D' AND

      acct_type NOT  IN(1,2,3,4,5,6,13,14,15,16,17,18,25,26,27,28,29,30) AND old_fdr_no IS NOT NULL AND

      TO_CHAR(MATURITY_DATE,'YYYY')=TO_CHAR(TO_DATE(:X_PRM_YR_END_DT,'dd/mm/yyyy'),'YYYY'));

       

      ACCT_FD_NO

      ACCT_CUST_CODE

      AMOUNT

      INT_RATE

      MATURITY_DATE

      10023

      193

      50,000.00

      1. 12.00

      29-Dec-10

      10024

      194

      50,000.00

      1. 12.00

      29-Dec-10

      10025

      195

      50,000.00

      1. 12.00

      29-Dec-10

      10026

      196

      50,000.00

      1. 12.00

      29-Dec-10

      20006

      128

      60,000.00

      1. 12.50

      20-Dec-11

      40008

      55

      50,000.00

      1. 12.50

      14-Dec-10

      70058

      649

      200,000.00

      1. 12.00

      11-Jan-11

      70059

      650

      200,000.00

      1. 12.00

      10-Jan-11

      70060

      651

      200,000.00

      1. 12.00

      11-Jan-11

      70061

      652

      200,000.00

      1. 12.00

      11-Jan-11

      70062

      655

      90,000.00

      1. 12.00

      11-Jan-11

      80005

      116

      50,000.00

      1. 12.50

      17-Dec-11

      80026

      698

      50,000.00

      1. 12.50

      30-Dec-11

       

      I'm not getting the desired output..

      I'm getting the OLD_FDR_NO in the field of ACCT_FD_NO, as you can see its matching.

      But MATURITY_DATE is not matching . Look at the 'year', They are not matching. More over, i'm querying for the record which matches with :X_PRM_YR_END_DT (1/Apr/2013),

      so i should get records which has the MATURITY_DATE 'year' as 2013 (as got in the first output). But all other than '2013' year is populated??