Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2011
    Posts
    85

    Unanswered: Invalid use of an aggregate function or OLAP function

    Hi All

    I'm trying a query to get some data

    select CASE WHEN T3.PACKMANIFESTQTY = 0 OR T3.PACKMANIFESTQTY = NULL THEN ((SUM(ABS(T4.QUANTITY-T3.INVOICEDQTY)))/ SUM(T4.QUANTITY)) ELSE ((SUM(ABS(T4.QUANTITY-T3.PACKMANIFESTQTY))) / SUM(T4.QUANTITY)) END AS Formula
    FROM ORDERITEMFACT T3,LINEITEMFLATVIEW T4
    where T3.lineItemKey = T4.lineItemKey
    group by CASE WHEN T3.PACKMANIFESTQTY = 0 OR T3.PACKMANIFESTQTY = NULL THEN ((SUM(ABS(T4.QUANTITY-T3.INVOICEDQTY)))/ SUM(T4.QUANTITY)) ELSE ((SUM(ABS(T4.QUANTITY-T3.PACKMANIFESTQTY))) / SUM(T4.QUANTITY)) END

    when i execute this i get
    Invalid use of an aggregate function or OLAP function.. SQLCODE=-120, SQLSTATE=42903, DRIVER=3.61.75

    without the group by

    An expression starting with "PACKMANIFESTQTY" specified in a SELECT clause, HAVING clause, or ORDER BY clause is not specified in the GROUP BY clause or it i etc... SQLCODE=-119, SQLSTATE=42803, DRIVER=3.61.75 

    Please help me to find the issue here

    Im using DB2 10.5 Fixpack 2

  2. #2
    Join Date
    Jul 2013
    Location
    Moscow, Russia
    Posts
    666
    Provided Answers: 55
    Hi,

    try this:
    Code:
    select 
      SUM(ABS(T4.QUANTITY - CASE WHEN T3.PACKMANIFESTQTY = 0 OR T3.PACKMANIFESTQTY IS NULL THEN T3.INVOICEDQTY ELSE T3.PACKMANIFESTQTY END)) 
    / SUM(T4.QUANTITY) AS Formula
    FROM ORDERITEMFACT T3,LINEITEMFLATVIEW T4
    where T3.lineItemKey = T4.lineItemKey
    Regards,
    Mark.

  3. #3
    Join Date
    Sep 2011
    Posts
    85
    Hi Mark

    Thanx for this..
    It's working for me...

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Code:
    CASE WHEN T3.PACKMANIFESTQTY = 0 OR T3.PACKMANIFESTQTY IS NULL
    THEN T3.INVOICEDQTY ELSE T3.PACKMANIFESTQTY END
    may be equivalent to
    Code:
    COALESCE( NULLIF(T3.PACKMANIFESTQTY , 0) , T3.INVOICEDQTY )

Posting Permissions

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