SQL statement to java

[Kingsley Mullers]

i have the following SQL statement and have tested it on SQL plusand SQL navigator but i want to use the same statement on my JSP or Java, how do i convert this statement to a java code or do i save it as a procedure and call it on my code (how do i do that)
DECLARE
v_first_date varchar2(10);
v_last_date varchar2(10);
v_original_date varchar2(10) := '200401';
BEGIN
select substr(v_original_date,1,4)||'-'||lpad(to_number(substr(v_original_date,5,2)-1),2,'0')||'-'||'07',
substr(v_original_date,1,4)||'-'||substr(v_original_date,5,2)||'-'||'06'
into v_first_date,v_last_date
from dual ;
dbms_output.put_line('v_first_date '||v_first_date);
dbms_output.put_line('v_last_date '||v_last_date);
END;

[Paul Sturrock]

Your procedure is anonomously defined - you need to create the procedure properly (as I think you are aware via "create or replace procedure foo ( ... )") and call it from Java via a CallableStatement.

That being said, your procedure seems to only manipulate a date format. You don't need to use any DB code to do this - particularaly since you don't retrieve any data from the DB. So why not just do this in Java?

[Shailesh Chandra]

Yes procedure is the answer
but creating procedure will not help because code here is using

"dbms_output.put_line" that is mean for sql console

I think this can be done in very simple way

 
select substr(v_original_date,1,4)||'-'||
lpad(to_number(substr(v_original_date,5,2)-1),2,'0')||'-'||'07' [B]v_first_date[/B],
substr(v_original_date,1,4)||'-'||substr(v_original_date,5,2)||'-'||'06' [B]v_last_date[/B]
from dual 

and after executing this query value can be retrieve from resultset

like rs.getString("v_last_date")
[ March 04, 2005: Message edited by: Shailesh Chandra ]

[Kingsley Mullers]

this is the full code that i have written but i have a feeling there's a syntax error here since this is not executing at all. Can you please check where i've gone wrong...Many thanks

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 = ? ");


String BillDate =("select substr(v_original_date,1,4)||'-'||lpad(to_number(substr(v_original_date,5,2)-1),2,'0')||'-'||'07' v_first_date,substr(v_original_date,1,4)||'-'||substr(v_original_date,5,2)||'-'||'06' v_last_date from dual ");
rs.getString("v_first_date");
rs.getString ("v_last_date ");


if (billtype.equals(" AB ")) {
strBuff.append (" select ccp08_msisdn");
strBuff.append (" from so_cpp.tcp08_payphone_histories");
strBuff.append (" where ccp08_date_actioned <= to_date('v_first_date','yyyy-mm-dd'");
strBuff.append (" and nvl(ccp08_date_ceased,sysdate) < to_date('v_last_date','yyyy-mm-dd') ");
}
else if (billtype.equals(" ANB ")){
strBuff.append (" select ccp08_msisdn");
strBuff.append ("from so_cpp.tcp08_payphone_histories");
strBuff.append (" where ccp08_date_actioned <= to_date('v_first_date','yyyy-mm-dd')");
strBuff.append (" and nvl(ccp08_date_ceased,sysdate+1) > to_date('v_last_date','yyyy-mm-dd') ");
}

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);
}

[Shailesh Chandra]

Mandla,

I have given some comments in your code, can you tell what is final query you want to make, because it seems you are making wrong query.

Thanks

 
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 = ? ");
 
 
String BillDate =("select substr(v_original_date,1,4)||'-'||lpad(to_number(substr(v_original_date,5,2)-1),2,'0')
||'-'||'07' v_first_date,substr(v_original_date,1,4)||'-'||substr(v_original_date,5,2)||'-'||'06' v_last_date 
from dual "); 
  [quote]
[B] Where are you using BillDate [/B]
[/quote]
 
 
rs.getString("v_first_date");
rs.getString ("v_last_date ");
 
  [quote]
[B] have you  executed query, if not then how do u expect a value 
and also what is use of rs.getString() in above two lines [/B]
[/quote]
 
 
 
if (billtype.equals(" AB ")) {
 
 
strBuff.append (" select ccp08_msisdn"); 
strBuff.append (" from so_cpp.tcp08_payphone_histories");
strBuff.append (" where ccp08_date_actioned <= to_date('v_first_date','yyyy-mm-dd'");
strBuff.append (" and nvl(ccp08_date_ceased,sysdate) < to_date('v_last_date','yyyy-mm-dd') ");
}
else if (billtype.equals(" ANB ")){
strBuff.append (" select ccp08_msisdn"); 
strBuff.append ("from so_cpp.tcp08_payphone_histories");
strBuff.append (" where ccp08_date_actioned <= to_date('v_first_date','yyyy-mm-dd')");
strBuff.append (" and nvl(ccp08_date_ceased,sysdate+1) > to_date('v_last_date','yyyy-mm-dd') "); 
}
 
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 ");
} 

