Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Need SQL Query To concatenate Pipe symbol between columns

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
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    Post subject: Need SQL Query To concatenate Pipe symbol between columns
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    Post subject:
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: 356
Location: New York

PostPosted: Tue Nov 04, 2008 5:32 pm    Post subject:
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    Post subject:
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    Post subject:
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    Post subject:
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    Post subject:
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    Post subject:
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: 1013
Location: India

PostPosted: Wed Nov 05, 2008 1:52 pm    Post subject:
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

Site Director


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

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

Hello,

You might look into the High Performance Unload.
http://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    Post subject:
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: 1013
Location: India

PostPosted: Wed Nov 05, 2008 3:53 pm    Post subject:
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    Post subject:
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: 2158
Location: @my desk

PostPosted: Wed Nov 05, 2008 4:04 pm    Post subject:
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    Post subject:
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: 1013
Location: India

PostPosted: Wed Nov 05, 2008 4:44 pm    Post subject:
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    Post subject:
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    Post subject:
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: 1013
Location: India

PostPosted: Wed Nov 05, 2008 5:31 pm    Post subject:
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    Post subject:
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    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
This topic is locked: you cannot edit posts or make replies. Db2 SQL query to convert rows into co... subratarec DB2 5 Thu Nov 17, 2016 4:51 pm
No new posts SQZ pipe delimited file with 1 column... Rick Silvers DFSORT/ICETOOL 2 Sun Nov 06, 2016 8:11 pm
No new posts Two Selects in a single select query Rohit Umarjikar DB2 1 Fri Nov 04, 2016 8:46 pm
No new posts Comparing Decimal and CHAR columns rakesh17684 DB2 7 Thu Oct 20, 2016 2:33 am
No new posts Problem in Running Query via JCL vickey_dw DB2 3 Tue Oct 18, 2016 11:11 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us