rownum and group by to limit to 1000 rows in detail

Question

I need to limit to 1000 rows in detail table EL8_STGG_CEHL_EXCP for each unique combination keys(AST_ID,PRCS_ID,PRCS_NME,PRCS_STEP_NME,SRC_APPL_LOG_DT) in the EL8_STGG_CEHL_LOG.

For example - If there are 3 unique combination keys, I expect 3000 rows in the detail table.

SELECT 
       E.AST_ID, 
       E.PRCS_ID, 
       E.PRCS_NME, 
       E.PRCS_STEP_NME,
       E.SRC_APPL_LOG_DT
    FROM EL8_STGG_CEHL_EXCP  E,EL8_STGG_CEHL_LOG L 
    WHERE
       L.AST_ID  = E.AST_ID 
       AND L.PRCS_ID  = E.PRCS_ID 
       AND L.PRCS_NME  = E.PRCS_NME  
       AND L.PRCS_STEP_NME  = E.PRCS_STEP_NME  
       AND L.SRC_APPL_LOG_DT = E.SRC_APPL_LOG_DT        
       AND (L.CEHL_PICK_UP_IND IS NULL OR UPPER(L.CEHL_PICK_UP_IND) not in ('Y','P'))
    GROUP BY
       E.AST_ID, 
       E.PRCS_ID, 
       E.PRCS_NME, 
       E.PRCS_STEP_NME,
       E.SRC_APPL_LOG_DT

it would be great help if anyone can help me out

Thanks

Answer 1

Untested... that's a lot of data to mock up.

Basically, I use my favorite analytic function, row_number()

with limited as (
  select
    L.*,
    row_number() over (partition by AST_ID,PRCS_ID,PRCS_NME,
      PRCS_STEP_NME,SRC_APPL_LOG_DT order by rownum) as rn
  from EL8_STGG_CEHL_LOG L
)
SELECT 
   E.AST_ID, 
   E.PRCS_ID, 
   E.PRCS_NME, 
   E.PRCS_STEP_NME,
   E.SRC_APPL_LOG_DT
FROM
  EL8_STGG_CEHL_EXCP  E,
  limited L 
WHERE
  L.AST_ID  = E.AST_ID 
  AND L.PRCS_ID  = E.PRCS_ID 
  AND L.PRCS_NME  = E.PRCS_NME  
  AND L.PRCS_STEP_NME  = E.PRCS_STEP_NME  
  AND L.SRC_APPL_LOG_DT = E.SRC_APPL_LOG_DT        
  AND L.RN <= 1000
  AND (L.CEHL_PICK_UP_IND IS NULL OR UPPER(L.CEHL_PICK_UP_IND) not in ('Y','P'))