Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2009
    Posts
    46

    Unanswered: Can Someone Tell Me What is Wrong with My Logic?

    Hey guys,

    I have a salary table called T_MBR_HIST:


    SSN# MBR_SAL_AMT
    _____ ____________



    My goal is to retrieve an average of the employee's top 3 salary amounts in their history. I was able to use the FETCH and sort commands to return the top 3 salaries, but my logic fails when I try to average the results.

    Here is my query:

    SELECT AVG(A.MBR.SAL_AMT) FROM


    (SELECT MBR_SAL_AMT FROM
    DSNP.PR01_T_MBR_HIST
    WHERE mbr_SSN_NBR = 444554444
    ORDER BY MBR_SAL_AMT DESC
    FETCH FIRST 3 ROWS ONLY ) AS A



    It appears that DB2 doesn't like the order command inside my derived table. Any suggestions? Thanks!
    Last edited by rockdave35; 02-26-09 at 17:28.

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Use OLAP functions, something like "...avg(sal_amt) over (partition by emp_id order by sal_amt desc rows 2 following)..."

    Not tested - use your imagination.
    ---
    "It does not work" is not a valid problem statement.

  3. #3
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Which version of DB2 are you using on which platform? Older versions of DB2 LUW didn't support ORDER BY in a subselect - newer versions do.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  4. #4
    Join Date
    Feb 2009
    Posts
    9
    ya. I agree with Stolze. V9 Supports orer by in Subqueries too.

  5. #5
    Join Date
    May 2007
    Location
    somewhere in dbforums
    Posts
    221
    Quote Originally Posted by rockdave35
    It appears that DB2 doesn't like the order command inside my derived table. Any suggestions? Thanks!
    Mind posting what error you get in the exact terms as displayed by DB2....
    Is MBR_SAL_AMT numeric???
    IBM Certified Database Associate, DB2 9 for LUW

  6. #6
    Join Date
    Jan 2009
    Posts
    46
    Quote Originally Posted by nick.ncs
    Mind posting what error you get in the exact terms as displayed by DB2....
    Is MBR_SAL_AMT numeric???
    Here is the error:

    The use of the reserved word "ORDER" following "" is not valid.
    Expected tokens may include: ") UNION EXCEPT". SQLSTATE=42601

    SQL0199N The use of the reserved word "ORDER" following "" is not valid. Expected tokens may include: ") UNION EXCEPT


    I believe that I am on DB2 8, but not sure.

Posting Permissions

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