Forum Stats

  • 3,728,033 Users
  • 2,245,522 Discussions
  • 7,853,253 Comments

Discussions

not a valid month error

user13168644
user13168644 Member Posts: 267 Blue Ribbon
edited November 2014 in SQL & PL/SQL

HI,

I have oracle 12.1.0.2 database enterprise running on a linux server. I have two tables , one stores the month names and another stores the year numbers. Here is the data for the first table

select  FISCAL_SYSTEM_PERIOD_CAL_YR_EQ, CALENDRICAL_EQUIVALENT

from  fiscal_system_periods  order by 2

FISCAL_SYSTEM_PERIOD_CAL_YR_EQ CALENDRICAL_EQUIVALENT   

---------------------------------  -------------------------

January                        1                   
February                       2                   
March                          3                   
April                          4                   
May                            5                   
June                           6                   
July                           7                   
August                         8                   
September                      9                   
October                        10                  
November                       11                  
December                       12                  
13 Series                     

(null)

when I run the following query i am getting  the invalid month error

select  FISCAL_SYSTEM_PERIOD_CAL_YR_EQ, calendrical_equivalent

from  fiscal_system_periods 

where  calendrical_equivalent > 0 and

to_date(FISCAL_SYSTEM_PERIOD_CAL_YR_EQ || ', ' || 2014,'Month, YYYY') >= to_date('November, 2014','Month, YYYY')

[Error] Script lines: 1-5 --------------------------

ORA-01843: not a valid month 

and the data types are

CALENDRICAL_EQUIVALENT     NUMBER

FISCAL_SYSTEM_PERIOD_CAL_YR_EQ VARCHAR2

but if i run the following query,  i am not getting any error

with

periods as  (Select 'January' period, 1 cal  From Dual                          

union all Select 'February' period , 2 cal From Dual                         

union all Select 'March' period , 3 cal From Dual                            

union all Select 'April' period , 4 cal From Dual                            

union all Select 'May'  period, 5 cal From Dual                              

union all Select 'June' period, 6 cal From Dual                             

union all Select 'July' period, 7 cal From Dual                             

union all Select 'August' period, 8 cal From Dual                           

union all Select 'September' period, 9 cal  From Dual                        

union all Select 'October' period, 10 cal From Dual                          

union all Select 'November' period, 11 cal From Dual                         

union all Select 'December' period, 12 cal From Dual                         

union all Select '13 Series' period, null cal  From Dual  )

select  to_date(periods.period ||', '|| 2014,'Month, YYYY') period from periods where periods.cal > 0 and  to_date(periods.period ||', '|| 2014,'Month, YYYY') = to_date('November, 2014','Month, YYYY')

i get the result

PERIOD          

---------------------

11/1/2014 12:00:00 AM

I upgraded the DB today to 12.1.0.2 from 11.2.0. yesterday . Any one knows why this started happening ?

Thanks

jgarry

