If Statement in SQL

[Steffi1013]

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

}

}

[Tolls]

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:

String sql = "SELECT something FROM some_table WHERE this_thing = " + myVariable;
rs = st.executeQuery(sql);

is bad. It should be:

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.

[RamyaSivakanth]

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

[masijade]

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.

[Tolls]

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.

[RamyaSivakanth]

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.

[j2me64]

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.