Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2012
    Posts
    1

    Unanswered: Spool output of large table with million records

    Hi,

    I am trying to simply take output of one table records in text file using query. I am getting error "ORA 01841 full year must be between 4713 and 9999 and not be 0" after extraction of very few records from the table.
    Below is the query I am presently using:

    SET HEADING OFF

    SET VERIFY OFF

    SET SERVEROUTPUT OFF

    SET TERMOUT OFF

    SET FEEDBACK OFF

    SET TRIMSPOOL ON

    SET LINESIZE 32000

    SET PAGESIZE 0

    SET COLSEP "|"

    Column MANDT format A5

    Column BUKRS format A5

    Column BELNR format A10

    Column GJAHR format A5

    Column BLART format A2

    Column BLDAT format A11

    Column BUDAT format A11

    Column MONAT format A5

    Column CPUDT format A10

    Column CPUTM format A8

    Column AEDAT format A10

    Column UPDDT format A10

    Column WWERT format A10

    Column USNAM format A12

    Column TCODE format A20

    Column BVORG format A16

    Column XBLNR format A16

    Column DBBLG format A10

    Column STBLG format A10

    Column STJAH format A5

    Column BKTXT format A25

    Column WAERS format A5

    Column KURSF format 999999999.99999

    Column KZWRS format A5

    Column KZKRS format 999999999.99999

    Column BSTAT format A5

    Column XNETB format A5

    Column FRATH format 9999999999999.99

    Column XRUEB format A5

    Column GLVOR format A5

    Column GRPID format A12

    Column DOKID format A40

    Column ARCID format A10

    Column IBLAR format A5

    Column AWTYP format A5

    Column AWKEY format A20

    Column FIKRS format A5

    Column HWAER format A5

    Column HWAE2 format A5

    Column HWAE3 format A5

    Column KURS2 format 999999999.99999

    Column KURS3 format 999999999.99999

    Column BASW2 format A5

    Column BASW3 format A5

    Column UMRD2 format A5

    Column UMRD3 format A5

    Column XSTOV format A5

    Column STODT format A10

    Column XMWST format A5

    Column CURT2 format A5

    Column CURT3 format A5

    Column KUTY2 format A5

    Column KUTY3 format A5

    Column XSNET format A5

    Column AUSBK format A5

    Column XUSVR format A5

    Column DUEFL format A5

    Column AWSYS format A10

    Column TXKRS format 999999999.99999

    Column LOTKZ format A10

    Column XWVOF format A5

    Column STGRD format A5

    Column PPNAM format A12

    Column BRNCH format A5

    Column NUMPG format A5

    Column ADISC format A5

    Column XREF1_HD format A20

    Column XREF2_HD format A20

    Column XREVERSAL format A10

    Column REINDAT format A10

    Column PSOTY format A5

    Column PSOAK format A10

    Column PSOKS format A10

    Column PSOSG format A5

    Column PSOFN format A30

    Column INTFORM format A8

    Column INTDATE format A10

    Column PSOBT format A10

    Column PSOZL format A5

    Column PSODT format A10

    Column PSOTM format A8

    Column FM_UMART format A8

    Column CCINS format A4

    Column CCNUM format A25

    Column SSBLK format A5

    Column BATCH format A10

    Column SNAME format A12

    Column SAMPLED format A8

    Column KNUMV format A10

    Column XBLNR_ALT format A26

    Column ZZREASON_CD format A12

    Column ZZREASON_DSCRPT format A50

    spool bkpf.txt

    select MANDT, BUKRS, BELNR, GJAHR, BLART, decode(BLDAT,'00000000','00.00.0000',to_char(to_da te(substr(BLDAT,1,10),'RRRR-MM-DD'),'DD.MM.RRRR')) as BLDAT, decode(BUDAT,'00000000','00.00.0000',to_char(to_da te(substr(BUDAT,1,10),'RRRR-MM-DD'),'DD.MM.RRRR')) as BUDAT, MONAT, decode(CPUDT,'00000000','00.00.0000',to_char(to_da te(substr(CPUDT,1,10),'RRRR-MM-DD'),'DD.MM.RRRR')) as CPUDT, decode(cputm,'000000','00:00:00',to_char(substr(cp utm,1,2)||':'||substr(cputm,3,2)||':'||substr(cput m,5,2))) as CPUTM,
    decode(aedat,'00000000','00.00.0000',to_char(to_da te(substr(AEDAT,1,10),'RRRR-MM-DD'),'DD.MM.RRRR')) as AEDAT,
    decode(UPDDT,'00000000','00.00.0000',to_char(to_da te(substr(UPDDT,1,10),'RRRR-MM-DD'),'DD.MM.RRRR')) as UPDDT,
    decode(WWERT,'00000000','00.00.0000',to_char(to_da te(substr(WWERT,1,10),'RRRR-MM-DD'),'DD.MM.RRRR')) as WWERT,
    USNAM, TCODE, BVORG, XBLNR, DBBLG, STBLG, STJAH, BKTXT, WAERS, KURSF, KZWRS, KZKRS, BSTAT, XNETB, FRATH, XRUEB, GLVOR, GRPID, DOKID, ARCID, IBLAR, AWTYP,
    AWKEY, FIKRS, HWAER, HWAE2, HWAE3, KURS2, KURS3, BASW2, BASW3, UMRD2, UMRD3, XSTOV, decode(STODT,'00000000','00.00.0000',to_char(to_da te(substr(STODT,1,10),'RRRR-MM-DD'),'DD.MM.RRRR')) as STODT, XMWST, CURT2, CURT3, KUTY2,
    KUTY3, XSNET, AUSBK, XUSVR, DUEFL, AWSYS, TXKRS, LOTKZ, XWVOF, STGRD, PPNAM, BRNCH, NUMPG, ADISC, XREF1_HD, XREF2_HD, XREVERSAL,
    decode(REINDAT,'00000000','00.00.0000',to_char(to_ date(substr(REINDAT,1,10),'RRRR-MM-DD'),'DD.MM.RRRR')) as REINDAT, PSOTY, PSOAK, PSOKS, PSOSG, PSOFN, INTFORM, decode(INTDATE,'00000000','00.00.0000',to_char(to_ date(substr(INTDATE,1,10),'RRRR-MM-DD'),'DD.MM.RRRR')) as INTDATE,
    decode(PSOBT,'00000000','00.00.0000',to_char(to_da te(substr(PSOBT,1,10),'RRRR-MM-DD'),'DD.MM.RRRR')) as PSOBT, PSOZL,
    decode(PSODT,'00000000','00.00.0000',to_char(to_da te(substr(PSODT,1,10),'RRRR-MM-DD'),'DD.MM.RRRR')) as PSODT, decode(PSOTM,'000000','00:00:00',to_char(substr(PS OTM,1,2)||':'||substr(PSOTM,3,2)||':'||substr(PSOT M,5,2))) as PSOTM, FM_UMART, CCINS,
    CCNUM, SSBLK, BATCH, SNAME, SAMPLED, KNUMV, XBLNR_ALT, ZZREASON_CD, ZZREASON_DSCRPT from sapsrp.bkpf where mandt = 200;

    spool off

    Kindly suggest what I am missing in this, so that I can extract all records from this table.

    Thanks,
    Manish

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >I am getting error "ORA 01841 full year must be between 4713 and 9999 and not be 0"
    This is the price you pay when dates are stored in VARCHAR2 columns.
    At least one column has at least 1 malformed DATE.

    You need to either fix the bad data or ignore the rows with the bad data.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Feb 2006
    Posts
    173
    Provided Answers: 1
    Can't promise it will work but you can try something like this for each of your date columns:
    Code:
    select ( case
             when bldat  =  '00000000'                                          then '00.00.0000'
             -- Use sysdate if '-' not found in position 5
             when substr( bldat, 5, 1 )  !=  '-'                                then to_char( sysdate, 'dd.mm.rrrr' )
             -- Use sysdate if '-' not found in position 8
             when substr( bldat, 8, 1 )  !=  '-'                                then to_char( sysdate, 'dd.mm.rrrr' )
             -- Use sysdate if year portion contains non numeric
             when regexp_instr( substr( bldat, 1, 4 ), '\D',1,1,0,'i' )  !=  0  then to_char( sysdate, 'dd.mm.rrrr' )
             -- Use sysdate if year portion not in valid range
             when to_number( substr( bldat, 1, 4 ) ) not between -4713 and 9999 then to_char( sysdate, 'dd.mm.rrrr' )
             -- Use sysdate if year portion is zero
             when to_number( substr( bldat, 1, 4 ) )  =  0                      then to_char( sysdate, 'dd.mm.rrrr' )
             -- Use sysdate if month portion contains non numeric
             when regexp_instr( substr( bldat, 6, 2 ), '\D',1,1,0,'i' )  !=  0  then to_char( sysdate, 'dd.mm.rrrr' )
             -- Use sysdate if month portion not in valid range
             when to_number( substr( bldat, 6, 2 ) ) not between 1 and 12       then to_char( sysdate, 'dd.mm.rrrr' )
             -- Use sysdate if day portion contains non numeric
             when regexp_instr( substr( bldat, 9, 2 ), '\D',1,1,0,'i' )  !=  0  then to_char( sysdate, 'dd.mm.rrrr' )
             -- Use sysdate if day portion not in valid range
             when to_number( substr( bldat, 9, 2 ) ) not between 1 and 31       then to_char( sysdate, 'dd.mm.rrrr' )
             -- Hopefully date is valid, use it.
             else to_char( to_date( substr( bldat, 1, 10 ), 'rrrr-mm-dd' ), 'dd.mm.rrrr' )
             end ) as bldat
     from  sapsrp.bkpf
    where  mandt  =  200;

  4. #4
    Join Date
    Feb 2005
    Posts
    57
    When you have a date column with value '00000000' you are forcing an invalid date '00.00.0000'!! If '00000000' is valid you need to either force NULL or a valid date value e.g. '01.01.1900' or '31.12.2099'.

    hth
    Outrider

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •