Unload into a delimited file with join condition

arien · New User Joined: 02 Nov 2006 Posts: 43 Location: London

Hi,

Couple of questions on logic clarification and need help on my approach here:

I need to UNLOAD a table into a comma-delimited text file and while unloading there should be a table join condition in the WHERE clause of the table.

I tried unloading using DSNUPROC, and it unloaded the complete table perfectly into delimited text file, but it comes with it's disadvantage that it cannot work with table joins? Is that correct ?

Next, I am trying with DSNUTILA, but it seems SELECT statement in SYSIN has a character limit of 72 characters? (Correct me if I am wrong here)What if I need to explicitly mention all the columns of a big file...

Also, how can I mention the column delimiter in SELECT sql without explicitly separating the Selected columns . Eg. if A B C are column names, my SQL for pipe-delmited output is something like :
SELECT A,'|',B,'|',C from sample;
Please suggest a solution?

Thanks

arien · New User Joined: 02 Nov 2006 Posts: 43 Location: London

Does that mean I need to write the parsing logic in a program instead

??

dick scherrer · Posted: Thu Aug 12, 2010 10:47 pm

Hello,

Suggest you post a few rows from both tables (relevant columns only) and the delimited output you want from the sample data.

arien · New User Joined: 02 Nov 2006 Posts: 43 Location: London

Thanks D.sch.

Just for example. My sample data has 2 input tables - EMP and SCENARIO

EMP data:

dbzTHEdinosauer · Posted: Fri Aug 13, 2010 12:37 am

I have always used APPENDIX1.4.1 Running DSNTIAUL
where I could simply code any sql select.

in your case I would code:

SELECT ScNo
,'|'
, ScName
FROM ...

in the case of ScNo, depending upon the data type, you may need a cast statement.

the nice thing about DSNTIAUL, you can always run the same sql in spufi.

arien · New User Joined: 02 Nov 2006 Posts: 43 Location: London

Thanks Db..

I could unload the table into a pipe-delimited flat file. But this SELECT statement has introduced spaces in between columns.

Eg: my output became:
1 | Has 1 Child
3 | Has no children

And second problem that I am facing is that the SMALLINT, DECIMAL format fields have become unreadable. any way around this? can I convert the output into ASCII format?

dick scherrer · Posted: Fri Aug 13, 2010 12:53 am

Hello,

You want your output to be plain text. The smallint and decimal fields are unreadable because they are still in the internal numeric format.

When you create the plain text delimited file, an ftp of the data to a unix or win-based system will automatically handle the ebcdic->ascii conversion.

Craq Giegerich · Posted: Fri Aug 13, 2010 1:00 am

arien · New User Joined: 02 Nov 2006 Posts: 43 Location: London

DSNUTIAL solves the issue to an extent. There is another utility that I came to know HPU - High Performance Unload. It runs the program INZUTILB. I'm trying to get my output based on this. I will update the forum.