Home » Developer & Programmer » Reports & Discoverer » problem in removing spaces
problem in removing spaces [message #197119] Tue, 10 October 2006 02:24 Go to next message
satya_jsr
Messages: 10
Registered: October 2006
Junior Member
I am generating the report by spooling.

In that report I have computed Total.I have the problem that its having spaces before the number.

How to remove those spaces.
Re: problem in removing spaces [message #197122 is a reply to message #197119] Tue, 10 October 2006 02:51 Go to previous messageGo to next message
Littlefoot
Messages: 20900
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I can only imagine what you are talking about; could you post a line or two of your actual spooled file, and the result you'd like to have? A blind shot could be this: use LTRIM function to remove extra spaces.
Re: problem in removing spaces [message #197128 is a reply to message #197122] Tue, 10 October 2006 03:16 Go to previous messageGo to next message
satya_jsr
Messages: 10
Registered: October 2006
Junior Member
Thanks Littlefoot for ur comment.

But LTRIM didnt worked as i m computin the total so its a noumber only.

The format is like this

NAME EMPNO JOB DATE SAL
AAA 111 A1 01-OCT-2006 100
AAA 111 A1 02-OCT-2006 100
***************
TOTAL 200

BBB 222 B2 01-OCT-2006 100
BBB 222 B2 02-OCT-2006 100
BBB 222 B2 03-OCT-2006 100
***************
TOTAL 300

here on column SAL and in TOTAL I am getting spaces.
I want to remove that spaces.










Re: problem in removing spaces [message #197129 is a reply to message #197128] Tue, 10 October 2006 03:22 Go to previous messageGo to next message
Littlefoot
Messages: 20900
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I'm sorry, but I really don't see spaces you are talking about. Do you see them in front of SAL and TOTAL? To me, it looks like this:

TOTAL 200

What space do you want to remove? This one, so that the result is like

TOTAL200
Re: problem in removing spaces [message #197138 is a reply to message #197129] Tue, 10 October 2006 04:08 Go to previous messageGo to next message
satya_jsr
Messages: 10
Registered: October 2006
Junior Member
Its actually a .csv file.

And in the TOTAL column where the value is 200,before that value there is spaces in that column.

I wanted to remove that spaces.

Its like

TOTAL, 200

and I want as

TOTAL,200
Re: problem in removing spaces [message #197139 is a reply to message #197138] Tue, 10 October 2006 04:18 Go to previous messageGo to next message
Littlefoot
Messages: 20900
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I see ... can you check the query you use to produce this file? Is there concatenation such as
SELECT name ||', '|| sal FROM ...
and should be
SELECT name ||','|| sal FROM ...
Or, even better, post the whole script.
Re: problem in removing spaces [message #197142 is a reply to message #197139] Tue, 10 October 2006 04:44 Go to previous messageGo to next message
satya_jsr
Messages: 10
Registered: October 2006
Junior Member
Ya I have used that.

The query is like

"
break on EMPNO duplicate skip 1
compute sum LABEL 'TOTAL' of TOTAL on EMPNO
spool C:\test.csv
select 'NAME','EMPNO','JOB','DATE','SOLD' from DUAL;
select name
|| ',' ||empno
|| ',' ||job
,trunc(insert_timestamp) "DATE"
,count(*) "TOTAL"
from EMP
group by name,job,trunc(insert_timestamp)
order by EMPNO;;
"
Here the SOLD field is as the Employee selling some product in one particular date,so for each employee whouse EMPNO is uniqe has a TOTAL at last.

Now in TOTAL column the value for TOTAL is coming with spaces.

[Updated on: Tue, 10 October 2006 04:58]

Report message to a moderator

Re: problem in removing spaces [message #197153 is a reply to message #197142] Tue, 10 October 2006 05:07 Go to previous messageGo to next message
satya_jsr
Messages: 10
Registered: October 2006
Junior Member
neglect the earliear reply

"
break on EMPNO duplicate skip 1
compute sum LABEL 'TOTAL' of TOTAL on EMPNO
spool C:\test.csv
select 'NAME','EMPNO','PROD','DATE','SOLD' from DUAL;
select name
|| ',' ||empno
|| ',' ||prod
,trunc(insert_timestamp) "DATE"
,count(*) "TOTAL"
from EMP
group by name,prod,trunc(insert_timestamp)
order by EMPNO;;
"
Here the SOLD field is as the Employee selling some product in one particular date,so for each employee whouse EMPNO is uniqe has a TOTAL at last.

Now in TOTAL column the value for TOTAL is coming with spaces.
Re: problem in removing spaces [message #197166 is a reply to message #197153] Tue, 10 October 2006 05:51 Go to previous messageGo to next message
Littlefoot
Messages: 20900
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
It seems that this line bothers you:
compute sum LABEL 'TOTAL' of TOTAL on EMPNO
Would it be OK if you modify it so that it includes a comma as the last label's character?
compute sum LABEL 'TOTAL,' of TOTAL on EMPNO
Re: problem in removing spaces [message #197180 is a reply to message #197166] Tue, 10 October 2006 06:36 Go to previous messageGo to next message
satya_jsr
Messages: 10
Registered: October 2006
Junior Member
I have used that also but its shifting the cell and still conating the spaces in that cell
Re: problem in removing spaces [message #197182 is a reply to message #197180] Tue, 10 October 2006 06:46 Go to previous message
Littlefoot
Messages: 20900
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Too bad Sad

I'm not sure is it possible to force Oracle to remove that space; it is not something that YOU wrote (as formating dates, numbers etc.) but Oracle's default behaviour (if I'm wrong about it, I'm sorry). Saying

COMPUTE SUM OF total ON empno

is just that - I don't know how to format it to remove that extra space. Sorry.
Previous Topic: years of data
Next Topic: Report server in ias Server and windows2000 platform
Goto Forum:
  


Current Time: Wed Dec 07 16:49:21 CST 2016

Total time taken to generate the page: 0.27607 seconds