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

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
LVL 5
gopatincAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

actonwangCommented:
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
actonwangCommented:
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
gopatincAuthor Commented:
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

RCorfmanCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
vishal68Commented:
What is this current_date. Is it a column of a table. Which table, what is the data type of current_date

0
RCorfmanCommented:
current_date is the same as sysdate...  I had that same question till I tried it... at least it is so in 9iR2.
0
pennnnCommented:
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
gopatincAuthor Commented:
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
gopatincAuthor Commented:
actonwang, your query is also more optimized (as compared to mine) due to the use of subquery
0
actonwangCommented:
did you dig out the reason for the error?
0
gopatincAuthor Commented:
No idea whatsoever. Have tried and failed
0
actonwangCommented:
could you try run your query in sqlplus? It could be a bug in TOAD.
0
gopatincAuthor Commented:
tried that as well. did not help, same error message
0
actonwangCommented:
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
gopatincAuthor Commented:
which table? all 3? the data is too large for that
0
actonwangCommented:
how about table definitions first?
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.