Rewrite an analytical query
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: Rewrite an analytical query

  1. #1
    Join Date
    Dec 2000
    Posts
    138

    Question Rewrite an analytical query

    I have a requrement in where I have been able to write the query using analytical query as the database is 9i, but unfortunately that wont run in reports 6i.

    Heres a sample table(not exact one tho) and the query. Can anyone suggest a alternate for this query in ver 8 (without analytics)

    The table emp_add_det
    PHP Code:
    SYS_ID CLIENT_ID EMP_ID ADDTP_CODE ADD_DETAIL
    ------ --------- ------ ---------- -----------------------------------
         
    1       100      1 O          100 X STLBCA 90001
         2       100      1 M          100 X ST
    LBCA 90001
         3       100      1 M          100 X ST
    LBCA 90001
         4       100      2 O          100 X ST
    LBCA 90001
         5       100      2 O          100 X ST
    LBCA 90001
         6       100      3 M          100 X ST
    LBCA 90001
         7       100      4 O          100 X ST
    LBCA 90001
         8       100      5 B          100 X ST
    LBCA 90001
         9       100      5 O          100 X ST
    LBCA 90001
        10       100      6 B          100 X ST
    LBCA 90001
        11       100      7 O          100 X ST
    LBCA 90001
        12       100      7 M          100 X ST
    LBCA 90001
        13       100      7 B          100 X ST
    LBCA 90001

    13 rows selected
    .

    Retrieve [B]only one row [/B] for each emp_id with addtp_code in O or and if there is a M for an emp_id the M should show else a row from O should show
    Also note that each emp_id can have multiple rows with addtp_code or O.

    the query using analytics is

    SELECT 
    *
      
    FROM (SELECT d.*,
                   
    MIN (sys_idOVER (PARTITION BY client_idemp_id ORDER BY addtp_code)
                                                                       
    min_sys_id,
                   
    MIN (addtp_codeOVER (PARTITION BY client_idemp_id ORDER BY addtp_code)
                                                                   
    min_addtp_code
              FROM EMP_ADD_DET d
             WHERE d
    .client_id 100 
                                          
    AND d.addtp_code IN ('M''O'))
     
    WHERE min_sys_id sys_id AND min_addtp_code addtp_code 
    The above query works fine. I couldnt find an alternate without the analytics. BTW any other analytic query faster than this is also appreciated for academics.


    Also Is there anyother way than the one below to get emp_ids who have ONLY addtp_code='O'.

    PHP Code:
    SELECT client_id,emp_id,
           
    SUM(DECODE(addtp_code,'O',0,1)),
                                
    SUM(DECODE(addtp_code,'O',1,0))
      
    FROM EMP_ADD_DET
        GROUP BY client_id
    ,emp_id
    HAVING SUM
    (DECODE(addtp_code,'O',0,1)) = 0
       
    AND SUM(DECODE(addtp_code,'O',1,0)) > 
    Any help or suggestions is appreciated.
    Thanks,
    -dharma

  2. #2
    Join Date
    Dec 2000
    Posts
    138
    Here is the way to go without analytics.
    Thanks to asktom.oracle.com

    Concatenate the columns and use substr to split them

    PHP Code:
    SELECT client_idemp_idSUBSTR(constr,1,1addtp_code,
           
    TO_NUMBER(SUBSTR(constr,2,6)) sys_idSUBSTR(constr,8addr 
      FROM 
    (SELECT client_id,emp_id,MIN(addtp_code||TO_CHAR(sys_id,'fm000009')||add_detail)     constr 
              FROM emp_add_det
             WHERE addtp_code  IN 
    ('M','O')                      
             
    GROUP BY client_id,emp_id
    -dharma

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width