I am getting the error in the below sql:
ORA-01858: a non-numeric character was found where a numeric was expected
SELECT c.contract_num,
CASE
WHEN ( MAX (TO_CHAR (TO_DATE (c.event_dt, 'YYYY-MM-DD'), 'MMDD'))
- MIN (TO_CHAR (TO_DATE (c.event_dt, 'YYYY-MM-DD'), 'MMDD')))
/ COUNT (c.event_occurrence) < 32
THEN
'Monthly'
WHEN ( MAX (
TO_CHAR (TO_DATE (c.event_dt, 'YYYY-MM-DD'), 'MMDD'))
- MIN (
TO_CHAR (TO_DATE (c.event_dt, 'YYYY-MM-DD'), 'MMDD')))
/ COUNT (c.event_occurrence) >= 32
AND ( MAX (
TO_CHAR (TO_DATE (c.event_dt, 'YYYY-MM-DD'), 'MMDD'))
- MIN (
TO_CHAR (TO_DATE (c.event_dt, 'YYYY-MM-DD'), 'MMDD')))
/ COUNT (c.event_occurrence) < 91
THEN
'Quarterley'
ELSE
'Yearly'
END
FROM ps_ca_bp_events c
GROUP BY c.contract_num;
This question is related to
oracle
I added TO_DATE
and it resolved issue.
Before modification - due to below condition i got this error
record_update_dt>='05-May-2017'
After modification - after adding to_date
, issue got resolved.
record_update_dt>=to_date('05-May-2017','DD-Mon-YYYY')
You can solve the problem by checking if your date matches a REGEX pattern. If not, then NULL (or something else you prefer).
In my particular case it was necessary because I have >20 DATE columns saved as CHAR, so I don't know from which column the error is coming from.
Returning to your query:
1. Declare a REGEX pattern.
It is usually a very long string which will certainly pollute your code (you may want to reuse it as well).
define REGEX_DATE = "'your regex pattern goes here'"
Don't forget a single quote inside a double quote around your Regex :-)
A comprehensive thread about Regex date validation you'll find here.
2. Use it as the first CASE condition:
To use Regex validation in the SELECT
statement, you cannot use REGEXP_LIKE
(it's only valid in WHERE
. It took me a long time to understand why my code was not working. So it's certainly worth a note.
Instead, use REGEXP_INSTR
For entries not found in the pattern (your case) use REGEXP_INSTR (variable, pattern) = 0
.
DEFINE REGEX_DATE = "'your regex pattern goes here'"
SELECT c.contract_num,
CASE
WHEN REGEXP_INSTR(c.event_dt, ®EX_DATE) = 0 THEN NULL
WHEN ( MAX (TO_CHAR (TO_DATE (c.event_dt, 'YYYY-MM-DD'), 'MMDD'))
- MIN (TO_CHAR (TO_DATE (c.event_dt, 'YYYY-MM-DD'), 'MMDD')))
/ COUNT (c.event_occurrence) < 32
THEN
'Monthly'
WHEN ( MAX (
TO_CHAR (TO_DATE (c.event_dt, 'YYYY-MM-DD'), 'MMDD'))
- MIN (
TO_CHAR (TO_DATE (c.event_dt, 'YYYY-MM-DD'), 'MMDD')))
/ COUNT (c.event_occurrence) >= 32
AND ( MAX (
TO_CHAR (TO_DATE (c.event_dt, 'YYYY-MM-DD'), 'MMDD'))
- MIN (
TO_CHAR (TO_DATE (c.event_dt, 'YYYY-MM-DD'), 'MMDD')))
/ COUNT (c.event_occurrence) < 91
THEN
'Quarterley'
ELSE
'Yearly'
END
FROM ps_ca_bp_events c
GROUP BY c.contract_num;
This error can come not only because of the Date conversions
This error can come when we try to pass date whereas varchar is expected
or
when we try to pass varchar whereas date is expected.
Use to_char(sysdate,'YYYY-MM-DD') when varchar is expected
Source: Stackoverflow.com