5 Replies Latest reply: Feb 10, 2013 12:29 AM by shivasha RSS

    How to get the max(level)?

    shivasha
      Hi,
      The below query returns level and other selected columns.
      I need to get the max(level) 2nd column value in the below example.How to modify the query?

      Ex
      Level      max(level)     id
      1 5 101
      1 5 102
      1 5 103
      2 5 104
      2 5 105
      3 5 107
      4 5 120
      5 5 134
      5 5 280

      SELECT DISTINCT level lvl
      ,form_frms.emp_id
      ,form_frms.ing_emp_id
      ,form_frms.prve_id
      ,CASE
      WHEN (select div_dn
      from epm_prod epm
      where epm.emp_id= form_frms.ing_emp_id) ='Y' THEN DIV_DN

      WHEN NVL((select distinct 'N'
      from emp_pro_version prvv
      where prvv.is_py='Y' and
      prvv.IS_VERSION ='N' and
      prvv.emp_id=form_frms.ing_emp_id) ,'Y')='N'
      THEN 'Y'

      WHEN NVL((select distinct 'Y'
      from employee epm
      where emp.emp_id=form_frms.ing_emp_id
      and epm.status<>'NEW') ,'N') ='Y'
      THEN 'Y'
      ELSE 'N'
      END
      ELSE 'N'
      END UN_KNOWN_ID
      FROM (SELECT empvv1.prvv_id

      FROM emp_view_version empvv1
      WHERE empvv1.is_version = 'Y'
      ) form_frms
      START WITH form_frms.emp_id = :lv_emp_id
      CONNECT BY PRIOR form_frms.ing_emp_id = form_frms.emp_id
      ORDER BY level DESC, form_frms.emp_id,UNKNOWN_ID ASC;

      Edited by: shivasha on Feb 9, 2013 12:24 AM
        • 1. Re: How to get the max(level)?
          Etbin
          Maybe
          select lvl,
                 max(lvl) over (order by null rows between unbounded preceding and unbounded following) max_level,
                 emp_id,
                 unknown_id
            from (SELECT DISTINCT
                         level lvl,
                         form_frms.emp_id,
                         form_frms.ing_emp_id,
                         form_frms.prve_id, 
                         CASE WHEN (select div_dn
                                      from epm_prod epm
                                     where epm.emp_id = form_frms.ing_emp_id
                                   ) = 'Y'
                              THEN DIV_DN
                              WHEN NVL((select distinct 'N'
                                          from emp_pro_version prvv
                                         where prvv.is_py = 'Y' 
                                           and prvv.IS_VERSION = 'N'
                                           and prvv.emp_id=form_frms.ing_emp_id
                                       ),'Y'
                                      ) = 'N'
                              THEN 'Y'
                              WHEN NVL((select distinct 'Y' 
                                          from employee epm
                                         where emp.emp_id = form_frms.ing_emp_id
                                           and epm.status != 'NEW'
                                       ),'N'
                                      ) = 'Y'
                              THEN 'Y' 
                              ELSE 'N'
                         END UNKNOWN_ID 
                    FROM (SELECT empvv1.prvv_id
                            FROM emp_view_version empvv1
                           WHERE empvv1.is_version = 'Y'
                         ) form_frms
                   START WITH form_frms.emp_id = :lv_emp_id
                   CONNECT BY PRIOR form_frms.ing_emp_id = form_frms.emp_id
                 )
           ORDER BY lvl DESC,emp_id,UNKNOWN_ID
          Regards

          Etbin
          • 2. Re: How to get the max(level)?
            Frank Kulash
            Hi,
            Etbin wrote:
            Maybe
            select lvl,
            max(lvl) over (order by null rows between unbounded preceding and unbounded following) max_level, ...
            That last line is the same as
            MAX (lvl) OVER ()    AS max_level,
            MAX doesn't require an ORDER BY clause.
            • 3. Re: How to get the max(level)?
              Etbin
              Thanks for pointing that out I somehow loose it wanting to be as illustrative as possible :(
              Indeed a way too exaggerated use of analytic function clauses :(
              Maybe a good example how not to do things for the OP ;)

              Regards

              Etbin
              • 4. Re: How to get the max(level)?
                shivasha
                Thanks for the help.. It is working
                • 5. Re: How to get the max(level)?
                  shivasha
                  Thanks for the help.. It is working