|
View previous topic :: View next topic
|
| Author |
Message |
revel
Active User
Joined: 05 Apr 2005
Posts: 135
Location: Bangalore/Chennai-INDIA
|
|
|
|
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 |
|
 |
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne
|
|
|
|
| you could look at the concat operand in a db2 manual. |
|
| Back to top |
|
|
ksk
Active User
.jpg)
Joined: 08 Jun 2006
Posts: 355
Location: New York
|
|
|
|
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 |
|
|
revel
Active User
Joined: 05 Apr 2005
Posts: 135
Location: Bangalore/Chennai-INDIA
|
|
|
|
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 |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne
|
|
|
|
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 |
|
|
revel
Active User
Joined: 05 Apr 2005
Posts: 135
Location: Bangalore/Chennai-INDIA
|
|
|
|
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 |
|
|
revel
Active User
Joined: 05 Apr 2005
Posts: 135
Location: Bangalore/Chennai-INDIA
|
|
|
|
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 |
|
|
Amsar
New User
.jpg)
Joined: 26 Sep 2008
Posts: 84
Location: Chennai
|
|
|
|
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 |
|
|
sushanth bobby
Senior Member
Joined: 29 Jul 2008
Posts: 1020
Location: India
|
|
|
|
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 |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19243
Location: Inside the Matrix
|
|
| Back to top |
|
|
revel
Active User
Joined: 05 Apr 2005
Posts: 135
Location: Bangalore/Chennai-INDIA
|
|
|
|
| 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 |
|
|
sushanth bobby
Senior Member
Joined: 29 Jul 2008
Posts: 1020
Location: India
|
|
|
|
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 |
|
|
revel
Active User
Joined: 05 Apr 2005
Posts: 135
Location: Bangalore/Chennai-INDIA
|
|
|
|
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 |
|
|
Arun Raj
Moderator
Joined: 17 Oct 2006
Posts: 2481
Location: @my desk
|
|
|
|
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 |
|
|
revel
Active User
Joined: 05 Apr 2005
Posts: 135
Location: Bangalore/Chennai-INDIA
|
|
|
|
Arun,
Thanks For your suggestion |
|
| Back to top |
|
|
sushanth bobby
Senior Member
Joined: 29 Jul 2008
Posts: 1020
Location: India
|
|
|
|
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 |
|
|
revel
Active User
Joined: 05 Apr 2005
Posts: 135
Location: Bangalore/Chennai-INDIA
|
|
|
|
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 |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne
|
|
|
|
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 |
|
|
sushanth bobby
Senior Member
Joined: 29 Jul 2008
Posts: 1020
Location: India
|
|
|
|
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 |
|
|
revel
Active User
Joined: 05 Apr 2005
Posts: 135
Location: Bangalore/Chennai-INDIA
|
|
|
|
| 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  |
|
| Back to top |
|
|
|
|
