Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    Join Date
    Sep 2008
    Posts
    19

    Unanswered: db2 coalesce with concat columns replacing with null still shows special characters

    Hi,


    I am creating a view where in a column say colC definition is based on concat of two other columns that are separated by ';' ( v.colA||';'||v.colB ). The requirement is that if the value of the concat'd columns are null it should return null. In the above case it happens to return ';' even if the result is null. I tried using coalesce but still it's giving same error.

    coalesce(concat(concat(v.colA, ';'), v.colB),'')

    coalesce(v.colA||';'||v.colB, NULL)

    case when v.colA||v.colB is NULL then NULL else
    v.colA||';'||v.colB END,

    Nothing seems to replace ';' .

    Can any one help,

    Thanks

  2. #2
    Join Date
    Sep 2008
    Posts
    19
    Can anyone help?

  3. #3
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    All you should need is v.ColA||';'||v.ColB. If either V.ColA or V.ColB are null then the result is null.

    Andy

  4. #4
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    You can resolve with a simple CASE statement.
    Code:
    case when v.ColA is null
              and v.ColB is null
                then null
            else v.ColA||';'||v.ColB
    end
    Now, what do you want when just one of them is null? a couple more when conditions will resolve that.
    Dave

  5. #5
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Quote Originally Posted by gautambali View Post
    ...
    ...

    case when v.colA||v.colB is NULL then NULL else
    v.colA||';'||v.colB END,

    Nothing seems to replace ';' .
    That means "v.colA||v.colB was NOT NULL".
    Because "when v.colA||v.colB is NULL" was not true.

    Aren't colA and colB zero length string(i.e. '')?
    Someone suggested similar issue in another thread.

  6. #6
    Join Date
    Sep 2008
    Posts
    19
    Quote Originally Posted by tonkuma View Post
    That means "v.colA||v.colB was NOT NULL".
    Because "when v.colA||v.colB is NULL" was not true.

    Aren't colA and colB zero length string(i.e. '')?
    Someone suggested similar issue in another thread.


    It doesn't appear to be a zero length string.
    When i do a select from either of the columns it returns this. But don't see any data. So how can I fix this.
    --------------------------------------------------------------------------------------------------------------------------------------------------------
    1 record(s) selected.

  7. #7
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    What do you get when you execute "select length(v.colA),length(v.colB)"?

    Andy

  8. #8
    Join Date
    Sep 2008
    Posts
    19
    db2 "select length(cola),length(colb) from view1 where id=1234"

    1 2
    ----------- -----------
    0 0

    1 record(s) selected.


    It shows zero byte.

  9. #9
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Which is what we have suspected all along. An empty string (length 0), and a null string are not the same. If you want to treat them the same you can do this:

    nullif(v.ColA,'')||';'||nullif(V.Colb,'')

    Andy

  10. #10
    Join Date
    Sep 2008
    Posts
    19
    tried nullif(v.ColA,'')||';'||nullif(V.Colb,'')

    Still getting the same result

  11. #11
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Please post the data types of ColA and ColB.

    What is the output of this:

    ColA,ColB,length(ColA),Length(ColB),nullif(ColA,'' ),nullif(ColB)?

    Andy

  12. #12
    Join Date
    Sep 2008
    Posts
    19
    Select result
    ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

    0 0 - -


    First 2 columns are blank.

  13. #13
    Join Date
    Sep 2008
    Posts
    19
    Data type Column
    Column name schema Data type name Length Scale Nulls
    ------------------------------- --------- ------------------- ---------- ----- ------
    colA SYSIBM VARCHAR 2000 0 Yes
    colB SYSIBM VARCHAR 500 0 Yes

  14. #14
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Then what is the output of this: nullif(v.ColA,'')||';'||nullif(V.Colb,'')

    Andy

  15. #15
    Join Date
    Sep 2008
    Posts
    19
    Select nullif(v.ColA,'')||';'||nullif(V.Colb,'') output :

    -----------------------------------------------------------------------------------------------------------------------------------------------------


    1 record(s) selected.


    It's empty result.

Posting Permissions

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