This discussion is archived
8 Replies Latest reply: Sep 9, 2013 10:35 PM by klaus.barthels RSS

Convert numeric values from listoutput

klaus.barthels Newbie
Currently Being Moderated


I have to load a listoutput from host to an external table.

The listoutput looks like

GRDNR                       ZAHL_DAT            Umsatz

09551972                    03.04.2013            1.000,00                                                                         

20792116                    03.04.2013               30,00                                                                         

18815699                    08.04.2013            1.000,00                                                                         

15688068                    18.04.2013              960,00  

My problem is, that the value "Umsatz" has digit points.

The script for the external table is:

CREATE TABLE MIRATOOL.EXT_LQSK74K1

(

  GRDNR     INTEGER,

  ZAHL_DAT  DATE        NULL,

  UMSATZ    NUMBER(7,2) NULL

)

ORGANIZATION EXTERNAL

  (  TYPE ORACLE_LOADER

     DEFAULT DIRECTORY PFAD_CSV_IN_HOST

     ACCESS PARAMETERS

       (RECORDS DELIMITED BY NEWLINE

        BADFILE 'LQSK74K1.bad'

        LOGFILE 'LQSK74K1.log'

        fields

         (FILLER1       CHAR(1)

         ,GRDNR         CHAR(8)

         ,FILLER2       CHAR(20)

         ,ZAHL_DAT      CHAR(10) date_format date mask "dd.mm.yyyy"

         ,FILLER3       CHAR(10)

         ,UMSATZ        CHAR(10)

         ) 

        )

     LOCATION (PFAD_CSV_IN_HOST:'LO.SDS.LQSK74K')

  )

REJECT LIMIT UNLIMITED

NOPARALLEL

NOMONITORING;

How must I convert the value "UMSATZ"??

 

Kindly regards

 

