title: ORA-00932: inconsistent datatypes: expected CHAR got NUMBER while adding 1 to a date
body: <p>Probably a silly mistake, but I couldn't figure this out myself. When I run this query in Oracle 11g. </p>
  
  <p>If this question is answered in SO, please let me know the link.</p>
  
  <pre><code>with LAST_BUSINESS_DAY as (select DECODE(to_char(last_day(to_date('29-mar-2013')), 'D'), 
                                    , '7', to_char((last_day('29-mar-2013') - 1), 'DD-MON-YYYY')
                                    , '1', to_char((last_day('29-mar-2013') - 2), 'DD-MON-YYYY')
                                    , to_char(last_day('29-apr-2013'), 'DD-MON-YYYY')) as LAST_BD from dual),
        HOLIDAYS as (select distinct rpt_day
                       from rpt_days rpt left join
                            calendars cal on rpt.calendar_id = cal.calendar_id
                      where rpt.type = 2 
                        and cal.group = 4)
    select case when to_char(to_date(LAST_BD, 'DD-MON-YYYY'), 'D') is null
                then LAST_BD
                else DECODE(to_char(to_date(LAST_BD, 'DD-MON-YYYY') , 'D')
                            , '6', LAST_BD
                            , '2', LAST_BD
                            , LAST_BD)
           end as LAST_BD_OF_MONTH
      from LAST_BUSINESS_DAY LBD 
           inner join HOLIDAYS H on LBD.LAST_BD = H.rpt_day
  </code></pre>
  
  <p>I get the result as </p>
  
  <pre><code>LAST_BD_OF_MONTH
  ===================
  29-MAR-2013
  </code></pre>
  
  <p>Now, when I try to add a day to the <code>LAST_BD</code> date, it throws an error.</p>
  
  <pre><code>with LAST_BUSINESS_DAY as (select DECODE(to_char(last_day(to_date('29-mar-2013')), 'D'), 
                                    , '7', to_char((last_day('29-mar-2013') - 1), 'DD-MON-YYYY')
                                    , '1', to_char((last_day('29-mar-2013') - 2), 'DD-MON-YYYY')
                                    , to_char(last_day('29-apr-2013'), 'DD-MON-YYYY')) as LAST_BD from dual),
        HOLIDAYS as (select distinct rpt_day
                       from rpt_days rpt left join
                            calendars cal on rpt.calendar_id = cal.calendar_id
                      where rpt.type = 2 
                        and cal.group = 4)
    select case when to_char(to_date(LAST_BD, 'DD-MON-YYYY'), 'D') is null
                then LAST_BD
                else DECODE(to_char(to_date(LAST_BD, 'DD-MON-YYYY') , 'D') -- line 35
                            , '6', LAST_BD - 1 -- CHANGED THIS
                            , '2', LAST_BD + 1 -- CHANGED THIS
                            , LAST_BD)
           end as LAST_BD_OF_MONTH
      from LAST_BUSINESS_DAY LBD 
           inner join HOLIDAYS H on LBD.LAST_BD = H.rpt_day
  </code></pre>
  
  <p><strong>Error Message</strong></p>
  
  <blockquote>
    <p>ORA-00932: inconsistent datatypes: expected CHAR got NUMBER<br>
    00932. 00000 -  "inconsistent datatypes: expected %s got %s"
    *Cause:<br>
    *Action: Error at Line: 35 Column: 20</p>
  </blockquote>
  
  <p>As I said, this might be a simple overlook from my side. I tried converting the <code>LAST_BD</code> to a <code>date</code>, but didn't work.</p>
  
  <p>I tried changing the <code>DECODE</code> as below</p>
  
  <pre><code>case when to_char(to_date(LAST_BD, 'DD-MON-YYYY'), 'D') is null
       then LAST_BD
       else DECODE(to_char(to_date(LAST_BD, 'DD-MON-YYYY') , 'D')
                   , '6', to_date(LAST_BD, 'DD-MON-YYYY') - 1
                   , '2', LAST_BD + 1 -- line 37
                   , LAST_BD)
  end as LAST_BD_OF_MONTH
  </code></pre>
  
  <p>and got this error :</p>
  
  <blockquote>
    <p>ORA-00932: inconsistent datatypes: expected DATE got NUMBER<br>
    00932. 00000 -  "inconsistent datatypes: expected %s got %s"
    *Cause:<br>
    *Action: Error at Line: 37 Column: 42</p>
  </blockquote>
  
  <p>So, I changed the  <code>line 37</code> to this, </p>
  
  <pre><code>case when to_char(to_date(LAST_BD, 'DD-MON-YYYY'), 'D') is null
       then LAST_BD
       else DECODE(to_char(to_date(LAST_BD, 'DD-MON-YYYY') , 'D')
                  , '6', to_date(LAST_BD, 'DD-MON-YYYY') - 1
                  , '2', to_date(LAST_BD, 'DD-MON-YYYY') + 1
                  , LAST_BD)
  end as LAST_BD_OF_MONTH
  </code></pre>
  
  <p>and this time its a different message.</p>
  
  <blockquote>
    <p>ORA-00932: inconsistent datatypes: expected CHAR got DATE<br>
    00932. 00000 -  "inconsistent datatypes: expected %s got %s"
    *Cause:<br>
    *Action: Error at Line: 35 Column: 20</p>
  </blockquote>
  
  <p>Any help to get this corrected is greatly appreciated.</p>
  
  <p><strong>ANSWER</strong> : </p>
  
  <pre><code>with LAST_BUSINESS_DAY as (select DECODE(to_char(last_day(to_date('29-mar-2013')), 'D'), 
                                    , '7', to_char((last_day('29-mar-2013') - 1), 'DD-MON-YYYY')
                                    , '1', to_char((last_day('29-mar-2013') - 2), 'DD-MON-YYYY')
                                    , to_char(last_day('29-apr-2013'), 'DD-MON-YYYY')) as LAST_BD from dual),
        HOLIDAYS as (select distinct rpt_day
                       from rpt_days rpt left join
                            calendars cal on rpt.calendar_id = cal.calendar_id
                      where rpt.type = 2 
                        and cal.group = 4)
    select case when to_char(to_date(LAST_BD, 'DD-MON-YYYY'), 'D') is null
                then LAST_BD
                else DECODE(to_char(to_date(LAST_BD, 'DD-MON-YYYY') , 'D')
                            , '6', to_char (to_date(LAST_BD, 'DD-MON-YYYY') - 1, 'DD-MON-YYYY')
                            , '2', to_char (to_date(LAST_BD, 'DD-MON-YYYY') + 1, 'DD-MON-YYYY')
                            , LAST_BD)
           end as LAST_BD_OF_MONTH
      from LAST_BUSINESS_DAY LBD 
           inner join HOLIDAYS H on LBD.LAST_BD = H.rpt_day
  </code></pre>