Home » SQL & PL/SQL » SQL & PL/SQL » Help me with this Query.
Help me with this Query. [message #246002] Tue, 19 June 2007 09:41 Go to next message
girishta
Messages: 30
Registered: December 2006
Member
dear all,

Am trying to get output in this format ......

TABLESPACE_NAME MONTH SUM(V.BYTES/1024/1024)
--------------- ------------ ----------------------
EXAMPLE JUNE-07 600

so i did write this query

Select d.tablespace_name , to_char(v.creation_time, 'MONTH-YY') Month , sum(v.bytes/1024/1024)
from dba_data_files d , v$datafile v
where d.file_name=v.name and v.creation_time > '17-JUN-07'
group by rollup(d.tablespace_name, v.creation_time)
having to_char(v.creation_time, 'MONTH-YY') is null
order by d.tablespace_name, v.creation_time;

but i got this output...

TABLESPACE_NAME MONTH SUM(V.BYTES/1024/1024)
------------------- ------------ ----------------------
EXAMPLE 600
600

please help me with a solution.

Thanks in advance.
Re: Help me with this Query. [message #246004 is a reply to message #246002] Tue, 19 June 2007 09:46 Go to previous messageGo to next message
pablolee
Messages: 2835
Registered: May 2007
Location: Scotland
Senior Member
instead of typing what you say you did, copy what you actually did from a sql plus session and paste it here (formatted and with code tags.)

Also supply db version.

1st thing tho' is do NOT compare dates to strings. Convert strings to dates explicitly.
Re: Help me with this Query. [message #246005 is a reply to message #246002] Tue, 19 June 2007 09:56 Go to previous messageGo to next message
Cthulhu
Messages: 381
Registered: September 2006
Location: UK
Senior Member
How is it you expect to see something in the Month column when you have:

having to_char(v.creation_time, 'MONTH-YY') is null

Why do you need to do a rollup ?
Re: Help me with this Query. [message #246011 is a reply to message #246002] Tue, 19 June 2007 10:10 Go to previous message
joy_division
Messages: 4642
Registered: February 2005
Location: East Coast USA
Senior Member
girishta wrote on Tue, 19 June 2007 10:41

where d.file_name=v.name and v.creation_time > '17-JUN-07'



Poor poor coding continues.

FOO SCOTT>l
  1* select 1 from dual where sysdate > '17-JUN-07'
FOO SCOTT>/
select 1 from dual where sysdate > '17-JUN-07'
                                   *
ERROR at line 1:
ORA-01843: not a valid month


DATEs cannot be greater than or less than character strings.
Previous Topic: Greatest column name with value
Next Topic: Leap years with a twist
Goto Forum:
  


Current Time: Thu Dec 08 08:39:38 CST 2016

Total time taken to generate the page: 0.05462 seconds