Results 1 to 12 of 12
  1. #1
    Join Date
    Mar 2012
    Posts
    27

    Unanswered: How to combine two columns into one column?

    Hello,

    I am new to DB2, and currently working with a project that uses DB2. I do not know what version of DB2 we use here, but I do know the server is about two years old if that helps.

    I have a fairly easy question regarding SQL syntax. With experience in T-SQL can someone help me or let me know if there is a equivalent statement to this select statement that combines two columns into one column as the result set.

    Example:

    SELECT FIRST_NAME + ' ' + LAST_NAME As FULL_NAME FROM CUSTOMERS

    When I apply this select statement I get this error message:

    ERROR [HY000] [IBM][System i Access ODBC Driver][DB2 for i5/OS]SQL0402 - + use not valid

    I'm guessing that the syntax "+ ' ' +" is the problem here, but I could be wrong.


    Thanks

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Quote Originally Posted by MrDatabase View Post
    Hello,

    I am new to DB2, and currently working with a project that uses DB2. I do not know what version of DB2 we use here, but I do know the server is about two years old if that helps.

    I have a fairly easy question regarding SQL syntax. With experience in T-SQL can someone help me or let me know if there is a equivalent statement to this select statement that combines two columns into one column as the result set.

    Example:

    SELECT FIRST_NAME + ' ' + LAST_NAME As FULL_NAME FROM CUSTOMERS

    When I apply this select statement I get this error message:

    ERROR [HY000] [IBM][System i Access ODBC Driver][DB2 for i5/OS]SQL0402 - + use not valid

    I'm guessing that the syntax "+ ' ' +" is the problem here, but I could be wrong.


    Thanks
    You need to use || to concatenate strings.

    Andy

  3. #3
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    For example:

    Select rtrim(FIRST_NAME)||' '||LAST_NAME from .......
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  4. #4
    Join Date
    Mar 2012
    Posts
    27

    How to combine two columns into one column?

    Thanks guys for answering me back, but let me make sure I have this right.

    Which one of these statements will be the right way.

    SELECT Full_Name(First_Name || ' ' || Last_Name) FROM Customers

    OR

    SELECT First_Name || ' ' || Last_Name As Full_Name FROM Customers

  5. #5
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Quote Originally Posted by MrDatabase View Post
    Thanks guys for answering me back, but let me make sure I have this right.

    Which one of these statements will be the right way.

    SELECT Full_Name(First_Name || ' ' || Last_Name) FROM Customers

    OR

    SELECT First_Name || ' ' || Last_Name As Full_Name FROM Customers
    Number 2.

    Andy

  6. #6
    Join Date
    Mar 2012
    Posts
    10
    Since you mentioned being new to db2 I should point out that if any of your columns in your concatenation list are null the total result of the concatenation is null.

    To deal with that you may wish to use coalesce around any potentially nullable columns in your concatenation.

  7. #7
    Join Date
    Mar 2012
    Posts
    27
    Good deal!

    Thanks bitznbitez, I appreciate the extra info.

  8. #8
    Join Date
    Nov 2011
    Posts
    24
    IFNULL function can also be used in place of coalese.
    This is similar to IFNULL in MS-SQL

  9. #9
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    IFNULL is actually defined in the SQL standard in terms of COALESCE, i.e. it is a <case abbreviation>:
    Code:
    b) NULLIF (V1, V2) is equivalent to the following <case specification>:
    CASE WHEN V1=V2 THEN NULL ELSE V1 END
    Pay close attention on non-deterministic expressions.

    However, you can't handle NULLs to inject a non-NULL value using NULLIF.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  10. #10
    Join Date
    Nov 2011
    Posts
    334
    Quote Originally Posted by stolze View Post
    IFNULL is actually defined in the SQL standard in terms of COALESCE, i.e. it is a <case abbreviation>:
    Code:
    b) NULLIF (V1, V2) is equivalent to the following <case specification>:
    CASE WHEN V1=V2 THEN NULL ELSE V1 END
    Pay close attention on non-deterministic expressions.

    However, you can't handle NULLs to inject a non-NULL value using NULLIF.
    Do you mean nullif is a non-deterministic expressions.
    Can you explain it more elaborately for "you can't handle NULLs to inject a non-NULL value using NULLIF"

  11. #11
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    It appears to me that stolze was probably reading too quickly and has confused IFNULL and NULLIF. His comment makes perfect sense to me for NULLIF, but is polarically opposite what I'd expect for IFNULL.

    I understand IFNULL (a, b) to be exactly the same as CASE WHEN a IS NULL THEN b ELSE a END which is exactly the same as Coalesce(a, b) in my mind.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  12. #12
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Pat is right. sorry about that. IFNULL() is just a synonym for COALESCE with 2 parameters only.
    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
  •