Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2011
    Posts
    2

    Unanswered: using concat in db2 client

    Dear friends,
    I am new to db2 .I have configured in windows db2 client for mainframe db2 database.
    I am connecting to mainframe database using db2cmd connect
    (db2 connect xx using xx)
    here, i type all the sql commands.(db2 select * from xx or db2 update xx)

    But when i am trying to concat two columns with | delimiter , i am facing error. I want to concat two columns.

    Table 1
    ====
    column name column type
    cola varchar
    colb date
    colc num

    I want the output like below
    name|date|num


    Please give the sql query for this table.

    Thanks in Advance

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    I want the output like below
    name|date|num
    Is that mean cola|colb|colc ?
    If so, try an expression like...
    cola || '|' || CHAR(colb) || '|' || CHAR(colc)

  3. #3
    Join Date
    Jan 2003
    Posts
    1,605
    Hi,
    also don't forget if you executing pipe character "|" from db2 cmd this character is interpreted as operating system shell command.

    First of all you need to use double || to concat as tonkuma pointed out and then you have three options (maybe even more):
    1. open text editor and type in the select statement and execute statement from db2cmd using command: db2 -tf your_sql_file
    2. double quote the SQL command in db2cmd like: db2 "select col1 || col2 from mytable"
    3. I suggest to stop using pipes characters because there are hard to read by human eye. I suggest to use concat command like: db2 select col1 concat col2 from mytable

    Hope this helps

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    grofaty,

    I surprised!
    Thank you very much!

    Though I saw it many times in syntax diagram in "SQL Reference" manual and in "Information Center",
    until now I didn't noticed CONCAT operator.(I know CONCAT function.)


    I found a note in the manual "DB2 for LUW SQL Reference Volume 1"
    || may be used as a synonym for CONCAT.
    More detailed description was in "DB2 Universal Database for z/OS SQL Reference"
    Both CONCAT and the vertical bars (||) represent the concatenation operator.
    Vertical bars (or the characters that must be used in place of vertical bars in some
    countries) can cause parsing errors in statements passed from one DBMS to
    another
    . The problem occurs if the statement undergoes character conversion with
    certain combinations of source and target CCSIDs. Thus, CONCAT is the
    preferable concatenation operator
    .
    ------------------------------------------------------------------
    Even if I thought I knew enough in a field(DB2 SQL),
    there are more things to learn.
    Study will never end until my death.
    ------------------------------------------------------------------

Tags for this Thread

Posting Permissions

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