IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

Need SQL Query To concatenate Pipe symbol between columns


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
revel

Active User


Joined: 05 Apr 2005
Posts: 135
Location: Bangalore/Chennai-INDIA

PostPosted: Tue Nov 04, 2008 5:11 pm
Reply with quote

Hi DB2 Folks,

Give me solution for below puzzle

Consider a table having col1, col2, col3 ...... col100 columns
Now i want single query, which fetches 100 columns information + It has to concatenate | (Pipe symbol) Between each columns

ie

Code:
col1    col2        col3 ..............   col100
A         B            C                     X                   
D         E            F                     Y
.
.
.


Output should look like

Code:
A|B|C|............|X
D|E|F|............|Y
.
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Tue Nov 04, 2008 5:15 pm
Reply with quote

you could look at the concat operand in a db2 manual.
Back to top
View user's profile Send private message
ksk

Active User


Joined: 08 Jun 2006
Posts: 355
Location: New York

PostPosted: Tue Nov 04, 2008 5:32 pm
Reply with quote

If you are doing through COBOL-DB2 program, declare a variable with value '|' and concatenate this field with other variables.

E.g.,

Code:

01    VAR1     PIC X VALUE '|'.


And code query as SELECT A1||VAR1||A2||VAR1||A3.....

where A1, A2, A3... are variabels to be concatenated.
Back to top
View user's profile Send private message
revel

Active User


Joined: 05 Apr 2005
Posts: 135
Location: Bangalore/Chennai-INDIA

PostPosted: Tue Nov 04, 2008 6:21 pm
Reply with quote

Thank you KSK and Dick for your suggestion... :o)

We are also doing same way using query as,

Code:
Select A1, CHAR('|')
      ,A2, CHAR('|')
      ,A3, CHAR('|')
From table


But I am looking for alternative solution because in case If table is having more than 100 columns, Then we need to mention every column name in single query itself.. so....

It puzzled me :O)
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Tue Nov 04, 2008 8:26 pm
Reply with quote

are you doing this in spufi/qmf or imbedded in program?

what is the reason for the pipe symbol between each column?

what is the final destination for this output?
Back to top
View user's profile Send private message
revel

Active User


Joined: 05 Apr 2005
Posts: 135
Location: Bangalore/Chennai-INDIA

PostPosted: Wed Nov 05, 2008 10:26 am
Reply with quote

Dear Dick,

I am doing it in program and I am using Pipe SYmbols between Resultant columns to differenciate columns very easily in output File so later that can be transfered to Excel Sheet and send it across client..

I am looking for Query which yield output as i stated above with writing every columns in a query..
Back to top
View user's profile Send private message
revel

Active User


Joined: 05 Apr 2005
Posts: 135
Location: Bangalore/Chennai-INDIA

PostPosted: Wed Nov 05, 2008 10:32 am
Reply with quote

Sorry there is typo
Quote:

I am looking for Query which yield output as i stated above with writing every columns in a query


I am looking for Query which yield output as i stated above without writing every columns in a query
Back to top
View user's profile Send private message
Amsar

New User


Joined: 26 Sep 2008
Posts: 84
Location: Chennai

PostPosted: Wed Nov 05, 2008 10:35 am
Reply with quote

Raghu,

Your way is correct you can easily catalog sysout in one PS ( if you run as a batch spufi) and after formatting the ps you can transfer it in excel as separated by |.

I have worked on this kind of requirement from my client.

Thanks,
Amsar
Back to top
View user's profile Send private message
sushanth bobby

Senior Member


Joined: 29 Jul 2008
Posts: 1020
Location: India

PostPosted: Wed Nov 05, 2008 1:52 pm
Reply with quote

Revel,

In excel there is an option in DATA --> TEXT to COLUMNS. There you got to select FIXED WIDTH and keep on marking the columns.

Sushanth
Back to top
View user's profile Send private message
dick scherrer

Moderator Emeritus


Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix

PostPosted: Wed Nov 05, 2008 2:05 pm
Reply with quote

Hello,

You might look into the High Performance Unload.
www-01.ibm.com/software/data/db2imstools/db2tools/db2hpu/

