// Define the query
DBCommand cmd = db.createCommand();
// Select required columns
cmd.select(T_EMP.C_EMPLOYEE_ID);
cmd.select(T_DEP.C_DEPARTMENT_ID);
cmd.select(T_EMP.C_LASTNAME.append(", ")
.append(T_EMP.C_FIRSTNAME.coalesce(DBDatabase.EMPTY_STRING))
.append(" (").append(T_DEP.C_NAME).append(")"));
// Set Joins
cmd.join(T_EDH.C_EMPLOYEE_ID, V_EDS.C_EMPLOYEE_ID)
.where(T_EDH.C_DATE_FROM.is(V_EDS.C_MAX_DATE_FROM));