CODEWITHSUNDEEP
sqloracle-databasedate
pedro
pedro

Reputation: 427

ORA-01847 - error while adding an uncorrelated AND clause

I'm trying to make a very simple query and I'm getty crazy over it cause I keep getting this error:

ora-01847 day of month must be between 1 and last day of month

This is the query

select *
  from TAB1 H 
  JOIN TAB2 D ON D.PAR2 = H.PAR1
  where TO_DATE(D.DATE, 'dd-mm-yyyy') <> sysdate
  and  H.FIELD = 'TEST';

What's weird is that if I take any of the two AND clause away, it'll work flowless. It will also work if, instead of using the last AND clause to retrieve some record , I pass their id directly so I don't think it's a problem of date format or anything else.

Both D.DATE and H.FIELD are defined as VARCHAR2(255 CHAR)

Any head up?

EDIT: [Actual dates that are causing issues]

01/10/2017
01/10/2018
01/10/2018
01/10/2017
01/05/2023
01/05/2023
01/05/2023
01/05/2023
01/05/2023
01/10/2017
01/05/2018
01/02/2018

!!!> While scrolling down to get this samples, it gave me that error again. I can only guess that it happened once arrived at a row with a 'TEST' value in the column

EDIT:

 select H.*
      from TAB1 H 
      JOIN TAB2 D ON D.PAR2 = H.PAR1
      where TO_DATE(D.DATE, 'dd-mm-yyyy') <> sysdate;

Will gave the same error. Eliminating the where clause or selecting everything will "solve" the problem though.

EDIT:

select ID as ID, null as ID_REF
    from TAB1
    where FIELD = 'TEST'
  union all
  select NULL as ID, ID_REF as ID_REF
  from TAB2
  where TO_DATE(DATE, 'dd-mm-yyyy') <> sysdate;

This, scrolling down (and allegedly reaching a record while the two conditions lik), will give the same error.

Upvotes: 0

Views: 258

Answers (1)

APC
APC

Reputation: 146329

The message for ORA-01847 is

day of month must be between 1 and last day of month

So the obvious candidate is 

> where TO_DATE(D.DATE, 'dd-mm-yyyy') <> sysdate

That is, you have values in D.DATE which contain strings that can't be cast to a date, such as '32-05-2017'. This is always a risk when we use a VARCHAR column to hold a date - or a number.

"I scroll through the result set, as soon as it load the 100+th results, it blows"

Yes, that was what my questions about fetching were driving at. Clearly you have duff data (non-dates) in the d.date column: filtering on h.field simply brought the duff to the top of the result set quicker than scrolling through the whole table did.

You can investigate the scale of the rot with a function like this:

create or replace function is_date
      (p_date_str in varchar2
        , p_date_fmt in varchar2 := 'dd-mm-yyyy' )
     return varchar2
as
    return_value varchar2(20);
    l_date date; 
begin
    begin
        l_date := to_date(p_date_str, p_dae_fmt);
        return_value := 'DATE';
    exception
        when others then
            return_value := 'NOT A DATE';
    end;
    return return_value;
end; 
/

This query will return the records you need to ponder:

select d.id -- or whatever
        , d.date
from tab2 d
where is_date(d.date) = 'NOT A DATE'
/

What you do next rather depends on the nature and quantity of the duffness you discover. That problem may well merit a new question.

Upvotes: 4

Related Questions