Hi All,

I many of our programs we populate a dropdown list with employee names. The employees selected are based on criteria selected by Or imposed on the user. Below is a procedure demonstrating the way I build the 'WHERE' clause. Could this be put into a Package where I pass (lnEmpHireNo, lnTypeNo, ldCurrDate)?

DO CASE
        * The user wants ALL employees
    CASE lnEmpHireNo == 1
        lcWhereString = " "

        * The user wants only ACTIVE employees
    CASE lnEmpHireNo == 2
        lcWhereString = "  AND (EMP.OUT_DT IS NULL OR EMP.OUT_DT >= TO_DATE('" + DTOC(ldCurrDate) + "', 'MM/DD/YYYY'))"

        * The user wants only FORMER employees
    CASE lnEmpHireNo == 3
        lcWhereString = "  AND (EMP.OUT_DT IS NOT NULL AND EMP.OUT_DT < TO_DATE('" + DTOC(ldCurrDate) + "', 'MM/DD/YYYY'))"

        * The user wants only Current & Former who worked in last 2 weeks.
    CASE lnEmpHireNo == 4
        lcWhereString = " AND (EMP.OUT_DT IS NULL OR EMP.OUT_DT > TO_DATE('" + DTOC(ldCurrDate) + "', 'MM/DD/YYYY') - 14) "
ENDCASE

DO CASE
        * The user wants Unionized Shop employees.
    CASE lnTypeNo == 1
        lcWhereString = lcWhereString + " AND EMP.HRS_BASE = 1 " + ;
                                        " AND EMP.UNION_EMP = 1 "
        * The user wants NON-Unionized Shop employees.
    CASE lnTypeNo == 2
        lcWhereString = lcWhereString + " AND EMP.HRS_BASE = 1 " + ;
                                        " AND EMP.UNION_EMP = 0 "
        * The user wants Office employees.
    CASE lnTypeNo == 3
        lcWhereString = lcWhereString + " AND EMP.HRS_BASE = 0 "

ENDCASE

lcSqlStatement = "SELECT RTRIM(PERS.LAST_NAME) || ', ' || RTRIM(PERS.FIRST_NAME) AS EMP_NAME, " + ;
                 "  PERS.PERS_NO " + ;
                 "FROM EMP, " + ;
                 "  PERS " + ;
                 "WHERE EMP.PERS_NO = PERS.PERS_NO " + lcWhereString + ;
                 "ORDER BY PERS.LAST_NAME, " + ;
                 "  PERS.FIRST_NAME"

TIA
Mike

P.S. VFP6 SP5 Oracle 8i