• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

SQL statement to java

 
Kingsley Mullers
Ranch Hand
Posts: 48
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Bartender
Posts: 10336
Eclipse IDE Hibernate Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 1082
Java Oracle Spring
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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



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
Ranch Hand
Posts: 48
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 1082
Java Oracle Spring
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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



[ March 14, 2005: Message edited by: Shailesh Chandra ]
 
Kingsley Mullers
Ranch Hand
Posts: 48
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 1082
Java Oracle Spring
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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



and write your code as


[ March 14, 2005: Message edited by: Shailesh Chandra ]
 
Kingsley Mullers
Ranch Hand
Posts: 48
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 1082
Java Oracle Spring
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Mandla,

hope this will help you



thanks
 
Shailesh Chandra
Ranch Hand
Posts: 1082
Java Oracle Spring
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic