How to remove space when spool data to textfile

[rosezha83]

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.

 

[JarlH]

Check out the ANSI SQL function TRIM.

E.g. [tt]SELECT TRIM(somecharacterexpression) FROM ...[/tt]

 

[rosezha83]

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??

 

[r937]

this is the ANSI SQL Forum

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



r937.com | rudy.ca