Results 1 to 7 of 7

Thread: Coalesce?

  1. #1
    Join Date
    Dec 2006
    Posts
    17

    Unanswered: Coalesce?

    Hi,

    I m trying to use coalesce but i m getting this error below, btw i m using db2 8.2.
    what i m doing is;
    ...
    where eh.toblm = coalesce(NULL,eh.toblm)

    SQL0206N "NULL " is not valid in the context where it is used.

    Actually what i want to do is, get the result of this select statement either @id is NOT NULL or NULL! if @id is NULL then all results should be displayed!
    ...
    where eh.toblm = coalesce(@id,eh.toblm)

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    I suggest you post the entire query along with the table definition. And what is "@id"?
    ---
    "It does not work" is not a valid problem statement.

  3. #3
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    The keyword NULL can only be used in a context where its data type is known.

    So, it's e.g. valid to use it with INSERT, as in
    Code:
    INSERT INTO mytable (col1, col2) VALUES ('value1', NULL)
    You may use it in any other context where a constant value is valid, but in that case only after an explicit type cast, e.g.
    Code:
    CAST(NULL as INT)
    or
    Code:
    CAST(NULL as CHAR(20))
    (or of course alternatively with the "old" casting methods INT(NULL) or CHAR(NULL,20)).

    Your condition
    Code:
    WHERE eh.toblm = coalesce(expr, eh.toblm)
    makes sense, and will indeed be a "no-op", i.e., match everything, when expr has the NULL value. But it should be of a datatype which is compatible with eh.toblm for that expression to be accepted by DB2, so plugging in NULL for expr won't work while e.g. CAST(NULL as INT) will work (provided eh.toblm is of a numeric type).
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  4. #4
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Peter,

    While you are right about the use of NULL, the problem of the original poster lies elsewhere. "where eh.toblm = coalesce(cast(NULL as whatever),eh.toblm)" is equivalent to "where eh.toblm = eh.toblm", which is equivalent to not having the WHERE clause at all.

    Apart from using the tool (COALESCE) improperly, he (or she) seems to be using the wrong tool for the task.
    ---
    "It does not work" is not a valid problem statement.

  5. #5
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    My understanding of the question was rather whether the condition
    Code:
    WHERE eh.toblm = coalesce(expr, eh.toblm)
    would indeed be "always true" in case expr is NULL. And to test this, raysefo tried to replace expr by NULL, which didn't work, hence his question.
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  6. #6
    Join Date
    Dec 2005
    Posts
    273
    " WHERE eh.toblm = coalesce(expr, eh.toblm)
    would indeed be "always true" in case expr is NULL "

    I disagree. If eh.toblm is <NULL> the result would be "unknown"

  7. #7
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Quote Originally Posted by umayer
    If eh.toblm is <NULL> the result would be "unknown"
    You're right. My mistake. Thanks!
    The following would include that case:
    Code:
    WHERE coalesce(eh.toblm,'XYXYX') = coalesce(expr, eh.toblm, 'XYXYX')
    where you must replace 'XYXYX' by some constant expression of the same datatype as eh.toblm and expr, but which never equals one of the possible values of those.
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

Posting Permissions

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