11 Replies Latest reply on Aug 13, 2012 3:30 PM by Frank Kulash

    how to avoid the error ORA-01843:

    Pavan
      hi all,

      i am facing problem with date column.

      when i am trying to do execute a query this works fine

      select null as link,'S'||to_char(DATE_OF_JOIN,'YY'),COUNT(STUDENT) "Blue Acres" from STUDENT_RECORD where PROCESSOR=1 and DATE_OF_JOIN >= to_date('0620' || to_char(sysdate, 'YYYY'), 'MMDDYYYY') and DATE_OF_JOIN < to_date('0920' || to_char(sysdate, 'YYYY'), 'MMDDYYYY') group by to_char(DATE_OF_JOIN,'YY')

      and when i am trying the below query its showing the only this year dates its not referring the previous years

      select null as link,'S'||to_char(DATE_OF_JOIN,'MMDDYY'),COUNT(STUDENT) "Blue Acres" from STUDENT_RECORD where PROCESSOR=1 and DATE_OF_JOIN >= to_date('1220' || to_char(sysdate, 'YYYY')-1, 'MMDDYYYY') and DATE_OF_JOIN < to_date('0320' || to_char(sysdate, 'YYYY'), 'MMDDYYYY') group by to_char(DATE_OF_JOIN,'MMDDYY')

      when i am trying below query its showng the error ORA-01843: not a valid month

      select null as link,'Fall'||to_char(to_char(DATE_OF_JOIN,'YY')-1),COUNT(STUDENT) "Blue Acres" from STUDENT_RECORD where PROCESSOR=1 and DATE_OF_JOIN >= to_date('0620' || to_char(sysdate, 'YYYY')-1, 'MMDDYYYY') and DATE_OF_JOIN < to_date('0920' || to_char(sysdate, 'YYYY')-1, 'MMDDYYYY') group by to_char(to_char(DATE_OF_JOIN,'YY')-1)
      can any help me regarding the problem pls

      Regards,
      Pavan
        • 1. Re: how to avoid the error ORA-01843:
          Karthick2003
          1. Tell us what you are trying to do. I mean your real problem for which the SQL is being used.

          2. Give the table structure

          3. Give Sample Data

          4. Mention DB Version

          5. Put code in formatted way.
          • 2. Re: how to avoid the error ORA-01843:
            jurgenk
            Or, to prevent just the error, put some extra braces:
             to_date('0920' || (to_char(sysdate, 'YYYY')-1), 'MMDDYYYY') 
            1 person found this helpful
            • 3. Re: how to avoid the error ORA-01843:
              chris227
              It is about Operator Precedence
              http://docs.oracle.com/cd/B10500_01/server.920/a96540/operators2.htm:
              "Oracle evaluates operators with equal precedence from left to right within an expression."
              so
              select '620' || to_char(sysdate, 'YYYY')- 1 r from dual
              
              evaluates to
              
              R
              6202011
              which doesnt match your date format MMDDYYYY anymore.

              One proper way is
              select to_date('0620' || to_char(sysdate, 'YYYY'), 'MMDDYYYY') - numtoyminterval(1, 'YEAR') r from dual
              
              R
              20.06.2011
              In fact it is about Operator Precedence a n d implicit type conversion. First the || (concatenation) is evaluated, resulting in varchar, then - (minus) resulting in number, lossing the leading zero.
              The reason jürgens exmaple works is, that first minus is evaluted, resulting in number and the the concat resulting in varchar preserving the leading zero.

              Regards

              Edited by: chris227 on 10.08.2012 03:57

              Edited by: chris227 on 10.08.2012 05:09
              • 4. Re: how to avoid the error ORA-01843:
                Frank Kulash
                Hi, Pavan,

                What are you trying to do?

                Are you trying to find rows where date_of_join is on or after June 20, but before September 20 of this year? If so:
                ...   date_of_join     >= TO_DATE ('0620', 'MMDD')
                AND   date_of_join     <  TO_DATE ('0920', 'MMDD')
                If you don't give a year in TO_DATE, it defaults to the current year.

                To find rows where date_of_join is on or after December 20 of last year, but before March 20 of this year:
                ...   date_of_join     >= ADD_MONTHS ( TO_DATE ('1220', 'MMDD')
                                                   , -12
                                          )
                AND   date_of_join     <               TO_DATE ('0320', 'MMDD')
                To find rows where date_of_join is on or after June 20, but before September 20 of last year:
                ...   date_of_join     >= ADD_MONTHS ( TO_DATE ('0620', 'MMDD')
                                                   , -12
                                          )
                AND   date_of_join     <  ADD_MONTHS ( TO_DATE ('0920', 'MMDD')
                                                   , -12
                                          )
                Oracle provides many good ways to manipulate DATEs as DATEs. There's hardly ever any reason to nest conversion functions, such as "TO_DATE ( TO_CHAR ...", or, even worse "TO_CHAR ( TO_CHAR ..."
                1 person found this helpful
                • 5. Re: how to avoid the error ORA-01843:
                  Pavan
                  hi all ,
                  i am using oracle 11g,
                  what i am trying is count number between the dates,

                  for example counting the number of students joined in every season,

                  STUDENT_RECORD (table)

                  DATE_OF_JOIN (join date)(DATE) STUDENT(Id)(NUMBER) PROCESSOR(like class)(NUMBER)

                  when i am referring above solutions i got the error reduce,
                  but i want calculate the result one row total but for the winter season is in between Dec 20th last year and Mar 20th this year for this season i got result like

                  LINK     'winter'||TO_CHAR(DATE_OF_SALE,'YY')      Blue Acres
                  -     Summer11     1234
                  -     Summer12     4910

                  can i know how to group by this thing,

                  Regards,
                  Pavan
                  • 6. Re: how to avoid the error ORA-01843:
                    Frank Kulash
                    Hi,
                    Pavan wrote:
                    hi all ,
                    i am using oracle 11g,
                    what i am trying is count number between the dates,

                    for example counting the number of students joined in every season,

                    STUDENT_RECORD (table)

                    DATE_OF_JOIN (join date)(DATE) STUDENT(Id)(NUMBER) PROCESSOR(like class)(NUMBER)

                    when i am referring above solutions i got the error reduce,
                    Sorry, I don't understand. What is "the error reduce"?
                    but i want calculate the result one row total but for the winter season is in between Dec 20th last year and Mar 20th this year for this season i got result like

                    LINK     'winter'||TO_CHAR(DATE_OF_SALE,'YY')      Blue Acres
                    -     Summer11     1234
                    -     Summer12     4910

                    can i know how to group by this thing,
                    Post the information theat Karthick requested in the first reply, 3 hours ago.

                    It's easy to group by quarters (that is, quarter years) in Oracle:
                    GROUP BY  TRUNC (date_column, 'Q')
                    The trouble is, that assumes that the quarters start on the 1st days of January, April, July and October.
                    It looks like you have a fiscal year, where the quarters start on the 20th days of March, June, September and December, that is, 2 months and 19 days after the calendar quarters start. You might be able to do something like:
                    GROUP BY  TRUNC ( ADD_MONTHS (date_column, -2) - 19
                                      , 'Q'
                              )
                    But it really depends on your data, and the results you want. Until you post CREATE TABLE and INSERT statements for a little sample data, and the results you want from that data, I can't help much.
                    • 7. Re: how to avoid the error ORA-01843:
                      Pavan
                      Hi all,

                      Thanks For your Replies..

                      But i am unable to get the correct requirement what i want ..


                      Here i am providing some more information which it may help you and me...

                      This is the table Structure which i am using


                      Column Name<a>                  </a>Data Type<a>        </a>Nullable<a>        </a>Default<a>        </a>Primary Key
                      SALE_ID<a>                           </a>NUMBER<a>             </a>No<a>              </a>-<a>                      </a>1
                      DATE_OF_SALE<a>                </a>DATE<a>                 </a>Yes<a>            </a> -<a>                      </a> -
                      PROCESSOR<a>                 </a>VARCHAR2(100)<a>   </a>Yes <a>            </a>-<a>                      </a> -
                      TOTAL_PIECES_SOLD<a>   </a> NUMBER<a>               </a>Yes<a>            </a> -     <a>                    </a> -


                      the sample data is
                      with t as (select 1 as SALE_ID, to_date('10/02/2011','DD/MM/YYYY') as DATE_OF_SALE,1234 as TOTAL_PIECES_SOLD,1 as PROCESSOR from dual union all
                                 select 2, to_date('12/25/2011','DD/MM/YYYY'), 1234,1 from dual union all
                                 select 2, to_date('02/25/2012','DD/MM/YYYY'), 2455,1 from dual union all
                                 select 3, to_date('07/02/2012','DD/MM/YYYY'), 1234,1 from dual)
                      I divided year into 4 quaters(Seasons)

                      Actually i am looking to calculate total number of sales in season so I am referring Date_Of_Sale for the calculation's for the four seasons..Date of sale is in mm/dd/yyyy format.



                      Sample query which i used
                      select null as link,'Fall'||to_char(DATE_OF_SALE,'YY'),sum(TOTAL_PIECES_SOLD) "Blue Acres" from CROP_SALE where PROCESSOR=1 and DATE_OF_SALE >= ADD_MONTHS ( TO_DATE ('0920', 'MMDD')
                                                         , -12
                                                         ) and DATE_OF_SALE <ADD_MONTHS ( TO_DATE ('1220', 'MMDD')
                                                          , -12
                                                )  group by to_char(DATE_OF_SALE,'YY') union all
                      select null as link,'Winter'||to_char(DATE_OF_SALE,'YY'),sum(TOTAL_PIECES_SOLD) "Blue Acres" from CROP_SALE where PROCESSOR=1 and DATE_OF_SALE >= ADD_MONTHS ( TO_DATE ('1220', 'MMDD')
                                                         , -12
                                                ) and DATE_OF_SALE < TO_DATE ('0320', 'MMDD') group by to_char(DATE_OF_SALE,'YY') union all 
                      select null as link,'Spring'||to_char(DATE_OF_SALE,'YY'),sum(TOTAL_PIECES_SOLD) "Blue Acres" from CROP_SALE where PROCESSOR=1 and DATE_OF_SALE >= TO_DATE ('0320', 'MMDD')and DATE_OF_SALE < TO_DATE ('0620', 'MMDD') group by to_char(DATE_OF_SALE,'YY')  union all 
                      select null as link,'Summer'||to_char(DATE_OF_SALE,'YY'),sum(TOTAL_PIECES_SOLD) "Blue Acres" from CROP_SALE where PROCESSOR=1 and DATE_OF_SALE >= TO_DATE ('0620', 'MMDD')and DATE_OF_SALE < TO_DATE ('0920', 'MMDD') group by to_char(DATE_OF_SALE,'YY')
                      its result i got is


                      -<a>            </a>Fall11     <a>            </a> 1234
                      -<a>           </a> Winter11<a>        </a> 1234
                      -<a>           </a> Winter12<a>        </a> 2455
                      -<a>           </a> Summer12<a>      </a> 1234

                      its fine but small issue the winter should combined but due to 2 years its splits into two years like Winter11,Winter12 how to combined this thing

                      i am using 11g

                      Edited by: Pavan on Aug 13, 2012 2:17 AM

                      Edited by: Pavan on Aug 13, 2012 4:14 AM

                      Edited by: Pavan on Aug 13, 2012 4:55 AM
                      • 8. Re: how to avoid the error ORA-01843:
                        UW (Germany)
                        I'm just starting to understand what you're trying to do, but maybe the source of your ORA-01843 error is simply that you mixed days in months in the format strings of your with statement it should be:
                        with t as (select 1 as SALE_ID, to_date('10/02/2011','MM/DD/YYYY') as DATE_OF_SALE,1234 as TOTAL_PIECES_SOLD,1 as PROCESSOR from dual union all
                                   select 2, to_date('12/25/2011','MM/DD/YYYY'), 1234,1 from dual union all
                                   select 2, to_date('02/25/2012','MM/DD/YYYY'), 2455,1 from dual union all
                                   select 3, to_date('07/02/2012','MM/DD/YYYY'), 1234,1 from dual)
                        select * from t;
                        
                           SALE_ID DATE_OF_S TOTAL_PIECES_SOLD  PROCESSOR
                        ---------- --------- ----------------- ----------
                                 1 02-OCT-11              1234          1
                                 2 25-DEC-11              1234          1
                                 2 25-FEB-12              2455          1
                                 3 02-JUL-12              1234          1
                        • 9. Re: how to avoid the error ORA-01843:
                          UW (Germany)
                          I'm still not sure whether I understood the question right. But does this query give you the result as needed?
                          select
                              null as link,
                              case 
                                 when to_char(DATE_OF_SALE,'MMDD') < '0320' then 'Winter'||to_char(to_number(to_char(DATE_OF_SALE,'YYYY')-1),'0000')
                                 when to_char(DATE_OF_SALE,'MMDD') < '0620' then 'Spring '||to_char(DATE_OF_SALE,'YYYY')
                                 when to_char(DATE_OF_SALE,'MMDD') < '0920' then 'Summer '||to_char(DATE_OF_SALE,'YYYY')
                                 when to_char(DATE_OF_SALE,'MMDD') < '1220' then 'Fall '||to_char(DATE_OF_SALE,'YYYY')
                                                                            else 'Winter '||to_char(DATE_OF_SALE,'YYYY')
                              end as season,
                              sum(TOTAL_PIECES_SOLD) "Blue Acres"
                          from CROP_SALE
                          where PROCESSOR=1
                          group by case 
                                 when to_char(DATE_OF_SALE,'MMDD') < '0320' then 'Winter'||to_char(to_number(to_char(DATE_OF_SALE,'YYYY')-1),'0000')
                                 when to_char(DATE_OF_SALE,'MMDD') < '0620' then 'Spring '||to_char(DATE_OF_SALE,'YYYY')
                                 when to_char(DATE_OF_SALE,'MMDD') < '0920' then 'Summer '||to_char(DATE_OF_SALE,'YYYY')
                                 when to_char(DATE_OF_SALE,'MMDD') < '1220' then 'Fall '||to_char(DATE_OF_SALE,'YYYY')
                                                                            else 'Winter '||to_char(DATE_OF_SALE,'YYYY')
                              end;
                          • 10. Re: how to avoid the error ORA-01843:
                            Pavan
                            thanks for reply,
                            in my first post i got the error ORA-01843:

                            later the first reply from Frank Kulash
                             date_of_join     >= ADD_MONTHS ( TO_DATE ('0620', 'MMDD')
                                                               , -12
                                                      )
                            AND   date_of_join     <  ADD_MONTHS ( TO_DATE ('0920', 'MMDD')
                                                               , -12
                                                      )
                            the error ORA-01843: solved now what i am trying is in my previous post i said that

                            i got the result like this
                            -            Fall11                1234
                            -            Winter11         1234
                            -            Winter12         2455
                            -            Summer12       1234
                            but needed the result like
                            -          Fall11                1234
                            -            Winter12         3689
                            -            Summer12       1234
                            • 11. Re: how to avoid the error ORA-01843:
                              Frank Kulash
                              Hi,
                              Pavan wrote:
                              ... the sample data is
                              with t as (select 1 as SALE_ID, to_date('10/02/2011','DD/MM/YYYY') as DATE_OF_SALE,1234 as TOTAL_PIECES_SOLD,1 as PROCESSOR from dual union all
                              select 2, to_date('12/25/2011','DD/MM/YYYY'), 1234,1 from dual union all
                              select 2, to_date('02/25/2012','DD/MM/YYYY'), 2455,1 from dual union all
                              select 3, to_date('07/02/2012','DD/MM/YYYY'), 1234,1 from dual)
                              Please test your code before you post it. You seem to be giving dates in
                              'MM/DD/yyyy' format, but the 2nd argument to TO_DATE is
                              'DD/MM/yyyy'.
                              I divided year into 4 quaters(Seasons)

                              Actually i am looking to calculate total number of sales in season so I am referring Date_Of_Sale for the calculation's for the four seasons..Date of sale is in mm/dd/yyyy format.
                              Use ADD_MONTHS and -19, like I said earlier.
                              WITH   got_qtr      AS
                              (
                                   SELECT     TRUNC ( ADD_MONTHS (date_of_sale, 1) - 19
                                              , 'Q'
                                              )          AS qtr
                                   ,     total_pieces_sold
                                   FROM     t
                              )
                              SELECT       CASE  TO_CHAR (qtr, 'Q')
                                         WHEN  '1'  THEN  'Winter '
                                         WHEN  '2'  THEN  'Spring '
                                         WHEN  '3'  THEN  'Summer '
                                         WHEN  '4'  THEN  'Fall '
                                     END  || TO_CHAR (qtr, 'YY')     AS sales_quarter
                              ,       SUM (total_pieces_sold)     AS blue_acres
                              FROM       got_qtr
                              GROUP BY  qtr
                              ORDER BY  qtr
                              ;
                              The formula I gave in my last reply (TRUNC (ADD_MONTHS (d, -2), 'Q')) maps each date d to the begininng of the calendar quarter before your fiscal quarter. That is, any date from December 20, 2011 through March 19, 2012, gets mapped to October 1, 2011. That's good enough for GROUPing BY, but you also want to convert that into a descriptive string, such as 'Winter 2012'. For that, it would be more convenient if we use the calendar quarter that begins during the fiscal quarter, not the one that begins before it, so that the year you want to display in the SELECT clause is the same as the year in the GROUP BY clause. That's why I used
                              ADD_MONTHS (d, 1)       instead of
                              ADD_MONTHS (d, -2).
                              1 person found this helpful