If Statement

[Steffi1013]

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!

 

[Diancecht]

What did you try so far?

Cheers,
Dian

 

[stefanwagner]

Idea 1: Use sql, and use UNION, and bind 2 queries like that:

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:

http://home.arcor.de/hirnstrom/minis/index.html

 

[Steffi1013]

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

 

[Steffi1013]

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?

 

[tom62]

Just a tip, replace String sQuery by a StringBuilder or StringBuffer.

 

[Steffi1013]

What is the difference between sQuery and Stringbuilder/Stringbuffer?

 

[Diancecht]

Has better performance to concatenate strings, but I'd suggest to make it work first and then care about performance.

Cheers,
Dian

 

[Steffi1013]

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?

 

[Steffi1013]

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!

 

[stefanwagner]

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:

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:

http://home.arcor.de/hirnstrom/minis/index.html

 

[Steffi1013]

So how would I convey that in an sQuery or Stringbuilder??

 

[stefanwagner]

Something like that:

  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.

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:

http://home.arcor.de/hirnstrom/minis/index.html

 

[chris921]

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

 

[stefanwagner]

It's out of scope, it's two months late, it's already corrected, and how often would you expect the String to be concatenated?

don't visit my homepage:

http://home.arcor.de/hirnstrom/minis/index.html

 

[TonHu]

...and modern Java compilers already convert the actual concatenation to act like a stringbuffer/stringbuilder sequence, at least with comparable speed.

 

[Diancecht]

Tonhu, are you sure about that? Do you have any link?

Cheers,
Dian

 

[TonHu]

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