| Help me with this Query. [message #246002] |
Tue, 19 June 2007 09:41  |
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  |
pablolee
Messages: 2882
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 |
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 |
joy_division
Messages: 4963
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.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