Answers

  • Unknown
    edited November 2014

    post results from below

    DESC fiscal_system_periods



    > I have two tables , one stores the month names and another stores the year numbers.

    BTW above is waste of disk space.

  • user13168644
    user13168644 Member Posts: 267 Blue Ribbon

    SQL> DESC fiscal_system_periods

    Name                                                  Null?    Type

    ----------------------------------------------------- -------- ------------

    --------------------

    FISCAL_SYSTEM_PERIOD_ID                               NOT NULL NUMBER(18)

    FISCAL_SYSTEM_PERIOD_DESIG                            NOT NULL VARCHAR2(5)

    FISCAL_SYSTEM_PERIOD_CFY_ID                           NOT NULL NUMBER(18)

    FISCAL_SYSTEM_PERIOD_CAL_YR_EQ                        NOT NULL VARCHAR2(20)

    CALENDRICAL_EQUIVALENT                                         NUMBER(10)

    FISCAL_SYSTEM_PERIOD_ORDER                                     NUMBER(10)

  • Unknown
    edited November 2014

    do you see where your problem is from example below?

    SQL> select distinct to_char(hiredate,'Month') ||'<--' Month from emp order by 1;

    MONTH

    ---------------------------------------

    April    <--

    December <--

    February <--

    January  <--

    June     <--

    May      <--

    November <--

    September<--

    8 rows selected.

    Using month name is sub-optimal approach since depends upon NLS_LANG setting.

    It is ALWAYS better to use month number instead of month name.

  • Deepak Mahto
    Deepak Mahto Member Posts: 117

    Let me post my observations!

    Create Table T
    As
    with
    periods as  (Select 'January' period, 1 cal  From Dual                         
    union all Select 'February' period , 2 cal From Dual                       
    union all Select 'March' period , 3 cal From Dual                           
    union all Select 'April' period , 4 cal From Dual                           
    union all Select 'May'  period, 5 cal From Dual                             
    union all Select 'June' period, 6 cal From Dual                           
    union all Select 'July' period, 7 cal From Dual                           
    union all Select 'August' period, 8 cal From Dual                         
    union all Select 'September' period, 9 cal  From Dual                       
    union all Select 'October' period, 10 cal From Dual                         
    union all Select 'November' period, 11 cal From Dual                       
    Union All Select 'December' Period, 12 Cal From Dual                       
    Union All Select '13 Series' Period, Null Cal  From Dual)
    Select  Period,Cal
    from periods;
    

    When we invoke below SQL it work fine.

    select * from (select  Period, Cal,to_date(Period || ', ' || 2014,'Month, YYYY') col1 ,to_date('November, 2014','Month, YYYY') col2
    From  T
    Where  Cal > 0 );
    
    Output :
    
    PERIOD           CAL COL1     COL2   
    --------- ---------- -------- --------
    January            1 01-01-14 01-11-14 
    February           2 01-02-14 01-11-14 
    March              3 01-03-14 01-11-14 
    April              4 01-04-14 01-11-14 
    May                5 01-05-14 01-11-14 
    June               6 01-06-14 01-11-14 
    July               7 01-07-14 01-11-14 
    August             8 01-08-14 01-11-14 
    September          9 01-09-14 01-11-14 
    October           10 01-10-14 01-11-14 
    November          11 01-11-14 01-11-14 
    December          12 01-12-14 01-11-14
    
    But when we add comparison operations , it error out
    
    select * from (select  Period, Cal,to_date(Period || ', ' || 2014,'Month, YYYY') col1 ,to_date('November, 2014','Month, YYYY') col2
    From  T
    Where  Cal > 0 ) where col1 >= col2;
    
    ORA-01843: not a valid month
    01843. 00000 -  "not a valid month"
    *Cause:   
    *Action:
    

    Still not sure what the problem.... it is in same context as questions posted!!

  • user13168644
    user13168644 Member Posts: 267 Blue Ribbon

    Hi 2795339 ( Sorry , didn't know your name),

    So , why is it errors out in the second instance  i.e. when the comparison operation is added .

    Thanks

  • post results from SQL below


    SELECT fiscal_system_period_cal_yr_eq,

           calendrical_equivalent,

           Length(fiscal_system_period_cal_yr_eq)

    FROM   fiscal_system_periods

    ORDER  BY 2;

  • Deepak Mahto
    Deepak Mahto Member Posts: 117
    edited November 2014

    Buddy even I am not aware!!

    I observe what while conversion on projections it is working fine..

    but while comparison it fails!

    lets waits... expert will help us out!!

  • user13168644
    user13168644 Member Posts: 267 Blue Ribbon
    FISCAL_SYSTEM_PERIOD_CAL_YR_EQ CALENDRICAL_EQUIVALENT LENGTH(FISCAL_SYSTEM_PERIOD_CAL_YR_EQ)   

    ---------------------------------  -------------------------  -----------------------------------------

    January                        1                      7                                   
    February                       2                      8                                   
    March                          3                      5                                   
    April                          4                      5                                   
    May                            5                      3                                   
    June                           6                      4                                   
    July                           7                      4                                   
    August                         8                      6                                   
    September                      9                      9                                   
    October                        10                     7                                   
    November                       11                     8                                   
    December                       12                     8                                   
    13 Series                      (null)                
  • ddf_dba
    ddf_dba Member Posts: 1,398 Bronze Trophy
    user13168644 wrote:
    
    HI,
    I have oracle 12.1.0.2 database enterprise running on a linux server. I have two tables , one stores the month names and another stores the year numbers. Here is the data for the first table
    
    select  FISCAL_SYSTEM_PERIOD_CAL_YR_EQ, CALENDRICAL_EQUIVALENT
    from  fiscal_system_periods  order by 2
    
    
    
    
    FISCAL_SYSTEM_PERIOD_CAL_YR_EQ
    CALENDRICAL_EQUIVALENT 
    
    
    
    ---------------------------------  -------------------------
    
    
    
    January                     
    1                 
    
    
    
    February                     
    2                 
    
    
    
    March                       
    3                 
    
    
    
    April                       
    4                 
    
    
    
    May                         
    5                 
    
    
    
    June                         
    6                 
    
    
    
    July                         
    7                 
    
    
    
    August                       
    8                 
    
    
    
    September                   
    9                 
    
    
    
    October                     
    10                 
    
    
    
    November                     
    11                 
    
    
    
    December                     
    12                 
    
    
    
    13 Series                   
    
    (null)
    
    
    
    
    
    
    when I run the following query i am getting  the invalid month error
    
    select  FISCAL_SYSTEM_PERIOD_CAL_YR_EQ, calendrical_equivalent
    from  fiscal_system_periods
    where  calendrical_equivalent > 0 and
    to_date(FISCAL_SYSTEM_PERIOD_CAL_YR_EQ || ', ' || 2014,'Month, YYYY') >= to_date('November, 2014','Month, YYYY')
    
    [Error] Script lines: 1-5 --------------------------
    ORA-01843: not a valid month
    
    and the data types are
    
    
    
    
    CALENDRICAL_EQUIVALENT   
    NUMBER
    
    
    
    FISCAL_SYSTEM_PERIOD_CAL_YR_EQ VARCHAR2
    
    
    but if i run the following query,  i am not getting any error
    
    with
    periods as  (Select 'January' period, 1 cal  From Dual                         
    union all Select 'February' period , 2 cal From Dual                       
    union all Select 'March' period , 3 cal From Dual                           
    union all Select 'April' period , 4 cal From Dual                           
    union all Select 'May'  period, 5 cal From Dual                             
    union all Select 'June' period, 6 cal From Dual                           
    union all Select 'July' period, 7 cal From Dual                           
    union all Select 'August' period, 8 cal From Dual                         
    union all Select 'September' period, 9 cal  From Dual                       
    union all Select 'October' period, 10 cal From Dual                         
    union all Select 'November' period, 11 cal From Dual                       
    union all Select 'December' period, 12 cal From Dual                       
    union all Select '13 Series' period, null cal  From Dual  )
    
    select  to_date(periods.period ||', '|| 2014,'Month, YYYY') period from periods where periods.cal > 0 and  to_date(periods.period ||', '|| 2014,'Month, YYYY') = to_date('November, 2014','Month, YYYY')
    
    
    i get the result
    
    
    
    
    PERIOD         
    
    
    
    
    ---------------------
    11/1/2014 12:00:00 AM
    
    
    
    
    I upgraded the DB today to 12.1.0.2 from 11.2.0. yesterday . Any one knows why this started happening ?
    Thanks
    
    

    No, because I am running 12.1.0.2 (on Windows) and your query works without modification:

    SQL> select * from v$version;

    BANNER                                                                               CON_ID
    -------------------------------------------------------------------------------- ----------
    Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production              0
    PL/SQL Release 12.1.0.2.0 - Production                                                    0
    CORE    12.1.0.2.0      Production                                                                0
    TNS for 64-bit Windows: Version 12.1.0.2.0 - Production                                   0
    NLSRTL Version 12.1.0.2.0 - Production                                                    0

    SQL>
    SQL> create table fiscal_system_periods(
      2          fiscal_system_period_cal_yr_eq varchar2(12) not null,
      3          calendrical_equivalent number);

    Table created.

    SQL>
    SQL>
    SQL> insert all
      2  into fiscal_system_periods(FISCAL_SYSTEM_PERIOD_CAL_YR_EQ,CALENDRICAL_EQUIVALENT)
      3  values('January',1)
      4  into fiscal_system_periods(FISCAL_SYSTEM_PERIOD_CAL_YR_EQ,CALENDRICAL_EQUIVALENT)
      5  values('February',2)
      6  into fiscal_system_periods(FISCAL_SYSTEM_PERIOD_CAL_YR_EQ,CALENDRICAL_EQUIVALENT)
      7  values('March',3)
      8  into fiscal_system_periods(FISCAL_SYSTEM_PERIOD_CAL_YR_EQ,CALENDRICAL_EQUIVALENT)
      9  values('April',4)
    10  into fiscal_system_periods(FISCAL_SYSTEM_PERIOD_CAL_YR_EQ,CALENDRICAL_EQUIVALENT)
    11  values('May',5)
    12  into fiscal_system_periods(FISCAL_SYSTEM_PERIOD_CAL_YR_EQ,CALENDRICAL_EQUIVALENT)
    13  values('June',6)
    14  into fiscal_system_periods(FISCAL_SYSTEM_PERIOD_CAL_YR_EQ,CALENDRICAL_EQUIVALENT)
    15  values('July',7)
    16  into fiscal_system_periods(FISCAL_SYSTEM_PERIOD_CAL_YR_EQ,CALENDRICAL_EQUIVALENT)
    17  values('August',8)
    18  into fiscal_system_periods(FISCAL_SYSTEM_PERIOD_CAL_YR_EQ,CALENDRICAL_EQUIVALENT)
    19  values('September',9)
    20  into fiscal_system_periods(FISCAL_SYSTEM_PERIOD_CAL_YR_EQ,CALENDRICAL_EQUIVALENT)
    21  values('October',10)
    22  into fiscal_system_periods(FISCAL_SYSTEM_PERIOD_CAL_YR_EQ,CALENDRICAL_EQUIVALENT)
    23  values('November',11)
    24  into fiscal_system_periods(FISCAL_SYSTEM_PERIOD_CAL_YR_EQ,CALENDRICAL_EQUIVALENT)
    25  values('December',12)
    26  into fiscal_system_periods(FISCAL_SYSTEM_PERIOD_CAL_YR_EQ,CALENDRICAL_EQUIVALENT)
    27  values('13 Series',null)
    28  select * From dual;

    13 rows created.

    SQL>
    SQL> commit;

    Commit complete.

    SQL>
    SQL> select     FISCAL_SYSTEM_PERIOD_CAL_YR_EQ, calendrical_equivalent
      2  from  fiscal_system_periods
      3  where  calendrical_equivalent > 0 and
      4  to_date(FISCAL_SYSTEM_PERIOD_CAL_YR_EQ || ', ' || 2014,'Month, YYYY') >= to_date('November, 2014','Month, YYYY');

    FISCAL_SYSTE CALENDRICAL_EQUIVALENT
    ------------ ----------------------
    November                         11
    December                         12

    SQL>

    Have you checked your data for invalid characters?

    David Fitzjarrell

  • jgarry
    jgarry Member Posts: 13,842

    A guess:

    add to where: calendrical_equivalent is not null

  • user13168644
    user13168644 Member Posts: 267 Blue Ribbon

    HI Garry,

    Unfortunately, I cant use the calendrical_equivalent is not null, because the developers have used the similar where clause in many places ( not sure how many places and where )  I don't know where .

    Thanks

    Feroz

  • user13168644
    user13168644 Member Posts: 267 Blue Ribbon

    HI David,

    Can you please run the query with the table creation script listed by user 2795339 at 12:30 AM today on your DB and see if it works . Just for a testing purpose.

    Thanks

  • ADG76
    ADG76 Member Posts: 18
    edited November 2014

    Looks like a version specific problem. I tried same query using your data in Oracle 11g R2 with following details as I dont have 12C available with me

    BANNER
    Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
    PL/SQL Release 11.2.0.2.0 - Production
    CORE 11.2.0.2.0 Production
    TNS for Linux: Version 11.2.0.2.0 - Production
    NLSRTL Version 11.2.0.2.0 - Production

    The query worked fine and returned the following result set.

    FISCAL_SYSTEM_PERIOD_CAL_YR_EQCALENDRICAL_EQUIVALENT
    November11
    December12
  • Hemant K Chitale
    Hemant K Chitale Member Posts: 15,753 Blue Diamond

    It depends on the order in which the two ANDed predicates are evaluated.

    If the predicate "calendrical_equivalent > 0" is evaulated first, the row for "13 series" is excluded.  So it need not consider that row for the second predicate.

    If the predicate "to_date(FISCAL_SYSTEM_PERIOD_CAL_YR_EQ || ', ' || 2014,'Month, YYYY') >= to_date('November, 2014','Month, YYYY')" is evaluated first, it obviously fails with ORA-01843 error for the "13 series" row.

    Oracle may choose to evaluate any of the two predicates first when you AND them.

    SQL>create table fiscal_system_periods(

      2              fiscal_system_period_cal_yr_eq varchar2(12) not null,

      3              calendrical_equivalent number)

      4  /

    Table created.

    SQL>insert all

      2    into fiscal_system_periods(FISCAL_SYSTEM_PERIOD_CAL_YR_EQ,CALENDRICAL_EQUIVALENT)

      3    values('January',1)

      4    into fiscal_system_periods(FISCAL_SYSTEM_PERIOD_CAL_YR_EQ,CALENDRICAL_EQUIVALENT)

      5    values('February',2)

      6    into fiscal_system_periods(FISCAL_SYSTEM_PERIOD_CAL_YR_EQ,CALENDRICAL_EQUIVALENT)

      7    values('March',3)

      8    into fiscal_system_periods(FISCAL_SYSTEM_PERIOD_CAL_YR_EQ,CALENDRICAL_EQUIVALENT)

      9    values('April',4)

    10    into fiscal_system_periods(FISCAL_SYSTEM_PERIOD_CAL_YR_EQ,CALENDRICAL_EQUIVALENT)

    11    values('May',5)

    12    into fiscal_system_periods(FISCAL_SYSTEM_PERIOD_CAL_YR_EQ,CALENDRICAL_EQUIVALENT)

    13    values('June',6)

    14    into fiscal_system_periods(FISCAL_SYSTEM_PERIOD_CAL_YR_EQ,CALENDRICAL_EQUIVALENT)

    15    values('July',7)

    16    into fiscal_system_periods(FISCAL_SYSTEM_PERIOD_CAL_YR_EQ,CALENDRICAL_EQUIVALENT)

    17    values('August',8)

    18    into fiscal_system_periods(FISCAL_SYSTEM_PERIOD_CAL_YR_EQ,CALENDRICAL_EQUIVALENT)

    19    values('September',9)

    20    into fiscal_system_periods(FISCAL_SYSTEM_PERIOD_CAL_YR_EQ,CALENDRICAL_EQUIVALENT)

    21    values('October',10)

    22    into fiscal_system_periods(FISCAL_SYSTEM_PERIOD_CAL_YR_EQ,CALENDRICAL_EQUIVALENT)

    23    values('November',11)

    24    into fiscal_system_periods(FISCAL_SYSTEM_PERIOD_CAL_YR_EQ,CALENDRICAL_EQUIVALENT)

    25    values('December',12)

    26    into fiscal_system_periods(FISCAL_SYSTEM_PERIOD_CAL_YR_EQ,CALENDRICAL_EQUIVALENT)

    27    values('13 Series',null)

    28    select * From dual

    29  /

    13 rows created.

    SQL>select    FISCAL_SYSTEM_PERIOD_CAL_YR_EQ, calendrical_equivalent

      2      from  fiscal_system_periods

      3      where  calendrical_equivalent > 0 and

      4      to_date(FISCAL_SYSTEM_PERIOD_CAL_YR_EQ || ', ' || 2014,'Month, YYYY') >= to_date('November, 2014','Month, YYYY')

      5  /

    FISCAL_SYSTE CALENDRICAL_EQUIVALENT

    ------------ ----------------------

    November                        11

    December                        12

    2 rows selected.

    SQL>select    FISCAL_SYSTEM_PERIOD_CAL_YR_EQ, calendrical_equivalent

      2      from  fiscal_system_periods

      3      where

      4      to_date(FISCAL_SYSTEM_PERIOD_CAL_YR_EQ || ', ' || 2014,'Month, YYYY') >= to_date('November, 2014','Month, YYYY')

      5  /

    ERROR:

    ORA-01843: not a valid month

    no rows selected

    SQL>select    FISCAL_SYSTEM_PERIOD_CAL_YR_EQ, calendrical_equivalent

      2      from  fiscal_system_periods

      3      where  calendrical_equivalent > 0

      4  /

    FISCAL_SYSTE CALENDRICAL_EQUIVALENT

    ------------ ----------------------

    January                          1

    February                          2

    March                            3

    April                            4

    May                              5

    June                              6

    July                              7

    August                            8

    September                        9

    October                          10

    November                        11

    December                        12

    12 rows selected.

    SQL>

    Hemant K Chitale

    jgarry
  • Deepak Mahto
    Deepak Mahto Member Posts: 117

    Thanks a lot Hemant sir for great explanations!!

    As you explain .. by Forcing below hint cause SQL to work fine by evaluating Cal > 0 first!!

    WITH ABC AS
    (select /*+ materialize*/ Period, Cal,to_date(Period || ', ' || 2014,'Month, YYYY') col1 ,to_date('November, 2014','Month, YYYY') col2 
    FROM  T 
    Where  Cal > 0 )
    SELECT * FROM ABC WHERE COL1 >= COL2; 
    
    PERIOD          CAL COL1    COL2 
    --------- ---------- -------- --------
    November          11 01-11-14 01-11-14
    December          12 01-12-14 01-11-14
    
    
  • jgarry
    jgarry Member Posts: 13,842

    It is pretty handy to know where all the source code is so you can search it.

  • user13168644
    user13168644 Member Posts: 267 Blue Ribbon

    Anab,

    The query was working absoultely fine in our 11.2.0.2 version of DB . The issue started when we migrated to 12.1.0.2.

    Regards.

  • jgarry
    jgarry Member Posts: 13,842

    That just goes to show, "working absolutely fine" does not mean "and won't break ever, even though we are improperly assuming deterministic behavior."

    Not so much dinging you, as everyone else who says "test it" when few can make a proper test.

This discussion has been closed.