CODEWITHSUNDEEP
sqloracle-database
Michelle
Michelle

Reputation: 11

ORA-01858 error using dates

I am not a programmer by trade but I know some SQL. I just need another set of eyes on my code because I am not sure why I am getting this error.

select  
count(1) AH 
           from regmdr.contact_interaction ci 
           join regmdr.source_data sd on ci.sd_id = sd.sd_id
           join regmdr.account_contact ac on ci.acct_cont_id = ac.acct_cont_id
           join regmdr.account acc on ac.acc_id = acc.acc_id
           where sd.sd_user_type in ('1','2')
           and sd.sd_origin_reference = 'www.alliancehealth.com' 
           and ci.ci_create_date in (select case when ci.ci_create_date between to_date('1/1/2016','mm/dd/yyyy') and to_date('1/31/2016','mm/dd/yyyy') then 'January'
                                             when ci.ci_create_date between to_date('2/1/2016','mm/dd/yyyy') and to_date('2/29/2016','mm/dd/yyyy') then 'February'
                                             when ci.ci_create_date between to_date('3/1/2016','mm/dd/yyyy') and to_date('3/31/2016','mm/dd/yyyy')  then 'March'
                  else '' end from regmdr.contact_interaction ci           group by to_char(ci.ci_create_date, 'yyyy-mm')

Upvotes: 1

Views: 103

Answers (2)

Alex Poole
Alex Poole

Reputation: 191570

You are trying to compare the ci.ci_create_date date value with the string that is produced by your case statement, which is January, February, March or null. So you're effectively doing a comparison like:

ci.ci_create_date = to_date('January')

Unless your NLS_DATE_FORMAT is 'Month' and your language is English, that will get the ORA-01858. You could convert the left-hand side of that to the month name too, but without the year in either format model that would include data from January in any year; and it's better for performance to not convert the data from the table if you can avoid it.

It isn't entirely clear what you're trying to do but as the subquery has an independent view of contact_interaction with no correlation it probably isn't going to do whatever you are trying anyway.

If you're trying to count values from the first three months of this year then you can do:

select count(1) AH 
from regmdr.contact_interaction ci 
join regmdr.source_data sd on ci.sd_id = sd.sd_id
join regmdr.account_contact ac on ci.acct_cont_id = ac.acct_cont_id
join regmdr.account acc on ac.acc_id = acc.acc_id
where sd.sd_user_type in ('1','2')
and sd.sd_origin_reference = 'www.alliancehealth.com' 
and ci.ci_create_date >= date '2016-01-01'
and ci.ci_create_date < date '2016-04-01'

Which will give you a single number. If you want it by month your group by was in the wrong place, and you can add

...
group by trunc(ci.ci_create_date, 'MM')

although you really need that in the select list too for the result set to make any sense - so you know which month each count belongs to.

Based on using the month names at all, perhaps you wanted those in the select list:

select to_char(trunc(ci.ci_created_date, 'MM'), 'Month') as month,
  count(1) as AH 
from regmdr.contact_interaction ci 
...
group by trunc(ci.ci_create_date, 'MM')

... but I'm speculating even more now. Also be aware that month names are sensitive to your NLS settings, particularly NLS_DATE_LANGUAGE. You can force them to always be in English via the optional third argument to to_char() though, e.g.

select to_char(trunc(ci.ci_created_date, 'MM'), 'Month', 'NLS_DATE_LANGUAGE=ENGLISH')
...

Upvotes: 3

T Gray
T Gray

Reputation: 712

Why not go with:

select to_char(ci.ci_create_date, 'YYYY-MM') monthyear,
       count(1) AH
from regmdr.contact_interaction ci
join regmdr.source_data sd on ci.sd_id = sd.sd_id
join regmdr.account_contact ac on ci.acct_cont_id = ac.acct_cont_id
join regmdr.account acc on ac.acc_id = acc.acc_id
where sd.sd_user_type in ('1','2')
and sd.sd_origin_reference = 'www.alliancehealth.com'
and to_char(ci.ci_create_date, 'MON' in ('JAN', 'FEB', 'MAR')
group by to_char(ci.ci_create_date, 'yyyy-mm';

If you are only interested in the counts (without context), wrap it in an outside select statement:

select AH
from (select to_char(ci.ci_create_date, 'YYYY-MM') monthyear,
            count(1) AH 
      from regmdr.contact_interaction ci
      join regmdr.source_data sd on ci.sd_id = sd.sd_id
      join regmdr.account_contact ac on ci.acct_cont_id = ac.acct_cont_id
      join regmdr.account acc on ac.acc_id = acc.acc_id
      where sd.sd_user_type in ('1','2')
      and sd.sd_origin_reference = 'www.alliancehealth.com'
      and to_char(ci.ci_create_date, 'MON') in ('JAN', 'FEB', 'MAR')
      group by to_char(ci.ci_create_date, 'yyyy-mm'
);

Upvotes: 0

Related Questions