Results 1 to 11 of 11

Thread: Sqlstate=1003

  1. #1
    Join Date
    Nov 2005
    Posts
    15

    Unanswered: Sqlstate=1003

    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

  2. #2
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    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?
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  3. #3
    Join Date
    Nov 2005
    Posts
    15
    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

  4. #4
    Join Date
    Nov 2005
    Posts
    15
    found it.

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

  5. #5
    Join Date
    May 2006
    Posts
    82
    I've used coalesces for each and every SUM operation
    Nuck, I believe you are same as the one who is @
    http://ibmmainframes.com/viewtopic.php?t=19469


    Why it behaves in the way it does is still a mystery.
    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.
    Vinay,

  6. #6
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Quote Originally Posted by nuck
    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.
    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.

    Does this sqlstate show up when there is a SUM(coalesce(col_a,0))??? that would make sense, sort of.
    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...
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  7. #7
    Join Date
    Nov 2005
    Posts
    15
    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.

  8. #8
    Join Date
    May 2006
    Posts
    82
    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.

    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).
    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.
    Vinay,

  9. #9
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Quote Originally Posted by nuck
    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.
    That's absolutely fine. I just hope that the checking of the SQLSTATE really considers that 01xxx are warnings and not errors.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  10. #10
    Join Date
    Nov 2005
    Posts
    15
    Quote Originally Posted by stolze
    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....

  11. #11
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Quote Originally Posted by nuck
    ...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.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

Posting Permissions

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