Thread: 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.");
 
}
 
}