View previous topic :: View next topic
|
Author |
Message |
Nilanjan Sikdar
New User
Joined: 26 Feb 2016 Posts: 9 Location: India
|
|
|
|
Hello,
I have a requirement where I need to join multiple table and need to extract data with pipe ('|') delimiter.
I have used QMF to run the query and try to export data using
EXPORT DATA TO TEST (DATAFORMAT=CSV
but this provides comma separated dataset.
Also tried to execute SPUFI with concatenation operator like below:
select
strip(char(COL1)) ||'|'
||strip(char(COL2)) ||'|'
||strip(char(COL3)) ||'|'
||strip(VARCHAR_FORMAT(CT.DTE,'DD/MM/YYYY')) ||'|'
||strip(VARCHAR_FORMAT(CT.DTE,'DD/MM/YYYY')) ||'|'
||strip(VARCHAR_FORMAT(CT.DTE,'DD/MM/YYYY')) ||'|'
||strip(char(COL7)) ||'|'
||strip(char(COL8)) ||'|'
but this is not helping as the string length is becoming more than 80 characters.
Any help will be appreciated.
Thanks,
Nilanjan Sikdar |
|
Back to top |
|
|
Nic Clouston
Global Moderator
Joined: 10 May 2007 Posts: 2455 Location: Hampshire, UK
|
|
|
|
Why do you have the same date 3 times? Would producing it only once reduce your length to <80 bytes?
If your data is going to be more than 80 bytes whatever you try then it is going to be more than 80 bytes and you just have to live with it. |
|
Back to top |
|
|
Nilanjan Sikdar
New User
Joined: 26 Feb 2016 Posts: 9 Location: India
|
|
|
|
Hi,
Those are 3 different type of Date fields. Like Creation Date, Update date, Authorize date etc.
Thanks,
Nilanjan |
|
Back to top |
|
|
sergeyken
Senior Member
Joined: 29 Apr 2008 Posts: 2010 Location: USA
|
|
|
|
Use DB2 Unload utility with parameter
FORMAT DELIMITED ...
Example 9 from this IBM’s RTFM |
|
Back to top |
|
|
Nilanjan Sikdar
New User
Joined: 26 Feb 2016 Posts: 9 Location: India
|
|
|
|
Hi sergeyken,
Thanks for the link but it seems the utility works for only 1 table. I have the output from 4 different tables joined together.
Thanks,
Nilanjan |
|
Back to top |
|
|
sergeyken
Senior Member
Joined: 29 Apr 2008 Posts: 2010 Location: USA
|
|
|
|
Nilanjan Sikdar wrote: |
Hi sergeyken,
Thanks for the link but it seems the utility works for only 1 table. I have the output from 4 different tables joined together.
Thanks,
Nilanjan |
Utilities do work also with VIEW, and JOIN.
It is always more useful to RTFM carefully, rather than moaning in the forums. |
|
Back to top |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010 Posts: 3048 Location: NYC,USA
|
|
|
|
Nilanjan Sikdar wrote: |
Hi,
Those are 3 different type of Date fields. Like Creation Date, Update date, Authorize date etc.
Thanks,
Nilanjan |
By referring the San column ? At least use the correct column names if you needed to get different values for three different dates .
Second , what’s the error message for the original problem ? To get such report it’s always best to write a program and create a report that you need which allows you more flexibility and easy maintenance when it comes to future changes |
|
Back to top |
|
|
Nilanjan Sikdar
New User
Joined: 26 Feb 2016 Posts: 9 Location: India
|
|
|
|
Hi Rohit,
That was a typo and I mentioned same date column for all.
This is a one off requirement hence we are trying the do using SQL extract.
When I run the query in SPUFI/QMF there is no error but output is having one NULL column. When I reduce the number of columns it is coming perfect.
Thanks |
|
Back to top |
|
|
Nic Clouston
Global Moderator
Joined: 10 May 2007 Posts: 2455 Location: Hampshire, UK
|
|
|
|
How come there was a typo? You should be using copy/paste. Also - show your full SQL and sample table DDL/data. |
|
Back to top |
|
|
sergeyken
Senior Member
Joined: 29 Apr 2008 Posts: 2010 Location: USA
|
|
Back to top |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010 Posts: 3048 Location: NYC,USA
|
|
|
|
Quote: |
When I run the query in SPUFI/QMF there is no error but output is having one NULL column. When I reduce the number of columns it is coming perfect. |
Try handling the NULL using VALUE or COALESCE function. |
|
Back to top |
|
|
sergeyken
Senior Member
Joined: 29 Apr 2008 Posts: 2010 Location: USA
|
|
Back to top |
|
|
Nilanjan Sikdar
New User
Joined: 26 Feb 2016 Posts: 9 Location: India
|
|
|
|
Sergeyken
I was going through the material mentioned above but didn't get a clear syntax for multiple view join under different table space.
If possible then can you please provide a sample with syntax.
Thanks,
Nilanjan |
|
Back to top |
|
|
sergeyken
Senior Member
Joined: 29 Apr 2008 Posts: 2010 Location: USA
|
|
|
|
Nilanjan Sikdar wrote: |
Sergeyken
I was going through the material mentioned above but didn't get a clear syntax for multiple view join under different table space.
If possible then can you please provide a sample with syntax.
Thanks,
Nilanjan |
Use command "FIND" to search in the mentioned manual for the following topics:
Example: Unloading a partitioned table space
Example: Unloading partition subsets by using multiple SELECT statements
Example: Unloading a partitioned table space with one output file per partition in DB2 FORCE when an unsupported SELECT statement is used
etc.
I recommend you to start with some very basic examples: how at all you can unload a single table in DELIMITED format?
When done, you may continue to more complicated examples.
It is always a very bad idea to start with resolving any global problem while having no idea about each of required simple steps. (Unless you expect that someone would give you a ready-to-copy-and-paste solution.) |
|
Back to top |
|
|
Nilanjan Sikdar
New User
Joined: 26 Feb 2016 Posts: 9 Location: India
|
|
|
|
Thanks all for your help.
I have achieved the solution in a round about way. Extracted CSV formatted data from mainframe using QMF.
Then I used Microsoft excel to convert the comma to pipe. I need to take care about different columns so that original data shouldn't get converted with excel predefined format.
Thanks,
Nilanjan |
|
Back to top |
|
|
|