Home » SQL & PL/SQL » SQL & PL/SQL » error ORA-01481 (Windows XP)
error ORA-01481 [message #354231] Fri, 17 October 2008 02:21 Go to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 #354238 is a reply to message #354237] Fri, 17 October 2008 02:50 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
anything wrong?

Quote:
to_date(&date_today, 'YYYYMMDD')
to_char(&date_today...

Either &date_today is a date or a string, so at least one of these is wrong.

Use SQL*Plus and copy and paste your session.
Before please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter) and align the columns in result.
Use the "Preview Message" button to verify.

Regards
Michel


Re: error ORA-01481 [message #354239 is a reply to message #354237] Fri, 17 October 2008 02:53 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Use quotes around your &dates
Re: error ORA-01481 [message #354240 is a reply to message #354238] Fri, 17 October 2008 02:55 Go to previous messageGo to next message
ejoeyz_85
Messages: 30
Registered: October 2008
Member
when i inserted &date_today = 20080907, the error occured.. and when i inserted other than that date, it was successful..

what is wrong?
Re: error ORA-01481 [message #354244 is a reply to message #354240] Fri, 17 October 2008 03:08 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
You didn't change the Group By clause.
Re: error ORA-01481 [message #354246 is a reply to message #354240] Fri, 17 October 2008 03:10 Go to previous messageGo to next message
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 #354247 is a reply to message #354240] Fri, 17 October 2008 03:11 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Was it really the same error, or maybe a division by zero?
Re: error ORA-01481 [message #354254 is a reply to message #354247] Fri, 17 October 2008 03:39 Go to previous messageGo to next message
ejoeyz_85
Messages: 30
Registered: October 2008
Member
so how to change it? Shocked
Re: error ORA-01481 [message #354257 is a reply to message #354254] Fri, 17 October 2008 04:07 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
That's not really an answer...
Re: error ORA-01481 [message #354260 is a reply to message #354231] Fri, 17 October 2008 04:14 Go to previous messageGo to next message
ejoeyz_85
Messages: 30
Registered: October 2008
Member
it was really the same error... i just inserted the 20080907 as date_today... error occured then..
Re: error ORA-01481 [message #354261 is a reply to message #354260] Fri, 17 October 2008 04:15 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Show us a copy & paste from a sqlplus session where you execute what you describe, please.
Re: error ORA-01481 [message #354262 is a reply to message #354260] Fri, 17 October 2008 04:16 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Michel Cadot wrote on Fri, 17 October 2008 09:50
Use SQL*Plus and copy and paste your session.
Before please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter) and align the columns in result.
Use the "Preview Message" button to verify.

Regards
Michel

Re: error ORA-01481 [message #354263 is a reply to message #354261] Fri, 17 October 2008 04:23 Go to previous messageGo to next message
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 #354267 is a reply to message #354263] Fri, 17 October 2008 04:46 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Michel Cadot wrote on Fri, 17 October 2008 11:16
Michel Cadot wrote on Fri, 17 October 2008 09:50
Use SQL*Plus and copy and paste your session.
Before please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter) and align the columns in result.
Use the "Preview Message" button to verify.

Regards
Michel



Re: error ORA-01481 [message #354271 is a reply to message #354263] Fri, 17 October 2008 05:17 Go to previous messageGo to next message
Frank
Messages: 7880
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 Go to previous messageGo to next message
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 Go to previous message
joy_division
Messages: 4643
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.
Previous Topic: Delete data
Next Topic: Use SUBSTR to get 3rd position with _ delimeter
Goto Forum:
  


Current Time: Fri Dec 09 02:11:53 CST 2016

Total time taken to generate the page: 0.24415 seconds