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

Is that mean cola|colb|colc ?
If so, try an expression like...
cola || '|' || CHAR(colb) || '|' || CHAR(colc)

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

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"
More detailed description was in "DB2 Universal Database for z/OS SQL Reference"
------------------------------------------------------------------
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.
------------------------------------------------------------------