8 Replies Latest reply: Sep 10, 2013 12:35 AM by klaus.barthels RSS

    Convert numeric values from listoutput

    klaus.barthels


      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

          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

            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 .

              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

                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 .

                  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

                    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

                      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

                        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