asked on

Avoid Blank Spaces for columns While Spooling to Flat File

Hello,
I have to use spool to export data to flat file(.txt) in oracle. These columns available in my table (TBL_JE_OUTPUT )
RECORD_TYPE,BUSINESSUNIT, JOURNALID,LEDGER,JOURNAL_LINE_NO, ACCOUNT- These are few columns in my table all are varchar2 type.
The columns like JournalID,Ledger,Jounrnal_line_no,Account are nullable. when I'm using a select query to spool data to text file - the columns having null value, the text file contains blank spaces for the columns which are null. Please find the example below.
SQL> set colsep ''
SQL> select RECORD_TYPE,BUSINESSUNIT, JOURNALID,LEDGER,JOURNAL_LINE_NO, ACCOUNT from tbl_je_output;

RBUSINJOURNALID LEDGER JOURNAACCOUN
--------------------------------------
LADV01 000005155110
LADV01 000006155110
LADV01 000007155110
LADV01 000008155110
LADV01 000009155110
LADV01 000010155110
LADV01 000011337016
HAFC01NEXT ACTUALS
LAFC01 000001152312
LAFC01 000002
HAFC01NEXT ACTUALS

But I want the output as under:
LADV01000005155110
LADV01000006155110
LADV01000007155110
LADV01000008155110
LADV01000009155110
LADV01000010155110
LADV01000011337016
HAFC01NEXTACTUALS
LAFC01000001152312
LAFC01000002
HAFC01NEXTACTUALS

How to achieve this? Please helpme out as this is an urgent requirement

Guy Hengel [angelIII / a3]

what about this:

 select RECORD_TYPE || BUSINESSUNIT ||  JOURNALID || LEDGER || JOURNAL_LINE_NO || ACCOUNT from tbl_je_output;

Open in new window

DiscoNova

If simple concatenation of the fields is all that is required, angelIII's solution is the correct way to go. On the other hand, if the concatenated fields might consist of (leadin or trailing) whitespaces, you might need to do something like this:

select
  trim(RECORD_TYPE)
  ||
  trim(BUSINESSUNIT)
  ||
  trim(JOURNALID)
  ||
  trim(LEDGER)
  ||
  trim(JOURNAL_LINE_NO)
  ||
  trim(ACCOUNT)
from
  tbl_je_output;

Open in new window

ASKER CERTIFIED SOLUTION

DiscoNova

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial