| Date comparison issue [message #600932] |
Thu, 14 November 2013 02:28  |
chat2raj.s
Messages: 161
Registered: October 2010
Location: Chennai, India
|
Senior Member |
|
|
Dear,
select To_Date(col1,'DD/MM/RRRR') from tab1; is working fine
select To_Date('01-01-2013','DD/MM/RRRR') from dual; is also working fine
But
SELECT 'x'
FROM tab1
WHERE To_Date(col1,'DD/MM/RRRR') >= To_Date('01-01-2013','DD/MM/RRRR');
give me error as below. Why ?
ORA-01858: a non-numeric character was found where a numeric was expected
TQ
|
|
|
|
|
|
|
|
|
|
| Re: Date comparison issue [message #600943 is a reply to message #600940] |
Thu, 14 November 2013 02:49  |
chat2raj.s
Messages: 161
Registered: October 2010
Location: Chennai, India
|
Senior Member |
|
|
If that was the case, then the following should give me the same error right..but it works fine..
select To_Date(col1,'DD/MM/RRRR') from tab1;
|
|
|
|
|
|
| Re: Date comparison issue [message #600947 is a reply to message #600945] |
Thu, 14 November 2013 03:00 |
chat2raj.s
Messages: 161
Registered: October 2010
Location: Chennai, India
|
Senior Member |
|
|
Iam using SQLTools 1.5.0 Beta build 9 (built with debug info).
Also the data in the col1 is sometimes like 30062013 or 14-OCT-08. Still my individual query shows no error, wheareas when i give a comparison of that date as in my main post, i get that error.
Thanks for your time.
|
|
|
|
| Re: Date comparison issue [message #600948 is a reply to message #600947] |
Thu, 14 November 2013 03:11 |
cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
Does SQLTools fetch all records at once, or in batches?
If the later get it to select all rows for the simple select and see if you get the error.
|
|
|
|
| Re: Date comparison issue [message #600949 is a reply to message #600947] |
Thu, 14 November 2013 03:11 |
John Watson
Messages: 8931
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
|
You had better report this to the person who wrote that SQLTools utility. Unless you care to download the source code and debug it yourself, which he would probably appreciate.
|
|
|
|
|
|
|
|
| Re: Date comparison issue [message #600956 is a reply to message #600953] |
Thu, 14 November 2013 04:04 |
chat2raj.s
Messages: 161
Registered: October 2010
Location: Chennai, India
|
Senior Member |
|
|
Sql tools fetches the complete data at one stretch with no errors..
Even the same error comes in Oracle reports also, from that is where i am trying to debug this case using sqltools.
|
|
|
|
| Re: Date comparison issue [message #600957 is a reply to message #600956] |
Thu, 14 November 2013 04:13 |
flyboy
Messages: 1903
Registered: November 2006
|
Senior Member |
|
|
chat2rajsgmailcom wrote on Thu, 14 November 2013 11:04Sql tools fetches the complete data at one stretch with no errors..
How did you verify that? How many rows were displayed? Did you list all of them?
What happens when you try
select To_Date(col1,'DD/MM/RRRR') from tab1 order by col1
from SQLTools? Because, this code really fetches all rows.
|
|
|
|
| Re: Date comparison issue [message #600958 is a reply to message #600956] |
Thu, 14 November 2013 04:14 |
cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
that would suggest there is a bug in sqltools then.
In you run
select To_Date(col1,'DD/MM/RRRR') from tab1;
in sqlplus does that generate the error?
If it does you've got corrupt data and sqltools is buggy.
|
|
|
|
| Re: Date comparison issue [message #600960 is a reply to message #600958] |
Thu, 14 November 2013 04:22 |
ThomasG
Messages: 3211
Registered: April 2005
Location: Heilbronn, Germany
|
Senior Member |
|
|
I have used SQLTools for years, and so far it behaved pretty much like SQLPlus where errors are concerned.
Did you click on the ">|" Button to go really to "the end" of the data set, and so fetch all rows?
|
|
|
|
|
|
| Re: Date comparison issue [message #600964 is a reply to message #600960] |
Thu, 14 November 2013 04:37 |
cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
ThomasG wrote on Thu, 14 November 2013 10:22Did you click on the ">|" Button to go really to "the end" of the data set, and so fetch all rows?
|
|
|
|
| Re: Date comparison issue [message #600965 is a reply to message #600964] |
Thu, 14 November 2013 04:59 |
chat2raj.s
Messages: 161
Registered: October 2010
Location: Chennai, India
|
Senior Member |
|
|
Yes i did. No Errors.
I also tried to take this data to another table and tested the same scenario and it works well.
The problem is only with the original table and its data.
|
|
|
|
|
|
| Re: Date comparison issue [message #600968 is a reply to message #600966] |
Thu, 14 November 2013 05:08 |
chat2raj.s
Messages: 161
Registered: October 2010
Location: Chennai, India
|
Senior Member |
|
|
If i execute the same query against individual records, then there is no errors. Whereas if it is executed on that table level, then it gives error.
ie, if 10 records in table , then i pass each primary key separately to that query and it works fine.
|
|
|
|
| Re: Date comparison issue [message #600969 is a reply to message #600966] |
Thu, 14 November 2013 05:09 |
cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
Then there's still corrupt data.
Create a function like this:
CREATE OR REPLACE FUNCTION check_date(p1 VARCHAR2)
RETURN DATE IS
BEGIN
RETURN to_date(p1,'DD/MM/RRRR');
EXCEPTION WHEN OTHERS THEN
NULL;
END;
Then run this query and see what it gives:
select col1 from tab1 where check_date(col1) is null;
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Date comparison issue [message #600977 is a reply to message #600974] |
Thu, 14 November 2013 05:30 |
chat2raj.s
Messages: 161
Registered: October 2010
Location: Chennai, India
|
Senior Member |
|
|
The below query works fine and returns 1 row only
SELECT GI_SYS_ID, To_Date(GI_FLEX_02, 'DD/MM/RRRR'),check_date(GI_FLEX_02)
FROM OT_GR_ITEM,OT_GR_HEAD
WHERE GH_SYS_ID=GI_GH_SYS_ID
AND GI_SYS_ID <= 11164617;
The same output has to come for this query also..just the last cond is added to it. But it give me the error..clearly it should not be a data problem.
SELECT GI_SYS_ID, To_Date(GI_FLEX_02, 'DD/MM/RRRR'),check_date(GI_FLEX_02)
FROM OT_GR_ITEM,OT_GR_HEAD
WHERE GH_SYS_ID=GI_GH_SYS_ID
AND GI_SYS_ID <= 11164617
AND To_Date(GI_FLEX_02, 'DD/MM/RRRR') >= To_Date('01/01/2000','DD/MM/RRRR');
|
|
|
|
|
|
|
|
| Re: Date comparison issue [message #600982 is a reply to message #600977] |
Thu, 14 November 2013 05:35 |
ThomasG
Messages: 3211
Registered: April 2005
Location: Heilbronn, Germany
|
Senior Member |
|
|
Well, the first query only accesses GI_FLEX_02 in ONE row, the second query might (depending on the execution plan) access GI_FLEX_02 in all rows.
It makes no sense to switch to "more complicated queries", we have to figure out where the wrong data is.
Have you created the check_date function cookiemonster posted and run his query? Please post the SQL session where you did that.
|
|
|
|
|
|
| Re: Date comparison issue [message #600984 is a reply to message #600977] |
Thu, 14 November 2013 05:37 |
cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
You assume that oracle checks this:
AND GI_SYS_ID <= 11164617
before this:
AND To_Date(GI_FLEX_02, 'DD/MM/RRRR') >= To_Date('01/01/2000','DD/MM/RRRR');
it doesn't have to, it can do the date check first, in which case any row in the table could be the problem.
So, again, run this query and see what it gives:
select col1 from tab1 where check_date(col1) is null;
If it outputs any rows you've got corrupt data.
Also before your next post, can you please read and follow How to use [code] tags and make your code easier to read?
|
|
|
|
| Re: Date comparison issue [message #600987 is a reply to message #600984] |
Thu, 14 November 2013 05:42 |
cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
There's a mistake in the function, I missed a RETURN, correct code is:
CREATE OR REPLACE FUNCTION check_date(p1 VARCHAR2)
RETURN DATE IS
BEGIN
RETURN to_date(p1,'DD/MM/RRRR');
EXCEPTION WHEN OTHERS THEN
RETURN NULL;
END;
|
|
|
|
| Re: Date comparison issue [message #600988 is a reply to message #600984] |
Thu, 14 November 2013 05:45 |
chat2raj.s
Messages: 161
Registered: October 2010
Location: Chennai, India
|
Senior Member |
|
|
Thanks will follow the Code tag henceforth.
Yes as you say, in that table the column will have even null values for many other records. But based on the other conditions in my query, what ever record is getting filtered, in those records that data is valid.
|
|
|
|
| Re: Date comparison issue [message #600989 is a reply to message #600988] |
Thu, 14 November 2013 05:48 |
cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
Who said anything about the column being null?
I didn't. If the column can be null, run this:
select col1 from tab1 where check_date(col1) is null
and col1 is not null;
|
|
|
|
| Re: Date comparison issue [message #600992 is a reply to message #600989] |
Thu, 14 November 2013 05:58 |
chat2raj.s
Messages: 161
Registered: October 2010
Location: Chennai, India
|
Senior Member |
|
|
|
Yes the above query will return data, but those records will get filtered based on my other conditions. So that should not be the actual problem in our case.
|
|
|
|
| Re: Date comparison issue [message #600993 is a reply to message #600992] |
Thu, 14 November 2013 05:59 |
pablolee
Messages: 2882
Registered: May 2007
Location: Scotland
|
Senior Member |
|
|
|
Irrelevant. The fact is that you have values in that column that do not fit an acceptable patter for the date conversion. That is the issue. that is what needs to be fixed.
|
|
|
|
| Re: Date comparison issue [message #600995 is a reply to message #600984] |
Thu, 14 November 2013 06:15 |
cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
And I already pointed that out:
cookiemonster wrote on Thu, 14 November 2013 11:37You assume that oracle checks this:
AND GI_SYS_ID <= 11164617
before this:
AND To_Date(GI_FLEX_02, 'DD/MM/RRRR') >= To_Date('01/01/2000','DD/MM/RRRR');
it doesn't have to, it can do the date check first, in which case any row in the table could be the problem.
|
|
|
|
| Re: Date comparison issue [message #600996 is a reply to message #600993] |
Thu, 14 November 2013 06:16 |
chat2raj.s
Messages: 161
Registered: October 2010
Location: Chennai, India
|
Senior Member |
|
|
Yes i accept, the data in that column need not be always in DDMMRRRR format. It may be null or some characters too.
But when i use the query, i filter the data based on few other conditions (say company code) which will have data only in DDMMRRRR format and i have verified the same.
Now my doubt is, when a query contains multiple conditions then how the final subset data is arrived. If oracle picks the date comparision condition first then it will fail with that error as few records have null data. But if it also applies other condition before applying the data comparision condition then it should not fail as the data is perfect.
To confirm this, i changed the query to first get subset data with other conditions and used an outer query on this subset to compare the dates and it works perfect.
Have we reached the problem!!
|
|
|
|
| Re: Date comparison issue [message #600997 is a reply to message #600996] |
Thu, 14 November 2013 06:21 |
cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
chat2rajsgmailcom wrote on Thu, 14 November 2013 12:16Yes i accept, the data in that column need not be always in DDMMRRRR format. It may be null or some characters too.
Null isn't a problem, to_date(null) won't error, other characters that don't match the format mask are the problem.
chat2rajsgmailcom wrote on Thu, 14 November 2013 12:16
Now my doubt is, when a query contains multiple conditions then how the final subset data is arrived. If oracle picks the date comparision condition first then it will fail with that error as few records have null data. But if it also applies other condition before applying the data comparision condition then it should not fail as the data is perfect.
As we keep saying, oracle can evaluate the where clause in any order it chooses.
The correct solution is to fix the corrupt data, or better yet change the columns datatype to date.
|
|
|
|
| Re: Date comparison issue [message #600999 is a reply to message #600996] |
Thu, 14 November 2013 06:35 |
pablolee
Messages: 2882
Registered: May 2007
Location: Scotland
|
Senior Member |
|
|
chat2rajsgmailcom wrote on Thu, 14 November 2013 12:16i filter the data based on few other conditions (say company code) which will have data only in DDMMRRRR format and i have verified the same. Just to emphasise, what has already been said (on more than one occasion in this thread. Irrelevant Oracle may still try to evaluate the rows with the invalid data.
Quote:
To confirm this, i changed the query to first get subset data with other conditions and used an outer query on this subset to compare the dates and it works perfect.
That would have been my suggestion as a halfway house i.e. you have a tactical, short term solution, you're halfway to getting a proper, Strategic solution. the main issue is that data is being stored incorrectly, and until that is fixed, you do not have a proper solution. In addition, all the workarounds that you will need to build between now, and the time that the strategic solution is implemented will be less efficient and more costly, and will probably have to be changed when that solution is applied.
get the data storage issue sorted, as a matter of urgency.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
