Db2 Sql Error Sqlcode=-120 Sqlstate=42903
Contents |
that is sourced on an aggregate function is not db2 sql error sqlcode 551 sqlstate 42501 permitted in a SET clause, VALUES clause, SET host-variable statement, SET transition-variable statement, assignment statement, or http://www.ibm.com/support/knowledgecenter/SSEPGG_9.5.0/com.ibm.db2.luw.messages.sql.doc/doc/msql00120n.html as the expression of a RETURN statement. An aggregate function or a user-defined function that is sourced on an aggregate function is allowed in a WHERE clause only if the WHERE http://www.sqlerror.de/db2_sql_error_-120_sqlstate_42903.html clause appears within a subquery of a HAVING clause. System action: The statement cannot be executed. Programmer response: The implied function is not supported by DB2. Refer to Chapter 4 of DB2 SQL Reference for information about restrictions on operands that can be specified within a WHERE clause, SET clause, VALUES clause, SET host-variable statement, SET transition-variable statement, assignment statement, or as the expression of a RETURN statement. <- BACK TO INDEX This is a free Service. I assume no warranty for misprints, and for the correctness and completeness of information and entries.
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and policies of this site http://stackoverflow.com/questions/4092724/sql-problem-with-aggregate-functions-in-where-clause About Us Learn more about Stack Overflow the company Business Learn more about hiring developers or posting ads with us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow is a community of 4.7 million programmers, just like you, helping each other. Join them; it only takes a minute: Sign up SQL problem with aggregate functions db2 sql in where clause up vote 4 down vote favorite I am working on this SQL problem: Show any purchase orders whose charged amount and actual amount are different. Show this by displaying the purchase order number, the POAmount for each purchase order, the actual amount (calculated by adding the prices of all items in the order), and the difference between the two. Sort the results db2 sql error to show those with the largest differences first. I am getting the following code when running the sql statement below: Error code -1, SQL state 42903: Invalid use of an aggregate function. select purchaseorder.ponum, purchaseorder.amount, sum(poitems.quantity*poitems.unitprice), purchaseorder.amount-sum(poitems.quantity*poitems.unitprice) from purchaseorder, poitems where purchaseorder.ponum = poitems.ponum and purchaseorder.amount!=sum(poitems.quantity*poitems.unitprice) group by purchaseorder.ponum, purchaseorder.amount I think it's because I'm using an aggregate function in my where clause. How can I remedy this problem??? Thanks, sql db2 aggregate-functions share|improve this question edited Nov 4 '10 at 0:24 OMG Ponies 198k36356415 asked Nov 3 '10 at 23:52 novicePrgrmr 3,717215488 add a comment| 4 Answers 4 active oldest votes up vote 5 down vote Try this: select purchaseorder.ponum, purchaseorder.amount, sum(poitems.quantity*poitems.unitprice), purchaseorder.amount-sum(poitems.quantity*poitems.unitprice) from purchaseorder, poitems where purchaseorder.ponum = poitems.ponum group by purchaseorder.ponum, purchaseorder.amount having purchaseorder.amount!=sum(poitems.quantity*poitems.unitprice) share|improve this answer edited Nov 3 '10 at 23:58 John Hartsock 50.4k1694125 answered Nov 3 '10 at 23:55 Jeff Hornby 6,57322140 That doesn't do the ordering he requested - with highest diff first ;) –tobyodavies Nov 4 '10 at 0:01 add a comment| up vote 1 down vote It looks like you should use the HAVING clause instead: SELECT ... FROM purchaseorder JOIN poitems ON (purchaseorder.po