Hi all,

I get an SQLSTATE of 1003 on one of my imbedded sqls....I've done the development and testing in QMF, which doesn't seem to provide any sqlstate info. Or does it???

anyways, the text behind 1003 is

Null values were eliminated from the argument of an aggregate function.

great. I've used coalesces for each and every SUM operation (which is the only aggregate function that I'm using). To test where this could be coming from, I removed all the SUM stuff and the group by to get a result set that's not summed up. I thought I could then find where the null value is. However there were no null values. huh?

Yes, I realize that sqlstate 1003 is 'only' a warning, but I want my sql to complete with an sqlcode of 0 and an sqlstate of 0.

any advice? Thanks in advance

1003 is not a valid SQLSTATE. I guess you mean 01003? (SQLSTATE is a string comprised of 5 characters, not a number.)

Do you get an error message from DB2 or just the SQLSTATE? The specific error message would be much more meaningful in your situation.

Finally, let us know the answers to the usual questions: what's your platform, DB2 version, and what is the exact failing statement?

hi stolze,

ooops, yup it's actually 01003.

Platform z/os DB2 v8, and the only useful thing that came back in the sqlca was the sqlstate.

the offending sql is unfortunately too large to post here (I get a message that more than 10000 characters per post is too much )

Does this sqlstate show up when there is a SUM(coalesce(col_a,0))??? that would make sense, sort of.

Ralph

found it.

Why it behaves in the way it does is still a mystery.

Nuck, I believe you are same as the one who is @
http://ibmmainframes.com/viewtopic.php?t=19469


DB2 is designed in that way to react with null values. Because NULL value is an unknown thing and it cant be considered for most of the business calculations. I dont know how it happens in other RDBMS.

You cannot find a solution for a problem without knowing the problem..right..?

People says NULL is unknown,anything,infinite,everything and nothing.

I see your query passes the NULL value to the mathamatical column fucntion (MAX in your case ) which inturn forces DB2 to ingore that, thats the reason it throughs you the warning message.

Any SQLSTATE beginning with 01 is a warning. So you are right in that there is no non-zero SQLCODE associated with that. If you could post the complete hex-encoding of the SQLCA, we can surely figure out some more information.

The COALESCE doesn't make much sense there. It may supress the warning, but you would only slow down the query unnecessarily. You could use COALESCE(SUM(col_a), 0) if you want to have 0 (zero) instead of NULL output.

Why do you actually worry about this warning?

You can also try to reduce the statement to a few lines which will still raise the 01003, while still be comprehensible for others...

good morning,

it looks like 01003 is something that I'll just have to live with.

From my perspective, it's caused by a correlated subselect which has no result (and that is completely ok). I was trying to find the maximal timestamp less than a given timestamp for a key value, and there was no entry on the table for that key (which is ok).

Up until now, I've only ever checked the SQLCODE after a select operation, and when it was not ok, then I'd look at the SQLSTATE. At my current site, they always check BOTH. Different sites, different standards.

ok, thanks for the infos.

Try this query against the QMF sample tables.

SELECT MAX(SALARY) FROM Q.STAFF
WHERE ID = 399 Please make sure there is no row for id 399.

Your result will be like
COL1
---------
-

It is nothing but null.

So it is understood that you were using MAX function in the subquery. Since there was no entry for that query, your MAX function result was NULL.

Hope this should help you to some extent. Can't say anything unless i dig into the query.

That's absolutely fine. I just hope that the checking of the SQLSTATE really considers that 01xxx are warnings and not errors.

...yeah, that's where the problem was - the installation here assumes that a non-zero SQLSTATE means that no result set is returned. I'm (unfortunately) forced into using a COBOL generator (SWT01 - yuck!) which includes it's own sql checks (and for whatever reason it's more interested in SQLSTATE than SQLCODE - go figure :/)
This basically resulted in a subprogram returning a 'no data' status instead of just returning the result set and not complaining. But at least I learned something from this....

I guess someone read the SQL standard where SQLSTATEs are the primary means to pass error information to the application. Unfortunately, the same person hasn't read any details on the semantics of the various classes (first 2 characters) of SQLSTATEs. (The last 3 characters are the subclass.)

My recommendation would be to fix your error handling routine.