[ March 14, 2005: Message edited by: Shailesh Chandra ]

[Kingsley Mullers]

thanks for the response, i'm new in devepment hope you dont mind me throwing stupid question..

After executing this the statement strBuff, where i pass a value (200303) then i get back the two values (v_first_date and v_last_date) which i'm supposed to be retreiving from the BillDate, retreiving the 2 i need to pass them to the if else SQL statement.(is the code below correct...thanks

String BillDate =("select substr(v_original_date,1,4)||'-'||lpad(to_number(substr(v_original_date,5,2)-1),2,'0')||'-'||'07' v_first_date,substr(v_original_date,1,4)||'-'||substr(v_original_date,5,2)||'-'||'06' v_last_date from dual ");
pstmt2 = conn.prepareStatement(BillDate);
rs = pstmt2.executeQuery();
rs.getString("v_first_date");
rs.getString ("v_last_date ");

[Shailesh Chandra]

Mandla,

you need not to execute saperate query for getting date you can simply use same in your query.

1. you have a date 200303 ie yyyymm format
2. you are formating this date as 2003-02-07 and 2003-03-06 with help of dual
3. you are using same as v_first_date and v_last_date

instead of executing query on dual you can use directly to_date function of oracle.

try to execute query on sql plus then put them in code

make query like

 
SELECT CCP08_MSISDN FROM SO_CPP.TCP08_PAYPHONE_HISTORIES
WHERE 
CCP08_DATE_ACTIONED <= ADD_MONTHS(TO_DATE('v_original_date'||'07','YYYYMMDD'),-1)
AND
NVL(CCP08_DATE_CEASED,SYSDATE) < TO_DATE('v_original_date'||'06',,'YYYYMMDD')

and write your code as

 
 
if ("AB".equals(billtype)) 
{
strbuff.append (" SELECT CCP08_MSISDN"); 
strbuff.append (" FROM SO_CPP.TCP08_PAYPHONE_HISTORIES");
strbuff.append (" WHERE ")
        strbuff.append ("CCP08_DATE_ACTIONED <=  add_months(TO_DATE('");
strbuff.append (v_original_date);
strbuff.append ("07");
strbuff.append ("','YYYYMMDD'),-1)");
strbuff.append (" AND NVL(CCP08_DATE_CEASED,SYSDATE) < TO_DATE('");
strbuff.append (v_original_date);
strbuff.append ("06");
strbuff.append ("','YYYYMMDD')");
}
 

[ March 14, 2005: Message edited by: Shailesh Chandra ]

[Kingsley Mullers]

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);
}

[Shailesh Chandra]

Mandla,

hope this will help you

 
if ("AB".equals(billtype)) 
{
strbuff.append (" SELECT CCP08_MSISDN"); 
strbuff.append (" FROM SO_CPP.TCP08_PAYPHONE_HISTORIES");
strbuff.append (" WHERE ")        
strbuff.append ("CCP08_DATE_ACTIONED <=  add_months(TO_DATE(");//[B]remove single quote in TO_DATE('[/B]
strbuff.append (?);//[B]replace v_original_date here set in prepared statement
and also add 07 in v_original_date before setting it.[/B]
[B]//[/B]strbuff.append ("07");
strbuff.append (",'YYYYMMDD'),-1)");
strbuff.append (" AND NVL(CCP08_DATE_CEASED,SYSDATE) < TO_DATE(");[B]remove single quote in TO_DATE('[/B]
strbuff.append (?);//[B]replace v_original_date do the same as above 
add 06 in v_original_date [/B]
[B]//[/B]strbuff.append ("06");
strbuff.append (",'YYYYMMDD')");
}
 

thanks

[Shailesh Chandra]

Originally posted by Mandla Mwelase:


String strSQL = strBuff.toString();

Mandla,

Still you would face hard time to execute query in java. because you are adding to select sql in you StringBuffer. it will work fine IFF no if condition is satisifed in your code.

thanks