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

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

    David Jitendranath Novice

      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=? )