6 Replies Latest reply: Mar 2, 2010 10:52 AM by user12122880 RSS

    ORA-01841 :(full) year must be between -4713 and +9999, and not be 0

    user12122880
      hello,

      a view which has been working for monrhs, is now showing the error ORA-01841.

      Basically the view excludes a string value of "Current" and should only display the other values which are converted to date format, using the TO_DATE function.
      What appears to have happened is that the order of the query has changed when it is run.
      I'm not sure as to why that has happened, as there has been no database changes recently.

      any help appreciated.

      Malcolm.
        • 1. Re: ORA-01841 :(full) year must be between -4713 and +9999, and not be 0
          Paul M.
          What appears to have happened is that the order of the query has changed when it is run.
          What that means exactly ? please clarify.

          Also post the query.
          • 2. Re: ORA-01841 :(full) year must be between -4713 and +9999, and not be 0
            user12122880
            hi Paul,

            this one works :-

            ("SNAP_ID"<>'Current' AND TO_DATE("SNAP_ID",'yyyymmdd')>TO_DATE(TO_CHAR

            (SYSDATE@!,'dd-mon-yyyy'),'dd-mon-yyyy')-7)

            this doesn't

            (TO_DATE("SNAP_ID",'yyyymmdd')>TO_DATE(TO_CHAR(SYSDATE@!,'dd-mon-yyyy')

            ,'dd-mon-yyyy')-7 AND "SNAP_ID"<>'Current')



            the evaluation order being reversed.
            this is the relevant part of the view - see the last few lines :-

            FROM adm_fact af,
            disco_programme_dim dpd,
            disco_adm_group_code_dim agc
            WHERE af.programme_code = dpd.pr_programme_code
            AND af.admissions_group_code = agc.gc_name_code
            AND agc.gc_name_code IN (SELECT gc_name_code
            FROM disco_adm_group_code_dim
            WHERE gc_student_level IN ('T','R')
            AND gc_fundability in('O','H'))
            AND af.ftpt_flag IN ('F','P','S')
            AND af.validity_status_code = 'C'
            AND (af.withdrawn_flag IS NULL OR af.withdrawn_flag='R')
            AND af.qualification_group_code NOT IN ('65','99')
            AND af.qualification_group_code IN (SELECT qa_qg_code
            FROM disco_qual_aim_dim
            WHERE qa_uni_val_code = '01')
            AND snapshot_id <> 'Current'
            AND TO_DATE(snapshot_id, 'yyyymmdd') > TO_DATE(TO_CHAR(SYSDATE,'dd-mon-yyyy'),'dd-mon-yyyy') - 7
            • 3. Re: ORA-01841 :(full) year must be between -4713 and +9999, and not be 0
              Paul M.
              Even less clear now... in your post there are syntaxes that can't work, so please post something more comprehensible.
              • 4. Re: ORA-01841 :(full) year must be between -4713 and +9999, and not be 0
                user12122880
                hi,

                this is the view :-

                SELECT act."Year", act."Snapshot", act.school "School", act.fundability "Fundability", act."Level", act.applications, tgt.target
                FROM
                (SELECT DISTINCT af.admissions_entry_year "Year",
                af.snapshot_id "Snapshot",
                dpd.pr_school_code school,
                agc.gc_fundability fundability,
                agc.gc_student_level "Level",
                SUM(af.factored_student) applications
                FROM adm_fact af,
                disco_programme_dim dpd,
                disco_adm_group_code_dim agc
                WHERE af.programme_code = dpd.pr_programme_code
                AND af.admissions_group_code = agc.gc_name_code
                AND agc.gc_name_code IN (SELECT gc_name_code
                FROM disco_adm_group_code_dim
                WHERE gc_student_level IN ('T','R')
                AND gc_fundability in('O','H'))
                AND af.ftpt_flag IN ('F','P','S')
                AND af.validity_status_code = 'C'
                AND (af.withdrawn_flag IS NULL OR af.withdrawn_flag='R')
                AND af.qualification_group_code NOT IN ('65','99')
                AND af.qualification_group_code IN (SELECT qa_qg_code
                FROM disco_qual_aim_dim
                WHERE qa_uni_val_code = '01')
                AND snapshot_id <> 'Current'
                AND TO_DATE(snapshot_id, 'yyyymmdd') > TO_DATE(TO_CHAR(SYSDATE,'dd-mon-yyyy'),'dd-mon-yyyy') - 7
                GROUP BY af.admissions_entry_year,
                dpd.pr_school_code,
                agc.gc_fundability,
                agc.gc_student_level,
                af.snapshot_id
                ORDER BY af.admissions_entry_year,
                af.snapshot_id,
                dpd.pr_school_code,
                agc.gc_fundability,
                agc.gc_student_level) act,
                (SELECT apt_admissions_year "Year", apt_school_code school , 'O' fundability, 'R' "Level", apt_overseas_target_pgr target from adm_pg_targets
                UNION
                SELECT apt_admissions_year "Year", apt_school_code school , 'H' fundability, 'R' "Level", apt_home_target_pgr target from adm_pg_targets
                UNION
                SELECT apt_admissions_year "Year", apt_school_code school , 'O' fundability, 'T' "Level", apt_overseas_target_pgt target from adm_pg_targets
                UNION
                SELECT apt_admissions_year "Year", apt_school_code school , 'H' fundability, 'T' "Level", apt_home_target_pgr target from adm_pg_targets) tgt
                WHERE act."Year"=tgt."Year"
                AND act.school=tgt.school
                AND act.fundability=tgt.fundability
                AND act."Level"=tgt."Level"
                • 5. Re: ORA-01841 :(full) year must be between -4713 and +9999, and not be 0
                  Frank Kulash
                  Hi,

                  This site normally compresses white-space. Whenever you post code (or any formatted text) on this site, type these 6 characters:

                  \
                  (small letters only, inside curly brackets) before and after each section of formatted text.
                  
                  The error message "ORA-01841 :(full) year must be between -4713 and +9999, and not be 0" is caused by a string to DATE conversion, wherre the string to be converted and the format string do not agree.
                  One place where that might be happening is:
                  AND TO_DATE(snapshot_id, 'yyyymmdd') > TO_DATE(TO_CHAR(SYSDATE,'dd-mon-yyyy'),'dd-mon-yyyy') - 7
                  First of all: What is the data type of snapshot_id?
                  If it's a DATE, don't use TO_DATE with it.
                  If it's a VARCHAR2 (or any other type of string), then that's the problem right there.  Dates should be stored in DATE columns.  Storing dates as strings causes an inefficient conversion, if not a error like the one you're getting.
                  
                  Assuming snapshot_id is a string, and that you can't change it now, then it looks like you have some bad data.  (That would account for why the query that was running before suddenly got this error.)  Luckily, this error is pretty specific: it can't interpret the year, so there's probably some problem in the first 4 characters of snapshot_id.
                  Try finding what kinds of data are stored in that column.  Try something like:
                  SELECT     SUBSTR (snapshot_id, 1, 4)     AS year_txt
                  ,     COUNT (*)               AS cnt
                  FROM     disco_qual_aim_dim
                  GROUP BY SUBSTR (snapshot_id, 1, 4)
                  ORDER BY year_txt;
                  Do *NOT* use a WHERE clause; the error may occur on any row, even rows ruled out by other conditions in your query.
                  Most bad data will appear at the beginning or end of the output, with very small values (like 1) in the cnt column.
                  If you find some bad values, use those values in another query that returns the primary key, or some other information that will pinpoint the row that needs to be corrected.  For example, say you notice that '0000' appears in the output from the query above.  To find which row contains that bad data:
                  SELECT *
                  FROM disco_qual_aim_dim
                  WHERE snapshot_id LIKE '0000%';
                   
                  
                  By the way, whenever you do 
                  TO_DATE ( TO_CHAR (...))
                  (or 
                  TO_CHAR ( TO_DATE (...))
                  ) an alarm should go off in your mind.  Oracle provides lots of functions and other tools for date manipulation; round-trip conversions are almost never a good idea.
                  The following expression from your query:
                  TO_DATE(TO_CHAR(SYSDATE,'dd-mon-yyyy'),'dd-mon-yyyy') - 7
                  can be re-written more eifficiently and clearly as
                  TRUNC (SYSDATE) - 7
                  This is not causing the ORA-01841 error, but it's worth correcting.                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
                  • 6. Re: ORA-01841 :(full) year must be between -4713 and +9999, and not be 0
                    user12122880
                    that is useful. Thanks.

                    regards,
                    Malcolm.