This would allow you to create delimited output files.
Back to top
View user's profile Send private message
revel

Active User


Joined: 05 Apr 2005
Posts: 135
Location: Bangalore/Chennai-INDIA

PostPosted: Wed Nov 05, 2008 3:48 pm
Reply with quote

Quote:

In excel there is an option in DATA --> TEXT to COLUMNS. There you got to select FIXED WIDTH and keep on marking the columns.


Hi Sushanth,

My question is How the Query Would be; not Taking FTP data info into Excel Sheet.. :o)

To dick scherrer,

I didn't any info from that link.. :o)

Anyhow thank you for all valuable time
Back to top
View user's profile Send private message
sushanth bobby

Senior Member


Joined: 29 Jul 2008
Posts: 1020
Location: India

PostPosted: Wed Nov 05, 2008 3:53 pm
Reply with quote

REVEL,

Quote:
My question is How the Query Would be; not Taking FTP data info into Excel Sheet..


Can you be more clear.

Query means, u talking about the RESULTSET data or the SELECT query.

Sushanth
Back to top
View user's profile Send private message
revel

Active User


Joined: 05 Apr 2005
Posts: 135
Location: Bangalore/Chennai-INDIA

PostPosted: Wed Nov 05, 2008 4:02 pm
Reply with quote

Hello Sushanth

Quote:
u talking about the RESULTSET data or the SELECT query.


Please see post which i posted above (1st one), I need resultant like

Code:
A|B|C|............|X
D|E|F|............|Y


in a OUTPUT DATA SET, I have to achieve this through programatically using single query

See the post

Quote:
Thank you KSK and Dick for your suggestion... :o)

We are also doing same way using query as,

Code:
Code:
Select A1, CHAR('|')
      ,A2, CHAR('|')
      ,A3, CHAR('|')
From table



But I am looking for alternative solution because in case If table is having more than 100 columns, Then we need to mention every column name in single query itself.. so....

It puzzled me :O)


Hope u r clear with question
Back to top
View user's profile Send private message
Arun Raj

Moderator


Joined: 17 Oct 2006
Posts: 2481
Location: @my desk

PostPosted: Wed Nov 05, 2008 4:04 pm
Reply with quote

Raghu,

Most unload utilities have options to unload data from tables inserting any delimiter you want between 2 columns. If your whole purpose of the program is just to select rows and insert "|"s then you could check out the unload utilities available at your shop and do some research on that.
Back to top
View user's profile Send private message
revel

Active User


Joined: 05 Apr 2005
Posts: 135
Location: Bangalore/Chennai-INDIA

PostPosted: Wed Nov 05, 2008 4:38 pm
Reply with quote

Arun,

Thanks For your suggestion
Back to top
View user's profile Send private message
sushanth bobby

Senior Member


Joined: 29 Jul 2008
Posts: 1020
Location: India

PostPosted: Wed Nov 05, 2008 4:44 pm
Reply with quote

Revel,
To get a result like this,
Code:
A|B|C|............|X
D|E|F|............|Y


You must do something like this for all the columns,
Code:
Select A1, CHAR('|')
      ,A2, CHAR('|')
      ,A3, CHAR('|')
From table


As ARUN said, you can unload the table with delimiter like (; , | etc..). The result will be in a dataset as you wanted. But UNLOAD is not a query REVEL, more over you wont be getting column names in unloaded dataset as spufi gives you. If the column name are not important you can just UNLOAD with DELIMITER option.

You can use DSNTEP2 , but the result dataset wont be like a SPUFI dataset. It has limited WIDTH.
DSNTEP2 result will be like this

