2 Replies Latest reply on Jul 16, 2010 1:35 PM by lrpieri

Problem with EL restrictions for 'is not null' and 'in' SQL clause

davestar Aug 25, 2009 10:25 PM

Hi, I have a query that gets employee list and I've got the query restrictions added as String array. When I look at the SQL generated on the console the query ignores the last two restrictions (userDefinedCol3 and sourceInd). Can you please tell me how I can modify these restrictions to include all the supplied query criteria? For those two restrictions, I've tried all other possiblities of having them within quotes or brackets I either get a IllegalArgumentException or a QuerySyntaxException: unexpected token: exception.

Here are my restrictions

private static final String[] INITIAL_RESTRICTIONS = {         
                "employee.id.dept = #{'IN'}",
                "employee.id.bureauOrOffice = #{'07'}",
                "employee.userDefinedCol3 is not #{null}",
                "employee.sourceInd.sourceInd in #{F}",};

    select
        * 
    from
        ( select
            employee0_.BUREAU_OR_OFFICE as BUREAU1_4877_,
            employee0_.DEPT as DEPT4877_,
            employee0_.SOCIAL_SECURITY_NUMBER as SOCIAL3_4877_,
            employee0_.SUBBUREAU_CODE as SUBBUREAU4_4877_,
            employee0_.ALTERNATIVE_WORK_SCHEDULE_CODE as ALTERNA36_4877_,
            employee0_.BASE_RATE as BASE5_4877_,
            employee0_.BUS_CODE as BUS6_4877_,
            employee0_.CURRENT_RECORD_IND as CURRENT7_4877_,
            employee0_.DISABLE_EDITS_IND as DISABLE8_4877_,
            employee0_.EMP_NAME_FIRST as EMP9_4877_,
            employee0_.EMP_NAME_LAST as EMP10_4877_,
            employee0_.EMP_NAME_MIDDLE as EMP11_4877_,
            employee0_.EMP_NAME_SFX as EMP12_4877_,
            employee0_.EMP_NAME_WHOLE as EMP13_4877_,
            employee0_.EMP_FLSA_CATEGORY as EMP37_4877_,
            employee0_.EMP_STATUS_CODE as EMP38_4877_,
            employee0_.EMP_WORK_SCHEDULE as EMP39_4877_,
            employee0_.FIRE_FIGHTER_EDIT_CHECK as FIRE14_4877_,
            employee0_.GRADE as GRADE4877_,
            employee0_.HRS_SCHED_WK1 as HRS16_4877_,
            employee0_.HRS_SCHED_WK2 as HRS17_4877_,
            employee0_.LOAD_DATE as LOAD18_4877_,
            employee0_.LOCATION_CODE as LOCATION19_4877_,
            employee0_.MNS_SCHED_WK1 as MNS20_4877_,
            employee0_.MNS_SCHED_WK2 as MNS21_4877_,
            employee0_.OCCUP_SERIES as OCCUP22_4877_,
            employee0_.ORACLE_USERNAME as ORACLE23_4877_,
            employee0_.ORG_CODE_PERSONNEL as ORG24_4877_,
            employee0_.PAY_BASIS as PAY25_4877_,
            employee0_.PAY_PLAN as PAY40_4877_,
            employee0_.PSN_TITLE_CODE as PSN26_4877_,
            employee0_.PSN_TITLE_OPM as PSN27_4877_,
            employee0_.ROTATING_SHIFT_IND as ROTATING28_4877_,
            employee0_.SIG_GROUP_ID as SIG29_4877_,
            employee0_.SOURCE_IND as SOURCE41_4877_,
            employee0_.STEP as STEP4877_,
            employee0_.TAAS_STATUS as TAAS31_4877_,
            employee0_.TMK_GROUP_ID as TMK32_4877_,
            employee0_.USER_DEFINED_COL1 as USER33_4877_,
            employee0_.USER_DEFINED_COL2 as USER34_4877_,
            employee0_.USER_DEFINED_COL3 as USER35_4877_ 
        from
            TIDB.EMPLOYEE employee0_ 
        where
            employee0_.DEPT=? 
            and employee0_.BUREAU_OR_OFFICE=? )

1. Re: Problem with EL restrictions for 'is not null' and 'in' SQL clause

davestar Aug 26, 2009 12:35 AM (in response to davestar)

Alright, I found out how I can get the restrictions to work, still can't get the 'is not null' restriction to work. It is strange that the restrictions require atleast one EL in each restriction.
I would still appreciate any help on getting 'is not null' to work

Here's what works

     private static final String[] INITIAL_RESTRICTIONS = {          
          "employee.id.dept = #{'IN'}",
          "employee.id.bureauOrOffice = #{'07'}",
          "lower(employee.empNameFirst) like concat(lower(#{employeeList.employee.empNameFirst}),'%')",
          "lower(employee.empNameLast) like concat(lower(#{employeeList.employee.empNameLast}),'%')",
          "employee.sourceInd.sourceInd in (#{'P'}, 'F')",
          "employee.employeeStatus.empStatusCode in (#{'A'},'C', 'F')",};

2. Re: Problem with EL restrictions for 'is not null' and 'in' SQL clause

lrpieri Jul 16, 2010 1:35 PM (in response to davestar)

Hi David,

Did you find how to make 'is not null' works?
Actions

Go to original post