Home » SQL & PL/SQL » SQL & PL/SQL » error ORA-01481 (Windows XP)
error ORA-01481 [message #354231] |
Fri, 17 October 2008 02:21 |
ejoeyz_85
Messages: 30 Registered: October 2008
|
Member |
|
|
hello everyone...
I am getting the error: "ORA-01481: invalid number format model"
on this statement:
Quote: | select hostname, to_char(&date_today , 'YYYY'), to_char(&date_today , 'WW'), genesis_port, instance_name,
api_name, sum(total_time), max(max_time), min(min_time), (sum(total_time)/sum(count)), sum(count)
from gen_api_performance_daily
where statistics_date between to_char(&date_last_week, 'YYYYMMDD') and to_char(&date_today , 'YYYYMMDD')
group by hostname, to_char(&date_today , 'YYYY'), to_char(&date_today , 'WW'), genesis_port, instance_name, api_name;
|
i inserted date_today = 20080907 and date_last_week = 20080901
Any ideas why I am getting the error ? how to solve?
|
|
|
Re: error ORA-01481 [message #354233 is a reply to message #354231] |
Fri, 17 October 2008 02:26 |
JRowbottom
Messages: 5933 Registered: June 2006 Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
Because you would use TO_CHAR to convert a date into a string.
You want to convert your strings into dates, and then sometimes extract part of them back out again, and so you need to rewrite your query like this:
select hostname
,to_char(to_date(&date_today,'yyyymmdd') , 'YYYY')
,to_char(to_date(&date_today,'yyyymmdd') , 'WW')
,genesis_port
....
from gen_api_performance_daily
where statistics_date between to_date(&date_last_week, 'YYYYMMDD')
and to_date(&date_today , 'YYYYMMDD')
group by ...
|
|
|
Re: error ORA-01481 [message #354237 is a reply to message #354231] |
Fri, 17 October 2008 02:45 |
ejoeyz_85
Messages: 30 Registered: October 2008
|
Member |
|
|
i still got an error..
i have changed the statement like this:
Quote: | select hostname, to_char(to_date(&date_today, 'YYYYMMDD') , 'YYYY'), to_char(to_date(&date_today, 'YYYYMMDD') , 'WW'), genesis_port, instance_name,
api_name, sum(total_time), max(max_time), min(min_time), (sum(total_time)/sum(count)), sum(count)
from gen_api_performance_daily
where statistics_date between to_date(&date_last_week, 'YYYYMMDD') and to_date(&date_today , 'YYYYMMDD')
group by hostname, to_char(&date_today , 'YYYY'), to_char(&date_today , 'WW'), genesis_port, instance_name, api_name;
|
anything wrong?
|
|
|
|
|
|
|
Re: error ORA-01481 [message #354246 is a reply to message #354240] |
Fri, 17 October 2008 03:10 |
JRowbottom
Messages: 5933 Registered: June 2006 Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
The error you're getting is because when you issue a command like TO_CHAR('20080907','YYYY') Oracle performs an implicit TO_DATE on the string '20080907' in order to change it into a date.
The default format mask that you are using at your site is obviously not YYYYMMDD.
That is why you need to perform an explicit TO_DATE on the strings.
|
|
|
|
|
|
|
|
|
Re: error ORA-01481 [message #354263 is a reply to message #354261] |
Fri, 17 October 2008 04:23 |
ejoeyz_85
Messages: 30 Registered: October 2008
|
Member |
|
|
Quote: | select hostname, to_char(to_date(&date_today, 'YYYYMMDD') , 'YYYY'), to_char(to_date(&date_today, 'YYYYMMDD') , 'WW'), genesis_port, instance_name,
api_name, sum(total_time), max(max_time), min(min_time), (sum(total_time)/sum(count)), sum(count)
from gen_api_performance_daily
where statistics_date between to_date(&date_last_week, 'YYYYMMDD') and to_date(&date_today , 'YYYYMMDD')
group by hostname, to_char(&date_today , 'YYYY'), to_char(&date_today , 'WW'), genesis_port, instance_name, api_name;
|
insert &date_today = 20080907
error : invalid number format model
|
|
|
|
Re: error ORA-01481 [message #354271 is a reply to message #354263] |
Fri, 17 October 2008 05:17 |
Frank
Messages: 7901 Registered: March 2000
|
Senior Member |
|
|
This is not a copy-paste from sqlplus:
- the error is not in the format sqlplus would return it
- there would be more input required from you.
So, please open up sqlplus.
Paste your query
enter the requested items
copy the complete session
come back here
reply to this message
include the contents of your clipboard in [CODE]-tags, not as a QUOTE.
|
|
|
Re: error ORA-01481 [message #354295 is a reply to message #354260] |
Fri, 17 October 2008 08:05 |
joicejohn
Messages: 327 Registered: March 2008 Location: India
|
Senior Member |
|
|
@ejoeyz_85,
In addition to the other comments,
ejoeyz_85 wrote on Fri, 17 October 2008 14:44 | it was really the same error... i just inserted the 20080907 as date_today... error occured then..
|
I do hope you didn't see the comment provided by @JRowBottom,
JRowbottom wrote on Fri, 17 October 2008 13:38 |
You didn't change the Group By clause.
|
And still you didn't heed his advice:
ejoeyz_85 wrote on Fri, 17 October 2008 14:53 |
*** Added CODE tags, Extracted only the Group By Clause
group by hostname, to_char(&date_today , 'YYYY'),
to_char(&date_today , 'WW'), genesis_port,
instance_name, api_name;
|
Regards,
Jo
|
|
|
Re: error ORA-01481 [message #354296 is a reply to message #354263] |
Fri, 17 October 2008 08:09 |
joy_division
Messages: 4963 Registered: February 2005 Location: East Coast USA
|
Senior Member |
|
|
ejoeyz_85 wrote on Fri, 17 October 2008 05:23 |
insert &date_today = 20080907
|
What does this mean? Please provide syntactically correct information. You INSERT into a TABLE, not a variable.
|
|
|
Goto Forum:
Current Time: Thu Mar 28 11:14:59 CDT 2024
|