Unload command

[lbzh]

I have some questions on the unload command.

I have a query such as:

Unload to c:\junk\test.txt delimiter ""
select
field1,
field2,
field3
from file1

Even though i put a delimiter of 2 double quotes. I still get pipes. Why?

Also if field1 and field2(both char (3)) are blank, the results are
pipes but no spaces between the pipes. I was expecting there to be 3 spaces between the pipes. How do I get rid of the pipes and maintain the 3 spaces even if field value is blank?
Thanks

 

[vpshriyan]

1. delimiter "" removes the default pipe data delimiter
If it fails try to use || (concat)
Unload to c:\junk\test.txt
select
field1||
field2||
field3
from file1 ;

2. unload statement knocks off spaces in the data. Use nvl function to fill the place holders.
Unload to c:\junk\test.txt
select
nvl(field1, ' ')||
nvl(field2, ' ')||
nvl(field3, ' ')
from file1 ;