Results 1 to 7 of 7
  1. #1
    Steffi1013 is offline Member
    Join Date
    Mar 2010
    Posts
    1
    Rep Power
    0

    Unhappy If Statement in SQL

    I am having to fix another developes code who left and I am consused as to how to do so... here is the issue

    He gets the employee id from the Report Menu table. Right now a bunch of users have the same employee id because they are contractors. He grabs the login id from their session, uses it to get the employee id from ReportMenu, then uses that employee id to grab their first and last name from the EMPLOYEE table. The first and last name for the generic employee id is Dummy Contractor. However, in the report menu table, there is an EmpName field that we can use instead for the dummy contractor employee id. It has the person's real last and first name.


    So I think I need to do an if statement in the second set of sql statements, though it was mentioned that I might need to create a method or something for the first select statement and call that unconditionally at first, and then in the second select statement call that method condtionally... if this is correct, how do I do that???

    btw... you can ignore the middle select because that has to be there between the two other select statements to allow permission into Lawson.
    ---- (SELECT RTRIM(AcctUnit)"; sQuery += " FROM RptRollupMaintenance)


    It is just the first and third select that I am having the conundrum with...

    Here is the code...
    public static boolean Load(User pUser) {

    boolean isLoaded = false;

    if (isLoadable(pUser)) {

    Connection cx = null;

    if (pUser.getPassword() == null) {

    //load security info from reporting DB

    cx = RptingDwAccess.getConnection();

    if (cx == null) {

    pUser.setError("Unable to connect to the reportingdw database. Please try later. If the problem persists, contact your administrator.");

    } else {

    Statement stmt = null;

    ResultSet rs = null;

    String sQuery = "";

    try {

    stmt = cx.createStatement();

    sQuery = " SELECT WEB_USER, EMPLOYEE, COMPANY, EmpName";

    sQuery += ", ID_TYPE, 'MVTPROD9', USR_EMAIL";

    sQuery += ", Replevel, Division";

    sQuery += " FROM ReportMenu";

    if (!pUser.getLogin().equals("")) {

    sQuery += " WHERE LOWER(RTRIM(WEB_USER)) " + DBUtils.getSqlString(pUser.getLogin().toLowerCase( ),true,true);

    } else if ((pUser.getCompany() > -1) && (pUser.getObjectId() > -1)) {

    sQuery += " WHERE EMPLOYEE = " + pUser.getObjectId();

    sQuery += " AND COMPANY = " + pUser.getCompany();

    } else {

    sQuery += " WHERE EMPLOYEE = " + pUser.getObjectId();

    }

    rs = stmt.executeQuery(sQuery);

    if (rs.next()) {

    pUser.setLogin(rs.getString(1));

    pUser.setObjectId(rs.getLong(2));

    pUser.setCompany(rs.getInt(3));

    pUser.setDescription(rs.getString(4));

    pUser.setIdType(rs.getString(5));

    pUser.setProductline(rs.getString(6));

    pUser.setEmail(rs.getString(7));

    pUser.setPlAccess(rs.getString(8));

    pUser.setPlAccessList(rs.getString(9));

    } else {

    pUser.setError("User '" + pUser.getLogin() + "' not found. Contact the administrator to setup your security record.");

    }

    if (!pUser.isError()) {

    Vector vStrings = new Vector();

    if (pUser.getPlAccess().toLowerCase().startsWith(User .PLACCESS_FLOAT)) {

    vStrings = pUser.getPlAccessList();

    //add region process levels

    int iRegion = -1;

    try {

    iRegion = Integer.parseInt(pUser.getPlAccess().substring(Use r.PLACCESS_FLOAT.length()));

    } catch(NumberFormatException nfe) {

    }

    sQuery = " SELECT RTRIM(AcctUnit)";

    sQuery += " FROM RptRollupMaintenance";

    if (iRegion > 0) {

    sQuery += " WHERE SrRegion = " + iRegion;

    } else {

    sQuery += " WHERE ProcessLevel <= " + ProcessLevel.DIVISION_MAX;

    }

    sQuery += " ORDER BY STR(AcctUnit)";

    //System.out.println(sQuery);

    rs = stmt.executeQuery(sQuery);

    while (rs.next()) {

    vStrings.addElement(rs.getString(1));

    }

    } else if (pUser.isCorp() || pUser.isDivFloater()) {

    vStrings = pUser.getPlAccessList();

    //add corp process levels

    Vector vPLs = pUser.getProcessLevels();

    for (int index = 0; index < vPLs.size(); index++) {

    vStrings.addElement(((ProcessLevel)vPLs.elementAt( index)).getProcessLevel());

    }

    }

    if (!vStrings.isEmpty()) pUser.setPlAccessList(vStrings);

    }

    } catch (Exception e) {

    pUser.setError("Error loading web user data for " + pUser.getLogin() + "<br>" + StringUtils.getStackTraceAsString(e) + "<br><br>Query=" + sQuery);

    //System.out.println("\n" + e.getMessage());

    } finally {

    try {if (rs != null) rs.close(); } catch (SQLException se) {}

    try {if (stmt != null) stmt.close();} catch (SQLException se) {}

    try {if (cx != null) cx.close(); } catch (SQLException se) {}

    }

    }

    }

    if (!pUser.isError()) {

    //load info from Lawson DB

    cx = LawsonAccess.getConnection();

    if (cx == null) {

    pUser.setError("Unable to connect to the Lawson database.");

    } else {

    Statement stmt = null;

    ResultSet rs = null;

    String sQuery = "";

    try {

    stmt = cx.createStatement();

    sQuery = " SELECT COMPANY, EMPLOYEE, FIRST_NAME, MIDDLE_INIT, LAST_NAME, PROCESS_LEVEL";

    sQuery += ", EMP_STATUS, EXEMPT_EMP, R_POSITION, HM_ACCT_UNIT";

    sQuery += " FROM EMPLOYEE";

    if (pUser.getPassword() != null) {

    sQuery += " WHERE LOWER(LAST_NAME) " + DBUtils.getSqlString(pUser.getLogin().toLowerCase( ),true,true);

    sQuery += " AND RIGHT(RTRIM(FICA_NBR),4) " + DBUtils.getSqlString(pUser.getPassword(),true,true );

    } else {

    sQuery += " WHERE EMPLOYEE = " + pUser.getObjectId();

    sQuery += " AND COMPANY = " + pUser.getCompany();

    }

    //sQuery += " AND (TERM_DATE < '1/1/1800' OR TERM_DATE > GetDate())";

    sQuery += " AND EMP_STATUS NOT LIKE 'T%'";

    rs = stmt.executeQuery(sQuery);

    if (rs.next()) {

    pUser.setCompany(rs.getInt("COMPANY"));

    pUser.setObjectId(rs.getLong("EMPLOYEE"));

    pUser.setFirstName(rs.getString("FIRST_NAME"));

    pUser.setMiddleName(rs.getString("MIDDLE_INIT"));

    pUser.setLastName(rs.getString("LAST_NAME"));

    pUser.getProcessLevel().setProcessLevel(rs.getStri ng("PROCESS_LEVEL"));

    pUser.setStatus(rs.getString("EMP_STATUS"));

    pUser.setExempt(rs.getString("EXEMPT_EMP"));

    pUser.setPositionNumber(rs.getString("R_POSITION") );

    pUser.setAccountingUnit(rs.getString("HM_ACCT_UNIT "));

    isLoaded = true;

    } else {

    if (pUser.getIdType().toLowerCase().endsWith("temp")) {

    pUser.setFirstName("Temp");

    pUser.setMiddleName("");

    pUser.setLastName("User");

    pUser.getProcessLevel().setProcessLevel(pUser.PROC _LVL_CORP[0]);

    pUser.setStatus("A");

    pUser.setExempt(false);

    isLoaded = true;

    } else {

    pUser.setError("Active employee " + pUser.getObjectId() + " in company " + pUser.getCompany() + " not found. Inform the administrator to update your security record with your correct company number.");

    }

    }

  2. #2
    Tolls is offline Moderator
    Join Date
    Apr 2009
    Posts
    11,755
    Rep Power
    19

    Default

    First off, post this in CODE tags. Lack of formatting makes this really difficult to read, especially with all the extra blank lines.

    Second, you really should be using a PreparedStatement and binding the variables in...not concatentating them into a SQL string. For example, this:

    Java Code:
    String sql = "SELECT something FROM some_table WHERE this_thing = " + myVariable;
    rs = st.executeQuery(sql);
    is bad. It should be:

    Java Code:
    String sql = "SELECT something FROM some_table WHERE this_thing = ?";
    PreparedStatment ps = conn.prepareStatement(sql);
    ps.setInt(myVariable);
    ps.execute();
    (note some method names may be slightly wrong, always check the API).

    Oh, and when you catch an exception you really ought to log the stack trace.

    After those changes ( and the code tags) we might actually be able to see what's going on.

  3. #3
    RamyaSivakanth's Avatar
    RamyaSivakanth is online now Senior Member
    Join Date
    Apr 2009
    Location
    Chennai
    Posts
    800
    Rep Power
    6

    Default

    Another suggestion here is ,if we want to debug easily always use column names for retrieval instead of index...
    Ramya:cool:

  4. #4
    masijade is offline Senior Member
    Join Date
    Jun 2008
    Posts
    2,571
    Rep Power
    9

    Default

    Quote Originally Posted by RamyaSivakanth View Post
    Another suggestion here is ,if we want to debug easily always use column names for retrieval instead of index...
    No, that is more of a "personal style" suggestion than it is any kind of valid "best practice" suggestion. In performance, using the index is actually quicker (there is no name lookup to determine the index, which is what is used when the retreival is actually made). Not that it is enough to have any impact except under the "heaviest" of use conditions.

  5. #5
    Tolls is offline Moderator
    Join Date
    Apr 2009
    Posts
    11,755
    Rep Power
    19

    Default

    Quite, if you've listed your returned values (as you should) rather than simply done "SELECT *" then there's no reason not using the index for the column.

  6. #6
    RamyaSivakanth's Avatar
    RamyaSivakanth is online now Senior Member
    Join Date
    Apr 2009
    Location
    Chennai
    Posts
    800
    Rep Power
    6

    Default

    might be index wise performance wise some benefit will be there,but if we have n number of columns in table,then name wise retreival is always better for a new person to debug the code.
    Ramya:cool:

  7. #7
    j2me64's Avatar
    j2me64 is offline Senior Member
    Join Date
    Sep 2009
    Location
    Zurich, Switzerland
    Posts
    962
    Rep Power
    5

    Default

    Quote Originally Posted by Steffi1013 View Post
    He gets the employee id from the Report Menu table. Right now a bunch of users have the same employee id because they are contractors. He grabs the login id from their session, uses it to get the employee id from ReportMenu, then uses that employee id to grab their first and last name from the EMPLOYEE table. The first and last name for the generic employee id is Dummy Contractor. However, in the report menu table, there is an EmpName field that we can use instead for the dummy contractor employee id. It has the person's real last and first name.

    why don't you solve the above problem through a view that joins the reportmenu with the employee table? this view could give you back the first and last name from the report menu table if the id exists or Dummy Contractor if the id doesn't exist.

Similar Threads

  1. add an If Else statement and......uh????
    By sonny in forum New To Java
    Replies: 6
    Last Post: 03-04-2010, 06:57 PM
  2. for statement help
    By helpisontheway in forum New To Java
    Replies: 5
    Last Post: 11-14-2009, 04:14 PM
  3. if statement help please!!
    By soc86 in forum New To Java
    Replies: 5
    Last Post: 12-02-2008, 02:56 PM
  4. Statement or Prepared Statement ?
    By paty in forum JDBC
    Replies: 3
    Last Post: 08-01-2007, 04:45 PM
  5. If Statement
    By aDrizzle in forum New To Java
    Replies: 4
    Last Post: 07-08-2007, 08:55 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •