SqlSyntaxErrorException in DB2 query

Question

I am trying to execute the following db2 query, but I'm getting this error:

SqlSyntaxErrorException: DB2 SQL Error: SQLCODE=-119, SQLSTATE=42803, SQLERRMC=ENTITLEMENT

The query is:

SELECT *
FROM reclaimbalance rb
    ,user_benefit_accrued_period ubap
WHERE rb.user_id = ubap.user_id
    AND rb.component_id = ubap.PAY_HEAD_ID
    AND ubap.CUSTOMER_ID = 281
    AND rb.year = '2016-2017'
    AND ubap.STATUS = 1
GROUP BY ubap.user_id
    ,ubap.PAY_HEAD_ID
HAVING sum(ubap.STD_BALANCE_ADDED_IN_PERIOD) != rb.ENTITLEMENT

You should post your table schema, sample data and expected results. I suspect the issue is around the group by and having clauses but it's difficult to understand what your trying to do. — sgeddes
I wanna fetch the records from two table on the basis of one table record's sum is not equal to other — user1359
You cannot use SELECT * with GROUP BY; the select list must contain only the columns from the GROUP BY clause and aggregate functions. — mustaccio

Gordon Linoff Gordon Linoff · Accepted Answer · 2016-08-08T16:24:56

One problem is SELECT *. I would expect the error to be a bit different from just a generic syntax error, though.

Also, you should learn to use explicit JOIN syntax. And, your HAVING clause has an unaggregated column.

I assume you want something like this:

SELECT ubap.user_id, ubap.PAY_HEAD_ID, rb.ENTITLEMENT,
       sum(ubap.STD_BALANCE_ADDED_IN_PERIOD)
FROM reclaimbalance rb JOIN
     user_benefit_accrued_period ubap
      ON rb.user_id = ubap.user_id AND rb.component_id = ubap.PAY_HEAD_ID
WHERE ubap.CUSTOMER_ID = 281 AND rb.year = '2016-2017' AND ubap.STATUS = 1
GROUP BY ubap.user_id, ubap.PAY_HEAD_ID, rb.ENTITLEMENT
HAVING sum(ubap.STD_BALANCE_ADDED_IN_PERIOD) <> rb.ENTITLEMENT;

SqlSyntaxErrorException in DB2 query

2 Answers