Code:
1                                                                               
1PAGE    1                                                                     
 ***INPUT STATEMENT:                                                           
  SELECT * FROM TESTING.CASE FETCH FIRST 10 ROWS ONLY;                   
                    +-----------------------------------------------------------
                    | CO_CD     | SYS_CD      |   CASE_NO        | CASE_STAT
                    +-----------------------------------------------------------
                  1_| VL         | VTGU1           |         0002263 | 1986-12-3
                  2_| VL         | VTGU1           |         0004371 | 1986-05-0
                  3_| VL         | VTGU1           |         0006760 | 2009-08-2
                  4_| VL         | VTGU1           |         0006940 | 2006-10-1
                  5_| VL         | VTGU1           |         0007044 | 1987-01-2
                  6_| VL         | VTGU1           |         0007688 | 1987-02-1
                  7_| VL         | VTGU1           |         0008152 | 1987-01-2
                  8_| VL         | VTGU1           |         0008753 | 1987-05-0
                  9_| VL         | VTGU1           |         0012133 | 1988-09-1
                 10_| VL         | VTGU1           |         0012902 | 1987-08-2
                    +-----------------------------------------------------------



If the dataset you are preparing is only for excel, and you want this column separation for excel alone. You can use the options available in excel itself as i said before DATA-->TEXT TO COLUMNS, you have two options in there FIXED WIDTH & DELIMITED. You can use things which is already available.

For excel, execute the query in SPUFI, send the data to windows, open the dataset, copy all the data, paste in excel worksheet and click on data-->text to columns.


Sushanth Bobby
Back to top
View user's profile Send private message
revel

Active User


Joined: 05 Apr 2005
Posts: 135
Location: Bangalore/Chennai-INDIA

PostPosted: Wed Nov 05, 2008 5:08 pm
Reply with quote

Hello Sushanth Bobby

Quote:
you can unload the table with delimiter like (; , | etc..). The result will be in a dataset as you wanted. But UNLOAD is not a query REVEL


Please Please see my previous post, I don't want to do using Utilities(I know UNLOAD is a utility - I Think you are speaking about DSNTIAUL UNLOAD utility), I want to do through programatically(Batch COBOL-DB2 Program)

Already we coded pgm which uses Query as i stated above but now i am looking for a query which should not use 100 columns in query for achieving result

To elabarate

The result we can get using below Query

Code:
SELECT Col1, CHAR('|')
          ,Col2, CHAR('|')
            .
            .
            .
           ,Col100, CHAR('|')
From Table


But i am looking Query simple one ie instead of using Columns can we achieve as like SELECT * FROM Table
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Wed Nov 05, 2008 5:18 pm
Reply with quote

Raghu,

Please, please, learn something about db2.

It is so easy to cut and paste the dcl from a table to get the column names
and use a rexx script to insert the literal '|' between each column name in the select to build your imbedded sql.

sure you are going to have to change it each time a column is added or deleted from a table.

otherwise, you will need to use dynamic sql to build your select statement based
on the column names returned from the sysimb db2 table containing column names for your table.
Back to top
View user's profile Send private message
sushanth bobby

Senior Member


Joined: 29 Jul 2008
Posts: 1020
Location: India

PostPosted: Wed Nov 05, 2008 5:31 pm
Reply with quote

RAVEL,
COBOL-DB2 - You can use FILLER's

Quote:
But i am looking Query simple one ie instead of using Columns can we achieve as like SELECT * FROM Table


Code:
SELECT * from TABLE

is not a efficient way to access data from a table.

Since you are looking for a single simple query,
Code:
SELECT CONCATALL('|',ALL COLUMNS) from TABLE

A query like above doesn't exist. What you looking for is common command for all the columns in the table. Which doesn't exist by the way or i don't know.

Read DINO's post, GOOD WAYS of achieving your RESULT.

Thank You,
Sushanth Bobby
Back to top
View user's profile Send private message
revel

Active User


Joined: 05 Apr 2005
Posts: 135
Location: Bangalore/Chennai-INDIA

PostPosted: Wed Nov 05, 2008 5:50 pm
Reply with quote

Quote:
Please, please, learn something about db2.
It is so easy to cut and paste the dcl from a table to get the column names
and use a rexx script to insert the literal '|' between each column name in the select to build your imbedded sql.


Anyhow Thank you for your solution icon_smile.gif icon_smile.gif
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
No new posts RC query -Time column CA Products 3
No new posts Remote Unload of CLOB Columns DB2 6
No new posts Increase the number of columns in the... IBM Tools 3
No new posts Dynamically pass table name to a sele... DB2 2
No new posts Query on edit primary command CLIST & REXX 5
Search our Forums:

Back to Top