multiple results from a query

[watergrinder]

Hello,

I want to retrieve 3 different count(*) values from a query using date. i.e count(*) of past 3 days. Is there a way to do that?

 

[BarbaraFuhrmann]

Hello,

if I understand your question you can do this by

select date_field, count(*)
from table
where date_field between sysdate and sysdate - 3
group by date_field;

Barbara

 

[watergrinder]

Hi,

I tried this,

select moddate, count(*)
from tablename
where moddate between (SELECT sysdate-3 FROM DUAL) and (SELECT sysdate FROM DUAL) group by moddate

But this considers time (hh:mi:ss) also. I need to retrieve records based on date only. I was trying to_char, didn't work. Any ideas?

 

[sem]

select TRUNC(moddate), count(*)
from tablename
where moddate between sysdate-3 and sysdate
group by TRUNC(moddate)

You may also need to truncate sysdate and moddate in WHERE clause also depending on your exact requirements.

Regards, Dima

http://seminihin.narod.ru

 

[watergrinder]

Thanks. I have another problem.There is a field which has extra spaces at the end. I wanted to trim the spaces to right before displaying query output. Any ideas?

 

[randall2nd]

I think Oracle has trim functions.
TRIM(field_to_trim) <-- trim spaces from both sides
LTRIM(field_to_trim) <-- trim spaces from left side
RTRIM(field_to_trim) <-- trim spaces from right side

Randall2nd
Ignorance does not equal stupidity, do not treat it as such.

 

[watergrinder]

Thanks. I already tried rtrim and trim. For some reason, they won't take those extra spaces away.

 

[randall2nd]

Very odd. That is what they are for. Are you sure the spaces you are seeing are in the field, and not from the way it is being displayed? Not trying to be rude/mean, just first I have heard trim() not working.

You could write a stored procedure to trim undesired characters from the field. Something like
psuedo code

create or replace function myTrim( str1 in varchar2 ) return str as
    varchar2 str := str1;
    integer  pos := length( str ) - 1;
  begin
    -- find the position of last space
    loop
      exit when ( substr( str, pos, 1 ) != " " )
      pos := pos  - 1;
    end loop;

    -- return desired characters from string
    return substr( str, 0, pos );
  end;
end;
/

There might be a more effecient way, but it gives you an idea.

If your field is not supposed to have any spaces then you could use instr() to find the space.

Randall2nd
Ignorance does not equal stupidity, do not treat it as such.

 

[watergrinder]

Yes, I think they are certainly spaces. Because, substr(fieldname,1,length(fieldname)) displays spaces whereas substr(fieldname,1,30) displays 30 chars only. Thanks for the pl/sql. Is it possible at all to implement it in sql?

 

[watergrinder]

And length(fieldname) = 30

 

[randall2nd]

Yikes straight plain sql. I am not sure.

Are you going against an oracle DB, are you just not able to do stored procedures, or no PL/SQL at all. Oracle can do some PL/SQL inside a select statement.

Can you use the DECODE() function. It would be messy depending on the size of your field.

Have you tried the REPLACE() function. I have heard it has some problems with spaces.

It really depends alot on the data that is in your field and what you are using to manipulate/display it.

Are there rules that govern this field?
Is there only one space at the end of the field or are there potentially multiple spaces?
How are you getting the data from the DB?
What are you doing with it after you get it from the DB? Can you manipulate it after you get it from the DB?

Do you have any sample data, table structures, SQL statements, code snipits, etc... you can post.

You may wish to repost this question in a new thread with a specific subject line, so it can grab the attention of people who may have encountered this situation.

Randall2nd
Ignorance does not equal stupidity, do not treat it as such.