Welcome to the Java Programming Forums


The professional, friendly Java community. 21,500 members and growing!


The Java Programming Forums are a community of Java programmers from all around the World. Our members have a wide range of skills and they all have one thing in common: A passion to learn and code Java. We invite beginner Java programmers right through to Java professionals to post here and share your knowledge. Become a part of the community, help others, expand your knowledge of Java and enjoy talking with like minded people. Registration is quick and best of all free. We look forward to meeting you.


>> REGISTER NOW TO START POSTING


Members have full access to the forums. Advertisements are removed for registered users.

Results 1 to 1 of 1

Thread: If Statement in SQL String

  1. #1
    Junior Member
    Join Date
    Mar 2010
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Default If Statement in SQL String

    I am having to fix another developes code who left and I am confused as to how to do so as I am just getting back into programming after many years other places... here is the issue

    I 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.");
     
    }
     
    }
    Last edited by helloworld922; March 30th, 2010 at 07:07 PM. Reason: please use [code] tags!


Similar Threads

  1. If-Else Statement help
    By SnowCrashed in forum Loops & Control Statements
    Replies: 5
    Last Post: February 9th, 2010, 07:57 PM
  2. Having trouble with an if statement. please help!!
    By humdinger in forum Loops & Control Statements
    Replies: 11
    Last Post: January 21st, 2010, 02:49 PM
  3. If Statement Java Need help
    By Keno888 in forum Loops & Control Statements
    Replies: 5
    Last Post: October 25th, 2009, 01:53 AM
  4. unreachable statement?
    By chronoz13 in forum Loops & Control Statements
    Replies: 1
    Last Post: September 11th, 2009, 10:06 AM
  5. If statement
    By Dave in forum Loops & Control Statements
    Replies: 2
    Last Post: August 27th, 2009, 10:11 AM