Results 1 to 10 of 10
  1. #1
    Join Date
    Jun 2011
    Posts
    13

    Unanswered: Analytic Function error

    We have a DB2 version 9.5 database and I am trying to write an analytic function to do a running total count:

    select {field1},{field2}, count(*) over(partition by ) as CNT
    from database.table
    where date({field2}) = '2010-02-01'

    I get an error: SQL0104N An unexpected token "(" was found following ""...SQLSTATE=42601.

    I dont know why an error is occuring here, its a simple analytic funtion, please advise if any thoughts on this. Thanks!.

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    partition by what?

  3. #3
    Join Date
    Jun 2011
    Posts
    13
    partition by {field2}

  4. #4
    Join Date
    Aug 2011
    Location
    Glasgow, UK
    Posts
    36
    Not sure understand your problem correctly. Do you not just need a "GROUP BY":

    select field1,field2, count(1) total
    from tablex
    where field2 = '2010-02-01'
    GROUP BY field1,field2

  5. #5
    Join Date
    Jun 2011
    Posts
    13

    Question

    Ok. Does anybody know how to write an analytic function in DB2? What I am trying to get is a running total,

    select {field1},{field2}, count(*) over(partition by {field2}) as CNT
    from database.table
    where date({field2}) = '2010-02-01'


    The above query is not working...can someone who has a DB2 databse test a query similar to the above and see if it runs or not? I want to see why its not working. Thanks.

  6. #6
    Join Date
    Aug 2011
    Location
    Glasgow, UK
    Posts
    36
    Quote Originally Posted by teel View Post
    Ok. Does anybody know how to write an analytic function in DB2? What I am trying to get is a running total,

    select {field1},{field2}, count(*) over(partition by {field2}) as CNT
    from database.table
    where date({field2}) = '2010-02-01'


    The above query is not working...can someone who has a DB2 databse test a query similar to the above and see if it runs or not? I want to see why its not working. Thanks.
    Do you have any sample data (a few rows is fine) for field1 and field2?

    Additionally, and more importantly, what do you expect to see after your query has been run (assuming it was working).

    THIS should establish your exact requirements.


  7. #7
    Join Date
    Jun 2011
    Posts
    13
    Ok. Here is some sample data, but this time i will use a date range in the SQL.

    field1

    1. 088213
    2. 05849
    3. 23289
    4. 89890
    5. 283092
    6. 236922


    field2

    1. 2011-01-02
    2 2011-01-15
    3. 2011-02-20
    4. 2011-04-14
    5. 2011-05-23
    6. 2011-05-30

    I want to see a total count of field1 within the date range.

    select {field1},{field2}, count(*) over(partition by {field2}) as CNT
    from database.table
    where date({field2}) >= '2010-01-01' and
    date{field2} <= '2011-05-30'


    field 1 field2 CNT

    1. 088213 2011-01-02 1
    2. 05849 2011-01-15 1
    3. 23289 2011-02-20 1
    4. 89890 2011-04-14 1
    5. 283092 2011-05-23 1
    6. 236922 2011-05-30 1

    Depending on what the result wille be, the CNT result is just an assumption, but I just want to see the Analytic function work in DB2


    Thanks.

  8. #8
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    field1

    1. 088213
    2. 05849
    3. 23289
    4. 89890
    5. 283092
    6. 236922


    field2

    1. 2011-01-02
    2 2011-01-15
    3. 2011-02-20
    4. 2011-04-14
    5. 2011-05-23
    6. 2011-05-30
    This is a bad sample data(or test data).
    Because there were no duplication in field2, so the clause "partition by {field2}" would be no use.

    Another issue is to provide the values of each columns separately.
    Usually, the data should provide row by row like you showed in expected result.


    I just want to see the Analytic function work in DB2
    You can find many examples in manuals, in this forum, so on...

    Search them with the keyword "OLAP specifications".

  9. #9
    Join Date
    Aug 2011
    Location
    Glasgow, UK
    Posts
    36
    How about this:
    Using DB2 UDB OLAP functions

    Good article with examples.

  10. #10
    Join Date
    Aug 2011
    Location
    Glasgow, UK
    Posts
    36
    Additionally, here's an example that uses the SAMPLE database:
    Code:
    -- test_olap_fx.sql
    -- To Execute USE:
    -- db2 -td@ -vf test_olap_fx.sql
    
    DB2 CONNECT TO SAMPLE
    @
    
    SELECT WORKDEPT, SALARY,
    SUM(SALARY) OVER (PARTITION BY WORKDEPT) AS DEPTSUM,
    AVG(SALARY) OVER (PARTITION BY WORKDEPT) AS AVGSAL,
    COUNT(*) OVER (PARTITION BY WORKDEPT) AS DEPTCOUNT,
    MAX(SALARY) OVER (PARTITION BY WORKDEPT) AS MAXSAL
    FROM EMP
    @
    Taken from:
    Meet the Experts: Bob Lyle Talks About OLAP Functions in DB2 for UNIX, Windows, and OS/2

    Also:
    Code:
    SELECT JOB, HIREDATE, EMPNO, LASTNAME, FIRSTNME, SALARY,
           FIRST_VALUE(SALARY) OVER (PARTITION BY JOB
           ORDER BY HIREDATE) AS FIRST_SALARY,
           SALARY - FIRST_VALUE(SALARY) OVER (PARTITION BY JOB
           ORDER BY HIREDATE) AS DELTA_SALARY
    FROM EMPLOYEE
    ORDER BY JOB, HIREDATE
    From: http://publib.boulder.ibm.com/infoce.../r0023461.html

    Enjoy!

    Marc

    Last edited by marc_; 08-18-11 at 08:09. Reason: More info added

Posting Permissions

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