Klaus

  • 1. Re: Convert numeric values from listoutput
    DK2010 Guru
    Currently Being Moderated

    Hi,

     

    Can you tell us what it should look like, menas you want O/p in what format.. If possible can you share 1,2 line if your datafile.

  • 2. Re: Convert numeric values from listoutput
    klaus.barthels Newbie
    Currently Being Moderated

    I try to make from the listoutput a external table for further alynalysis.

    The value "Umsatz" (to examble in listoutput with value 1.999.888,77) is to convert to a numeric value with the format 7, 2 (Result must be 1999888,77).

    In this case, the comma "," is the decimal separator (german format).

    If I use the obove script I get the value 888, the other signs are ignored

     

    Regards

  • 3. Re: Convert numeric values from listoutput
    Richard Harrison . Expert
    Currently Being Moderated

    Hi Klaus,

    This might help (I've never actually tried it myself though.....):

     

    http://docs.oracle.com/cd/E11882_01/server.112/e22490/et_params.htm#autoId10

     

    Cheers,

    Harry

  • 4. Re: Convert numeric values from listoutput
    klaus.barthels Newbie
    Currently Being Moderated

    Hi Harry,

    many thanks for your hint, but there are many samples for converting date and time, but nothing for my problem.

    To the link you wrote, I've looked several times before, but don't find something about converting to numeric.

     

    Regards

     

    Klaus

  • 5. Re: Convert numeric values from listoutput
    Richard Harrison . Expert
    Currently Being Moderated

    Hi Klaus,

    Sorry I though that link was directly to the "TERRITORY" keyword but it doesn't seem to be when i click it again. i did a quick test and it sort of 50% works......

     

    CREATE TABLE TEST_DE

    (

      UMSATZ    NUMBER(12,2) NULL

    )

    ORGANIZATION EXTERNAL

      (  TYPE ORACLE_LOADER

         DEFAULT DIRECTORY TMP

         ACCESS PARAMETERS

           (RECORDS DELIMITED BY NEWLINE

    TERRITORY "GERMANY"

            BADFILE 'LQSK74K1.bad'

            LOGFILE 'LQSK74K1.log'

            fields

             (UMSATZ        char(15)

             )

            )

         LOCATION (TMP:'input.dat')

      )

    REJECT LIMIT UNLIMITED

    NOPARALLEL

    NOMONITORING;

     

    [oracle@server]:(/scratch]# cat input.dat

    1999888,77

     

    SYS@DB>select * from test_de;

     

     

        UMSATZ

    ----------

    1999888.77

     

    so it's happily sorts out the decimal marker....

     

    but.... as soon as i add commas as the 'thousands' separators it won;t work..... i just get 'invalid number'

     

    maybe this sort of helps.....maybe?

     

    Cheers,

    Harry

  • 6. Re: Convert numeric values from listoutput
    klaus.barthels Newbie
    Currently Being Moderated

    Hi Harry,

    thanks for the second hint.

    The german sign for the 'thousends' seperator is the dot, not the comma.

    I've tryed it with this parameter, but it it also didn't work with the listoutput.

    The same problem, if there is a thousends separater the hole record is ignored (Error in value) and the signs after the decimal separater (",") are also ignored.

     

    Regards

     

    Klaus

  • 7. Re: Convert numeric values from listoutput
    Barbara Boehmer Oracle ACE
    Currently Being Moderated

    One method would be to load the umsatz values as varchar2, then use a view with to_number and nls_numeric_characters, to convert it, as demonstrated below.

     

    SCOTT@orcl12c> HOST TYPE LO.SDS.LQSK74K

    GRDNR                       ZAHL_DAT            Umsatz

    09551972                    03.04.2013            1.000,00

    20792116                    03.04.2013               30,00

    18815699                    08.04.2013            1.000,00

    15688068                    18.04.2013              960,00

     

    SCOTT@orcl12c> CREATE OR REPLACE DIRECTORY pfad_csv_in_host AS 'c:\my_oracle_files'

      2  /

     

    Directory created.

     

    SCOTT@orcl12c> CREATE TABLE EXT_LQSK74K1

      2  (

      3    GRDNR     INTEGER,

      4    ZAHL_DAT  DATE         NULL,

      5    UMSATZ    VARCHAR2(10) NULL

      6  )

      7  ORGANIZATION EXTERNAL

      8    (  TYPE ORACLE_LOADER

      9       DEFAULT DIRECTORY PFAD_CSV_IN_HOST

    10       ACCESS PARAMETERS

    11         (RECORDS DELIMITED BY NEWLINE

    12          BADFILE 'LQSK74K1.bad'

    13          LOGFILE 'LQSK74K1.log'

    14          SKIP 1

    15          fields

    16           (FILLER1       CHAR(1)

    17           ,GRDNR         CHAR(8)

    18           ,FILLER2       CHAR(20)

    19           ,ZAHL_DAT      CHAR(10) date_format date mask "dd.mm.yyyy"

    20           ,FILLER3       CHAR(10)

    21           ,UMSATZ        CHAR(10)

    22           )

    23          )

    24       LOCATION (PFAD_CSV_IN_HOST:'LO.SDS.LQSK74K')

    25    )

    26  REJECT LIMIT UNLIMITED

    27  NOPARALLEL

    28  NOMONITORING

    29  /

     

    Table created.

     

    SCOTT@orcl12c> SELECT * FROM ext_lqsk74k1

      2  /

     

         GRDNR ZAHL_DAT   UMSATZ

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

       9551972 03.04.2013  1.000,00

        792116 03.04.2013     30,00

       8815699 08.04.2013  1.000,00

       5688068 08.04.2013    960,00

     

    4 rows selected.

     

    SCOTT@orcl12c> COLUMN umsatz FORMAT 999,999.00

    SCOTT@orcl12c> CREATE OR REPLACE VIEW test_view AS

      2  SELECT grdnr, zahl_dat,

      3         CAST

      4           (TO_NUMBER

      5              (umsatz,

      6               '999G999D99',

      7               'NLS_NUMERIC_CHARACTERS='',.''')

      8            AS NUMBER (7,2)) umsatz

      9  FROM   ext_lqsk74k1

    10  /

     

    View created.

     

    SCOTT@orcl12c> DESC test_view

    Name                                      Null?    Type

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

    GRDNR                                              NUMBER(38)

    ZAHL_DAT                                           DATE

    UMSATZ                                             NUMBER(7,2)

     

    SCOTT@orcl12c> SELECT * FROM test_view

      2  /

     

         GRDNR ZAHL_DAT        UMSATZ

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

       9551972 03.04.2013    1,000.00

        792116 03.04.2013       30.00

       8815699 08.04.2013    1,000.00

       5688068 08.04.2013      960.00

     

    4 rows selected.

  • 8. Re: Convert numeric values from listoutput
    klaus.barthels Newbie
    Currently Being Moderated

    Hi Barbara,

    many thanks.

    I think that the proposed method of you is the only way to meaningfully process the data further.

    However, I had hoped that the external table offers the possibility to solve the problem with a function (mask ...).

     

    Best regards

     

    Klaus

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points