?
Solved

ORA-01841: (full) year must be between -4713 and +9999, and not be 0

Posted on 2006-04-14
16
Medium Priority
?
2,065 Views
Last Modified: 2010-05-18
I am having trouble running this query.

select cust.customer_id as customer_id, sum(ac.amount)
from customers cust, accounts ac, months m
where cust.CUSTOMER_ID = ac.customer_ID
and ac.month_id = m.month_id
and to_date(m.year_month,'YYYY-MM') < current_date
group by cust.customer_id

This results in an ORA-01841: (full) year must be between -4713 and +9999, and not be 0 error.
The m.year_month is a string field in the format YYYY-MM ex: 2006-04

Any input as to why this query is failing?

I tried it on the months table, which has the year_month field and in there it works fine.
In other words, this query works fine

select *
from months m
where to_date(m.year_month,'YYYY-MM') < current_date

There are NO null or empty values in the result set. I tried that by removing the filter and displaying the to_date(m.year_month,'YYYY-MM') field to see if there are any invalid values. There were no invalid values.

I am totally lost. Please help
0
Comment
Question by:gopatinc
  • 6
  • 6
  • 2
  • +2
16 Comments
 
LVL 19

Expert Comment

by:actonwang
ID: 16458816
Are those 2 queries all running in sql*plus?
also could you try this:

select cust.customer_id as customer_id, sum(ac.amount)
from customers cust, accounts ac, months m
where cust.CUSTOMER_ID = ac.customer_ID
and ac.month_id = m.month_id
group by cust.customer_id

to see if it works?

what is the definition for column year_month?
0
 
LVL 19

Assisted Solution

by:actonwang
actonwang earned 1000 total points
ID: 16458907
also if your :

select *
from months m
where to_date(m.year_month,'YYYY-MM') < current_date

is fine, at least you can do is to change your original query as:

select cust.customer_id as customer_id, sum(ac.amount)
from customers cust, accounts ac,
  (
select *
from months m1
where to_date(m1.year_month,'YYYY-MM') < current_date)
 m
where cust.CUSTOMER_ID = ac.customer_ID
and ac.month_id = m.month_id
group by cust.customer_id


Acton
0
 
LVL 5

Author Comment

by:gopatinc
ID: 16459248
The first query you mentioned Acton, does work.
year_month is a string column
I am runnign the queries from TOAD

I am aware of the option of using subquery but would like to avoid it and understand instead why the qyuery is failing in the first place.

One way I figures, and currently use, is the following:

select cust.customer_id as customer_id, sum(ac.amount)
from customers cust, accounts ac
where cust.CUSTOMER_ID = ac.customer_ID
and ac.month_id < ( select m.month_id
                              from months m
                              where to_date(m.year_month,'YYYY-MM') < current_date
                             )
group by cust.customer_id

Infact if I put an additional filter in the original query, as below, it works

select cust.customer_id as customer_id, sum(ac.amount)
from customers cust, accounts ac, months m
where cust.CUSTOMER_ID = ac.customer_ID
and ac.month_id = m.month_id
and m.month_id < 36
and to_date(m.year_month,'YYYY-MM') < current_date
group by cust.customer_id

NOTE: I have added the filter m.month_id < 36   The months table only has entries upto month_id 36.
I also tried other mumbers, ex 40 and that worked as well.
Any ideas why I am having suvh behaviour?
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 16

Accepted Solution

by:
RCorfman earned 1000 total points
ID: 16461729
I don't have an explanation why it isn't working, other than some of the data seems to be bad in that year_month column, but that doesn't make sense either assuming the short query you show...
select * from months m
where to_date(m.year_month,'YYYY-MM') < current_date
Really does work. If that is the case, then it would seem something else is going on.

I will note though... it is preferable NOT to use a function on the database column when possible. This precludes use of an index... since the values are presumably in a sortable order (yyyy-mm), it would be better to put the function around current_date instead....

Does this query work?

select cust.customer_id as customer_id, sum(ac.amount)
from customers cust, accounts ac, months m
where cust.CUSTOMER_ID = ac.customer_ID
and ac.month_id = m.month_id
and m.year_month < to_char(current_date,'YYYY-MM')
group by cust.customer_id
0
 
LVL 7

Expert Comment

by:vishal68
ID: 16467252
What is this current_date. Is it a column of a table. Which table, what is the data type of current_date

0
 
LVL 16

Expert Comment

by:RCorfman
ID: 16467280
current_date is the same as sysdate...  I had that same question till I tried it... at least it is so in 9iR2.
0
 
LVL 11

Expert Comment

by:pennnn
ID: 16469849
Do you have an index on the m.year_month column?
The only explanation for the problem that comes to mind is that in your original query the optimizer chooses to do an index only scan (no table access) and somehow the index is corrupted and the data stored in the index (not in the table) is bad and causes the error.
So when you add other conditions to the query, the optimizer decides to use a table scan, so it gets the correct data from the table (instead of the index) and the query works fine.
If there is such an index try dropping and recreating it and see if this solves the problem.
Hope that helps!
0
 
LVL 5

Author Comment

by:gopatinc
ID: 16470160
Pennn, there is no index on the year_month column.

The query RCorfman sent also works and he has made a valid point as well.
Thanks everyone.
0
 
LVL 5

Author Comment

by:gopatinc
ID: 16470177
actonwang, your query is also more optimized (as compared to mine) due to the use of subquery
0
 
LVL 19

Expert Comment

by:actonwang
ID: 16470436
did you dig out the reason for the error?
0
 
LVL 5

Author Comment

by:gopatinc
ID: 16470472
No idea whatsoever. Have tried and failed
0
 
LVL 19

Expert Comment

by:actonwang
ID: 16471641
could you try run your query in sqlplus? It could be a bug in TOAD.
0
 
LVL 5

Author Comment

by:gopatinc
ID: 16471785
tried that as well. did not help, same error message
0
 
LVL 19

Expert Comment

by:actonwang
ID: 16471895
if you don't mind, could you send your table definition and data (if not big) to me. I am interested to see it.
0
 
LVL 5

Author Comment

by:gopatinc
ID: 16472380
which table? all 3? the data is too large for that
0
 
LVL 19

Expert Comment

by:actonwang
ID: 16472384
how about table definitions first?
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
Suggested Courses

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question