This discussion is archived
1 Reply Latest reply: Dec 9, 2013 5:40 AM by Marwim RSS

Not getting desired output...

Vijetha Explorer
Currently Being Moderated

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??

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points