thanks for the response,
the v_original_date is equal to Bill_Period
strBuff.append("WHERE CCP07_BILL_PERIOD = ?"); which is then formatted.
how do i pass this original_date to the if else statement
(strBuff.append (v_original_date)
do i also put a question mark here i.e " ?"
StringBuffer strBuff= new StringBuffer();
strBuff.append(" SELECT CCP07_MSISDN, ");
strBuff.append(" CCP07_IN_CALLS, CCP07_OUT_CALLS, ");
strBuff.append(" CCP07_IN_SECONDS, CCP07_OUT_SECONDS, ");
strBuff.append(" CCP07_IN_COST, CCP07_OUT_COST, ");
strBuff.append(" TO_CHAR(CCP07_DATE_MODIFIED,'YYYY-MM-DD'), CCP07_USER_ID ");
strBuff.append(" FROM SO_CPP.TCP07_PAYPHONE_CALLS,SO_CPP.TCP06_PAYPHONES ");
strBuff.append(" WHERE CCP07_BILL_PERIOD = ? ");
strBuff.append(" AND CCP06_TYPE_CODE = ? ");
if(billtype.equals(" AB ")) {
strBuff.append (" SELECT CCP08_MSISDN");
strBuff.append (" FROM SO_CPP.TCP08_PAYPHONE_HISTORIES, SO_CPP.TCP_06PAYPHONES");
strBuff.append (" WHERE CCP08_MSISDN =CCP06_MSISDN ");
strBuff.append (" AND CCP08_DATE_ACTIONED <= ADD_MONTHS(TO_DATE(' ");
strBuff.append (v_original_date);
strBuff.append ("07");
strBuff.append ("','YYYYMMDD')-1)");
strBuff.append (" AND NVL(CPP08_DATE_CEASED,SYSDATE) < TO_DATE(' ");
strBuff.append (v_original_date);
strBuff.append ("06");
strBuff.append ("','YYYYMMDD')");
}
else if (billtype.equals(" ANB ")){
strBuff.append (" SELECT CCP08_MSISDN");
strBuff.append (" FROM SO_CPP.TCP08_payphone_HISTORIES");
strBuff.append (" WHERE CCP08_DATE_ACTIONED <= ADD_MONTHS(TO_DATE(' ");
strBuff.append (v_original_date);
strBuff.append ("07");
strBuff.append ("','YYYYMMDD')-1)");
strBuff.append (" AND NVL(CPP08_DATE_CEASED,SYSDATE+1) > TO_DATE(' ");
strBuff.append (v_original_date);
strBuff.append ("06");
strBuff.append (" ','YYYYMMDD')");
}
else if(billtype.equals("AA")){
strBuff.append ("SELECT DISTINCT CCP08_MSISDN ");
strBuff.append (" FROM SO_CPP.TCP06_PAYPHONES, SO_CPP.TCP08_PAYPHONE_HISTORIES ");
strBuff.append ("WHERE CCP06_MSISDN = ccp08_msisdn");
strBuff.append (" AND NVL (CCP08_DATE_CEASED,SYSDATE+1)>SYSDATE ");
}
else if(billtype.equals("IB")){
strBuff.append (" SELECT DISTINCT CCP07_MSISDN ");
strBuff.append (" FROM SO_CPP.TCP07_PAYPHONE_CALLS, SO_CPP.TCP06_PAYPHONES ");
strBuff.append (" WHERE ccp06_MSISDN=CCP07_MSISDN ");
strBuff.append (" AND NVL (CCP06_DATE_CEASED,SYSDATE-1)< SYSDATE ");
}
String strSQL = strBuff.toString();
try
{
pstmt1 = conn.prepareStatement(strSQL);
pstmt1.setString(1,billperiod);
rs = pstmt1.executeQuery();
}
catch (Exception e)
{
logger.debug("Error on database unreachable statements"+ e);
System.out.println("Error on database unreachable statements"+ e);
}