×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

How to remove space when spool data to textfile

How to remove space when spool data to textfile

How to remove space when spool data to textfile

(OP)
Hi, I try to spool data from database to textfile. My coding as below, but the problem now i cannot remove the space in text file. The output will be like this:-

Sample Output:
282|282000000792007 |23102007 | 1



The output that i want is like this:-
282|282000000792007|23102007|1


set trimspool on
spool on
spool D:\...
select to_char(trunc(a.T_157_Branch),'000'), (a.T_4071_New_Application_Number), (a.T_151_Application_Date) ,
CASE
WHEN to_number(substr(a.T_153_Application_Type,1,3)) is NULL THEN 0
ELSE a.T_153_Application_Type
END as App_type
from applicationdata a
inner join mainappdata b on a.sys_recordkey = b.sys_recordkey
inner join businessdata c on b.sys_recordkey = c.sys_recordkey
where (FINAL_APP_DATE is not null
AND FINAL_APP_DATE= (to_char(trunc(SYSDATE-1),'DDMMYYYY'))
AND STATUS ='12') OR EXTRA = 'Y'
AND
((length(Branch) = 1) OR (length(Branch) = 2) OR (length(Branch) = 3));

spool off



Anyone can help me!!


Thank you.

RE: How to remove space when spool data to textfile

Check out the ANSI SQL function TRIM.

E.g. SELECT TRIM(somecharacterexpression) FROM ...

RE: How to remove space when spool data to textfile

(OP)
Hi JarlH,

Thank you for your reply and help.

I able to solve the problem, but i keep get this error message "ORA-01445: Cannot select ROWID from, or sample, a join view without a key-preserved table" when i do inner join table.

the SQL as follow:-

SELECT LPAD(a.Branch, 3, '0')||
CASE
WHEN TO_NUMBER(RPAD(a.Application_Type,2)) IS NULL THEN 0
ELSE TO_NUMBER(RPAD(a.Application_Type,2))
END ||
CASE
WHEN TO_NUMBER(RPAD(b.MA_HT_NUMBER,8)) IS NULL THEN 0
ELSE TO_NUMBER(RPAD(b.MA_HT_NUMBER,8))
END ||
CASE
WHEN TO_NUMBER(RPAD(c.BUS_FAX_NO,8)) IS NULL THEN 0
ELSE TO_NUMBER(RPAD(c.BUS_FAX_NO,8))
END ||
CASE
WHEN TO_NUMBER(RPAD(d.GUAR1_DUR_OF_STAY_MM,2)) IS NULL THEN 0
ELSE TO_NUMBER(RPAD(d.GUAR1_DUR_OF_STAY_MM,2))
END
FROM  applicationdata   a
inner join mainappdata  b ON  a.sys_recordkey = b.sys_recordkey
inner join businessdata c ON  b.sys_recordkey = c.sys_recordkey
inner join guar1data    d ON  c.sys_recordkey = d.sys_recordkey
WHERE (a.VEH1_FINAL_APP_DATE IS NOT NULL
AND  a.VEH1_FINAL_APP_DATE= (TO_CHAR(TRUNC(SYSDATE),'DDMMYYYY'))
AND a.SYS_STATUSINFO ='12') OR a.FLAG_EXTRA = 'Y';


Any idea why the error message come out??

RE: How to remove space when spool data to textfile

this is the ANSI SQL Forum

i think perhaps you should try asking your question in one of the oracle forums instead

smile

r937.com | rudy.ca

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login


Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close