Win a copy of Mesos in Action this week in the Cloud/Virtualizaton forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Unsure how to do subtraction using joined table values

 
vanan saravanan
Ranch Hand
Posts: 95
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
This is what i need to acheive. I have one value "Totalleave" in one table. The second value is "Leavetaken" in another table. I need to take "totalleave" minus "Leavetaken" and get the value. Primary key connecting both is "employeeID". I need help retriving values from both tables and then doing the subtraction function. Thank you.


This is my draft select statement.

String userName = (String) session.getAttribute("User");
if (null == userName) {
request.setAttribute("Error", "Session has ended. Please login.");
RequestDispatcher rd = request.getRequestDispatcher("login.jsp");
rd.forward(request, response);
}


("select Totalleave from tb_employee where employeeID = '" + userName + "'");


Your help in this matter is greatly appreciated.
[ June 12, 2006: Message edited by: Bear Bibeault ]
 
Shailesh Chandra
Ranch Hand
Posts: 1082
Java Oracle Spring
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
SELECT (A.TOTALLEAVE - B.LEAVETAKEN) AS BALANCE_LEAVE
FROM TB_EMPLOYEE A, ANOTHER_TABLE B WHERE A.EMPLOYEEID=B.EMPLOYEEID
AND A.EMPLOYEEID = ?


Shailesh
 
vanan saravanan
Ranch Hand
Posts: 95
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thank you for your reply. But i get the following error. What might be wrong. Your help is greatly appreciated.



Generated servlet error:
C:\Program Files\Apache Tomcat 4.0\work\Standalone\localhost\_\login1\secure1$jsp.java:134: ';' expected.
String query1 = SELECT (tb_employee.Leave_Entitlement - tb_leavemc.Duration) AS BALANCE_LEAVE




String query1 = SELECT (tb_employee.Leave_Entitlement - tb_leavemc.Duration) AS BALANCE_LEAVE FROM tb_employee A, tb_leavemc B WHERE A.emp_ID=B.emp_ID AND A.emp_ID = ?

PreparedStatement statement = connection.prepareStatement( query1 );
statement.setString(1, username);
 
Shailesh Chandra
Ranch Hand
Posts: 1082
Java Oracle Spring
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
My first tip would be that check the sql on your database without java.

Tell me if it works correct,also tell me what database are you using

and write your query as

String query1 = "SELECT (A.Leave_Entitlement - B.Duration) AS BALANCE_LEAVE FROM tb_employee A, tb_leavemc B WHERE A.emp_ID=B.emp_ID AND A.emp_ID = ?";


Shailesh
[ June 12, 2006: Message edited by: Shailesh Chandra ]
 
vanan saravanan
Ranch Hand
Posts: 95
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thank you very much. It works now without any errors. But if i want to display the value of "BALANCE_LEAVE" what is the coding i should write. Thank you.
 
vanan saravanan
Ranch Hand
Posts: 95
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I tried the following but it does not seem to work.

out.println("BALANCE_LEAVE");

out.println('BALANCE_LEAVE');

<%=rst.getString("BALANCE_LEAVE") %>

Could someone please help... Thank you.
 
Shailesh Chandra
Ranch Hand
Posts: 1082
Java Oracle Spring
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Originally posted by vanan saravanan:
I tried the following but it does not seem to work.

out.println("BALANCE_LEAVE");

out.println('BALANCE_LEAVE');

<%=rst.getString("BALANCE_LEAVE") %>

Could someone please help... Thank you.

  • <%=rst.getString("BALANCE_LEAVE")%>
  • <%=rst.getInt("BALANCE_LEAVE")%>
  • <%=rst.getString(1)%>
  • <%=rst.getInt(1)%>

  • OR
  • out.println(rst.getString("BALANCE_LEAVE"));
  • out.println(rst.getInt("BALANCE_LEAVE"));
  • out.println(rst.getString(1));
  • out.println(rst.getInt(1));



  • Shailesh
    [ June 13, 2006: Message edited by: Shailesh Chandra ]
     
    vanan saravanan
    Ranch Hand
    Posts: 95
    • Mark post as helpful
    • send pies
    • Quote
    • Report post to moderator
    Thank you for your reply. But i got errors as follows. I tried each one seperately.

    1)<%=rst.getString("BALANCE_LEAVE")%>
    javax.servlet.ServletException: Column 'BALANCE_LEAVE' not found.

    2)<%=rst.getInt("BALANCE_LEAVE");%>
    C:\Program Files\Apache Tomcat 4.0\work\Standalone\localhost\_\login1\secure1$jsp.java:179: ')' expected.
    out.print(rst.getInt("BALANCE_LEAVE"););

    3)<%=rst.getString(1)%>
    prints out the ´┐Żusername´┐Ż variable

    4)<%=rst.getInt(1);%>
    C:\Program Files\Apache Tomcat 4.0\work\Standalone\localhost\_\login1\secure1$jsp.java:179: ')' expected.
    out.print(rst.getInt(1););


    OR

    1)out.println(rst.getString("BALANCE_LEAVE"));
    C:\Program Files\Apache Tomcat 4.0\work\Standalone\localhost\_\login1\secure1$jsp.java:180: ')' expected.
    out.println(rst.getString("BALANCE_LEAVE"));

    2)out.println(rst.getInt("BALANCE_LEAVE"));
    C:\Program Files\Apache Tomcat 4.0\work\Standalone\localhost\_\login1\secure1$jsp.java:180: ')' expected.
    out.println(rst.getInt("BALANCE_LEAVE"));

    3)out.println(rst.getString(1));
    C:\Program Files\Apache Tomcat 4.0\work\Standalone\localhost\_\login1\secure1$jsp.java:180: ')' expected.
    out.println(rst.getString(1));

    4)out.println(rst.getInt(1));
    C:\Program Files\Apache Tomcat 4.0\work\Standalone\localhost\_\login1\secure1$jsp.java:180: ')' expected.
    out.println(rst.getInt(1));
     
    vanan saravanan
    Ranch Hand
    Posts: 95
    • Mark post as helpful
    • send pies
    • Quote
    • Report post to moderator
    This is my exact code. database is mysql and works fine.

    <%//*********************************************************************************************************
    //Check if user login sucessfully
    //**********************************************************************************************************%>

    <%@ page import="java.util.*, java.sql.*, java.text.*" %>
    <title>leave page</title>
    <%

    response.setHeader("Cache-Control","no-cache"); //forces caches to obtain a new copy of the page from the origin server
    response.setHeader("Cache-Control","no-store"); //directs caches not to store the page under any circumstance
    response.setDateHeader("Expires", 0); //causes the proxy cache to see the page as "stale"
    response.setHeader("Pragma","no-cache"); //HTTP 1.0 backward compatibility

    String userName = (String) session.getAttribute("User");
    if (null == userName) {
    request.setAttribute("Error", "Session has ended. Please login.");
    RequestDispatcher rd = request.getRequestDispatcher("login.jsp");
    rd.forward(request, response);
    }
    %>

    <%//*********************************************************************************************************
    //Database Coonection Code
    //**********************************************************************************************************%>

    <%
    Connection con = null;



    String dbURL = "jdbc:mysql://localhost/***";

    Class.forName("com.mysql.jdbc.Driver");
    con = DriverManager.getConnection(dbURL, "***", "***");

    %>


    <%//*********************************************************************************************************
    //Start of JSP(SQL) code
    //**********************************************************************************************************%>


    <%
    Statement stm=null;
    ResultSet rst=null;
    stm= con.createStatement();
    String query = ("select Name, Leave_Entitlement from tb_employee where emp_ID = '" + userName + "'");
    rst = stm.executeQuery(query);


    String query1 = "SELECT (A.Leave_Entitlement - B.Duration) AS BALANCE_LEAVE FROM tb_employee A, tb_leavemc B WHERE A.emp_ID=B.emp_ID AND A.emp_ID = ?";



    PreparedStatement statement = con.prepareStatement( query1 );
    statement.setString(1, userName);




    %>



    <%//*********************************************************************************************************
    //Start of Displaying Results code
    //**********************************************************************************************************%>

    <%

    while (rst.next()){ %>

    <%=rst.getString("Name") %>
    <%=userName%>
    out.println("BALANCE_LEAVE"); //<--------Problem lies here
    <%=rst.getString("Leave_Entitlement") %>
    <%}

    %>


    <%//*********************************************************************************************************
    //End of connection code
    //***********************************************************************************************************%>

    <%

    rst.close();
    stm.close();
    con.close();

    %>


    <p> </p>
    <p> </p>
    <p> </p>
    <p> </p>
    <p> </p>
    <p><a href="secure1.jsp">Secure 1</a>
    <br/>
    <a href="secure2.jsp">Secure 2</a>
    <br/>
    <a href="logout.jsp">Logout</a>
    <br/>
    </p>
    <p>
    This is Secure1 page. This page is pretended to contain secure information.
    </p>
     
    Shailesh Chandra
    Ranch Hand
    Posts: 1082
    Java Oracle Spring
    • Mark post as helpful
    • send pies
    • Quote
    • Report post to moderator
    vanan,

    Sorry for the runtimes errors , actually I copied some of your code with semi colons which has caused runtimes error.

    Now back to your problem

    look at this code of yours



    In the above code you are creating the prepared statement and not executing same.

    Now look at your this code


    as per your comment you have problem at code out.println("BALANCE_LEAVE"); and its only a statement which to print BALANCE_LEAVE also it is not enclosed in any scriptlets (<% %>)


    Probably you need to work little more with your code


    Shailesh
    [ June 13, 2006: Message edited by: Shailesh Chandra ]
     
    vanan saravanan
    Ranch Hand
    Posts: 95
    • Mark post as helpful
    • send pies
    • Quote
    • Report post to moderator
    In the above code you are creating the prepared statement and not executing same.

    I dont understand the above comment. The prepared statement works fine. The problem lies with printing the value of BALANCE_LEAVE.


    <%

    while (rst.next()){ %>

    <%=rst.getString("Name") %>
    <%=userName%>

    <%=rst.getString("BALANCE_LEAVE")%> // Tried each one individually but got error
    <%=rst.getString("BALANCE_LEAVE")%>// Tried each one individually but got error
    <%=rst.getInt("BALANCE_LEAVE")%>// Tried each one individually but got error
    <%=rst.getString(1)%>// Tried each one individually but got error
    <%=rst.getInt(1)%>// Tried each one individually but got error
    out.println(rst.getString("BALANCE_LEAVE"));// Tried each one individually but got error
    out.println(rst.getInt("BALANCE_LEAVE"));// Tried each one individually but got error
    out.println(rst.getString(1));// Tried each one individually but got error
    out.println(rst.getInt(1));// Tried each one individually but got error

    <%=rst.getString("Leave_Entitlement") %>
    <%}

    %>
     
    Shailesh Chandra
    Ranch Hand
    Posts: 1082
    Java Oracle Spring
    • Mark post as helpful
    • send pies
    • Quote
    • Report post to moderator
    Originally posted by vanan saravanan:

    I dont understand the above comment. The prepared statement works fine.


    ResultSet rst is output of query hold by variable query(("select Name, Leave_Entitlement from tb_employee where emp_ID = '" + userName + "'")but

    but where is the output of sql defined in variable query1("SELECT (A.Leave_Entitlement - B.Duration) AS BALANCE_LEAVE FROM tb_employee A, tb_leavemc B WHERE A.emp_ID=B.emp_ID AND A.emp_ID = ?";

    Actually I am looing for a line like
    ResultSet some_variable = statement.executeQuery()


    Shailesh



    )
     
    vanan saravanan
    Ranch Hand
    Posts: 95
    • Mark post as helpful
    • send pies
    • Quote
    • Report post to moderator
    I missed out the output for ResultSet for query1. Have included it in. But i get the error javax.servlet.ServletException: Column 'BALANCE_LEAVE' not found.

    <%//*********************************************************************************************************
    //Start of JSP(SQL) code
    //**********************************************************************************************************%>


    <%
    Statement stm=null;
    Statement stm1=null;
    ResultSet rst=null;
    ResultSet rst1=null;
    stm1= con.createStatement();
    stm= con.createStatement();
    String query = ("select Name, Leave_Entitlement from tb_employee where emp_ID = '" + userName + "'");
    rst = stm.executeQuery(query);


    String query1 = "SELECT (A.Leave_Entitlement - B.Duration) AS BALANCE_LEAVE FROM tb_employee A, tb_leavemc B WHERE A.emp_ID=B.emp_ID AND A.emp_ID = '" + userName + "'";


    rst1 = stm1.executeQuery(query1);


    %>



    <%//*********************************************************************************************************
    //Start of Displaying Results code
    //**********************************************************************************************************%>

    <%

    while (rst.next()){ %>

    <%=rst.getString("Name") %>
    <%=userName%>

    <%=rst.getString("Leave_Entitlement") %>
    <%}



    while (rst1.next()){ %>

    <%=rst.getString("BALANCE_LEAVE")%>

    <%}





    %>
    [ June 13, 2006: Message edited by: vanan saravanan ]
     
    Shailesh Chandra
    Ranch Hand
    Posts: 1082
    Java Oracle Spring
    • Mark post as helpful
    • send pies
    • Quote
    • Report post to moderator
    I am not very sure about aliases in mysql as I have never worked om mysql try index of column instead, and use the correct resultset rst1

    <%=rst1.getString(1)%>


    Shailesh
    [ June 13, 2006: Message edited by: Shailesh Chandra ]
     
    vanan saravanan
    Ranch Hand
    Posts: 95
    • Mark post as helpful
    • send pies
    • Quote
    • Report post to moderator
    Thank you so much for your patience in helping me with the problem. It is now working fine. Just one last thing.

    String query1 = "SELECT (A.Leave_Entitlement - B.Duration) AS BALANCE_LEAVE FROM tb_employee A, tb_leavemc B WHERE A.emp_ID=B.emp_ID AND A.emp_ID = '" + userName + "'";

    In my database, duration appears a few times. For example there is a record of '1' day. After a few other records, there is another record of '2' days for the same username. How do i modify the code above so that, Leave_Entitlement - total number of duration for that particular username.

    Thank you for your kind assistance.
     
    vanan saravanan
    Ranch Hand
    Posts: 95
    • Mark post as helpful
    • send pies
    • Quote
    • Report post to moderator
    Managed to figure it out myself

    String query1 = "SELECT (A.Leave_Entitlement - sum(B.Duration)) AS BALANCE_LEAVE FROM tb_employee A, tb_leavemc B WHERE A.emp_ID=B.emp_ID AND A.emp_ID = '" + userName + "'";


    Thank you for your help.
     
    Shailesh Chandra
    Ranch Hand
    Posts: 1082
    Java Oracle Spring
    • Mark post as helpful
    • send pies
    • Quote
    • Report post to moderator
    cheers

    Shailesh
     
    • Post Reply
    • Bookmark Topic Watch Topic
    • New Topic