SQL & PL/SQL

Hello experts and sql pros!

I guess I need your help on a problem that I've been struggling with for hours now.

I have the following query, which in the final version will be written as pl/sql code:

SELECT
     CNT_GEBIET,
     CNT_GEBIET DISPLAY,
     CNT_GEBIET BUTTON,
     STR_NAME,
     decode(STR_ART_GEBIETE,'Vogelschutz','SPA','FFH','FFH','FFH und Vogelschutz','FFH & SPA') STR_ART_GEBIETE,
     STR_REG_KART_TEAMS,
     STR_BESCHREIBUNG,
     BOOL_ANZEIGEN,
     STR_FEDERFUEHRENDE,
     K.INT_VERFAHREN,
     STR_VERFAHREN,
     D.ST_BIOGEO,
     D.STR_GEBIET,
     E.STR_BIOGEO,
     E.INT_BIOGEO,
case when to_char(min(L.DATE_GP_RT_END), 'YYYY') < to_char(sysdate, 'YYYY')  then 'red'
else 'green' end as mxcolour,
case when to_char(min(L.DATE_R_AT_END), 'YYYY') <= to_char(sysdate, 'YYYY') then 'green'
when to_char(min(L.DATE_R_V_END), 'YYYY') > to_char(min(L.DATE_GP_V_END), 'YYYY') then 'red'
else 'black' end as mxcolour_r_date,
   to_char(min(L.DATE_GP_AT_START), 'YYYY')  DATE_GP_AT_START,
   to_char(min(L.DATE_R_AT_END), 'YYYY')  DATE_R_AT_END,
   to_char(min(L.DATE_GP_V_START), 'YYYY')  DATE_GP_V_START,
   to_char(min(L.DATE_R_V_END), 'YYYY')  DATE_R_V_END,
   to_char(min(L.DATE_GP_RT_START), 'YYYY')  DATE_GP_RT_START,
   to_char(min(L.DATE_R_RT_END), 'YYYY')  DATE_R_RT_END,
   I.GEB,
   I.STR_REGBEZE,
   H.ALF,
   I.LNG_REG_KART_TEAMS,

   MAX(NVL(M.LNG_ARBEITSSCHRITT,21)) LNG_ARBEITSSCHRITT

FROM VT_TBL_ARBEIT_ZU_GEBIET M
RIGHT OUTER JOIN (
  VT_TBL_GEBIET K
  INNER JOIN VT_TBL_ART_GEBIETE B ON B.CNT_ART_GEBIETE = K.INT_ART_GEBIET
  INNER JOIN VT_TBL_FEDERFUEHRENDE C ON C.CNT_FEDERFUEHRENDE = K.LNG_FEDER
  INNER JOIN VT_TBL_GEBIET_ZU_BIOGEO D ON D.STR_GEBIET = K.CNT_GEBIET
  INNER JOIN VT_TBL_BIOGEO E ON D.ST_BIOGEO = E.INT_BIOGEO
  INNER JOIN VT_TBL_VERFAHREN F ON F.CNT_VERFAHREN = K.INT_VERFAHREN
  INNER JOIN VT_TBL_REG_KART_TEAMS G ON G.CNT_REG_KART_TEAMS = K.INT_REG_KART_TEAMS
  INNER JOIN VT_TBL_GEBIET_ZU_ALF H ON H.GEBIET = K.CNT_GEBIET
  INNER JOIN TBL_REGBEZE I ON I.GEB = K.CNT_GEBIET
      AND I.LNG_REG_KART_TEAMS = G.CNT_REG_KART_TEAMS
) ON M.LNG_GEBIET = K.CNT_GEBIET
LEFT OUTER JOIN TBL_ARBEITSSCHRITTE_NEU A
  ON M.LNG_ARBEITSSCHRITT = A.CNT_ARBEITSSCHRITT
left outer join vt_tbl_arbeit_crit_date l on l.lng_gebiet = k.cnt_gebiet
   where k.cnt_gebiet not like '%Test%' and nvl(m.lng_arbeitsschritt,21) not in (1,4,13,23,28,31,45,54,60,66,73,78) and nvl(l.lng_arbeitsschritt,1) = 1
GROUP BY
    CNT_GEBIET,
    STR_NAME,
    STR_ART_GEBIETE,
    STR_REG_KART_TEAMS,
    LNG_REG_KART_TEAMS,
    H.ALF,
    STR_BESCHREIBUNG,
    BOOL_ANZEIGEN,
    STR_FEDERFUEHRENDE,
    D.ST_BIOGEO,
    D.STR_GEBIET,
    E.STR_BIOGEO,
    E.INT_BIOGEO,
    F.STR_VERFAHREN,
    K.INT_VERFAHREN,
    I.GEB,
    I.STR_REGBEZE,
    L.DATE_GP_AT_START,
    L.DATE_R_AT_END,
    L.DATE_GP_V_START,
    L.DATE_R_V_END,
    L.DATE_GP_RT_START,
    L.DATE_R_RT_END

My problem is that I want to select MAX(M.LNG_ARBEITSSCHRITT) while the max value is supposed to be limited to a specific range.

In fact, the values 1,4,13,23,28,31,45,46,54,60,66,73,78 should not be taken into consideration.

Can you please show me how to specify a range of values that can be used for the computation of max(m.lng_arbeitsschritt)?

I Appreciate your help and input!

Thanks for having a look at this post!

Regards,

Seb