Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

If Statement

Status
Not open for further replies.
Apr 18, 2002
185
US
I am attempting to modify someone else's code and I need to do the following...

If employee id is 611481, then use the EmpName field in ReportMenu for the employee name, not the LAST_NAME, FIRST_NAME fields in the EMPLOYEE table.

The code that already exists is:

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();
}


PLEASE help with if statement!
 
Idea 1: Use sql, and use UNION, and bind 2 queries like that:
Code:
SELECT a.empname, null, b.foo, c.bar
FROM a, b, c
WHERE b.emp = 612345
AND a.id = b.emp and b.bar = c.foo
UNION 
SELECT d.last_name, d.firsst_name, b.foo, c.bar
FROM d, b, c
WHERE b.id != 612345
AND d.id = b.emp and b.bar = c.foo
Idea 2: Select allways last_name, firsst_name, empname, and id, and decide later by ID, which to use.

don't visit my homepage:
 
Here is the second statement that pulls the employee table that if in this statement the emp id = then I want it to grab the EmpName from the first query

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.getString("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;


Does this make sense??

Sorry... that I didn't include it all
 
My thought is that I may need to create a function with the first "select" statement and then call that method with no conditions and then in the second "select" statement, call the method conditionally?

Am I on the right track?
 
Just a tip, replace String sQuery by a StringBuilder or StringBuffer.
 
What is the difference between sQuery and Stringbuilder/Stringbuffer?
 
Has better performance to concatenate strings, but I'd suggest to make it work first and then care about performance.

Cheers,
Dian
 
Thx for the explanation!

Now back to the original problem... since I am trying to do an if statement in the second statement that would reference the first if statement - is it best to put it in a method and then call it unconditionally the first time and then call it conditionally in the second statement?

If so, how do I go about that?
 
Stefanwagner:

Could you explain in depth a little more about your idea of using UNION?? The programmer ran the first select and then the other and I want to use all the information from the second select EXCEPT if the EMPLOYEE (id) = 12345 in the EMPLOYEE table. If EMPLOYEE (id) = 12345, then I want to grab EmpName from the ReportMenu table, otherwise I just want the last_name and first_name from the EMPLOYEE table.

So right now I am stuck and don't know which way to turn!! Please help!
 
You can use union to merge two select-statements, if they have the same number of columns and the type of the columns matches.

A simple example:
Code:
SELECT a, b from foo;
a    b
---- -------
1    foo
2    bar 

SELECT c, d from bar;
c    d
---- -------
1    foobar
3    barfoo

SELECT  a, b from foo 
UNION 
SELECT c, d from bar;
a    b
---- -------
1    foo
2    bar 
1    foobar
3    barfoo
c and d now show up as if they where a and b.

Using a where-clause you may exclude from foo if a=1 and exclude from bar if a!=1 - for example.

don't visit my homepage:
 
Something like that:
Code:
  SELECT last_name, first_name
    FROM EMPLOYEE
    WHRE id != 12345
UNION 
  SELECT EmpName, ''
    FROM ReportMenu
    WHERE id=12345;

You need two columns, hence '' in the second query too.
Code:
StringBuilder sb = new StringBuilder (200); 
sb.append ("SELECT last_name, first_name ");
sb.append ("  FROM EMPLOYEE ");
// and so on
sb.append ("  WHERE id=12345 "); 
return sb.toString ();
Use an estimated length for the new StringBuilder (ESTIMATED);


don't visit my homepage:
 
I know its outside of the scope of the question but concatenating string objects annoys me! Use StringBuffer or StringBuilder!

Also - if you're calling these methods regularly, perhaps you should be using Stored Procs and calling them through CallableStatements?

It'll be quicker and safer :)
 
...and modern Java compilers already convert the actual concatenation to act like a stringbuffer/stringbuilder sequence, at least with comparable speed.
 
Couldn't the article I had in mind (it's in the FF cache of my desktop, other location), but this a quick search:

my google search
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top