Anyone know how to slove this?

[b3kool]

I'm working in a telephone company. I'm using Oracle9i, and SQL Navigator for Oracle. Here's my query:

select called, SUM(ceil(duration/60)) as SL0806
from c0806
where called like '1900%' group by called;

The query is fine, but in the results there're many same records like:

A B C
1 | 19001221 | 72
2 | 19001255 | 1186
3 | 19001255* | 1
4 | 190012552 | 6
5 | 190012553 | 3
6 | 1900125533 | 1
7 | 19001509 | 6
8 | 19001510 | 235
9 | 19001510# | 2
10 | 19001511 | 659
11 | 190015110 | 31
12 | 19001512 | 1

Now the problem is the record number 2 and number 3 are the same. Which statement I must use to retrive these records just in one?
I meant is must be:

A B C
1 | 19001221 | 72
2 | 19001255 | 1187
3 | 190012552 | 6
4 | 190012553 | 3

Please help me out! Thanks alot! (Sorry for my English)

 

[SantaMufasa]

b3kool,

Your English is not a problem...the problem is that your SELECT statement does not match your output: Your SELECT has only two output expressions, yet your output has three columns, and the output labels do not seem to relate to the SELECT expression either.

So, I suggest that you post the results of a "DESCRIBE c0806" statement, plus the results of a "SELECT * from c0806 where rownum <= 15;" so we can more clearly understand the context of your SELECT statement.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[Turkbear]

Hi,
Also, unless you typed it that way,
2 | 19001255 | 1186
3 | 19001255* | 1
are not the same ( The* is part of the field's data) - same thing with
8 | 19001510 | 235
9 | 19001510# | 2 ( The #)





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[slaing]

If you simply want to get rid of the # and * for the purposes of grouping, then the TRANSLATE function might do the job for you

For example :

select translate('12345#','1234567890#*','1234567890') from dual

Returns '12345'

If your row number column 'A' really is part of the query, then as said above, please post the data structure.

There might still be a problem if 'called' ever has digits after the # or *. In that case you could probably use INSTR and SUBSTR functions as well - it won't look pretty so I'm not going to code it on-spec !!

Steve

 

[Beilstwh]

Or use replace

select REPLACE('123#456','#') FROM DUAL;

123456

Bill
Oracle DBA/Developer
New York State, USA

 

[carp]

Well, we can only speculate since the poster hasn't been back since the original post and the sample output (1) doesn't match the query and (2) does not exhibit the problem the poster was complaining about.