Converting FLTP to CHAR Data type in SLT

[Former Member]

Hello,

Question – I’m trying to convert a field on a table which is from an Oracle db, from a Floating Point [FLPT] to a NVARCHAR in HANA.   We’re using SLT to replicate the table from Oracle.  I’ve tried several different methods, but I always end up with scientific notation in the NVARCHAR field in HANA rather than
a string of numbers. I’ve tried first converting the field from FLTP to INT4 or NUMC and then creating a new field on the table with a CHAR data type.  Then using a WRITE TO code to populate the new field.   All of the different flavors of this approach seems to always return scientific notation rather than just a string
of numbers.

Details of steps:

Using IUUC_REPL_TABSTG to change the data type from FLTP to a fixed number (INT4 or NUMC) also adding a new field with the data type of CHAR.   Then in IUUC_ASS_RUL_MAP I populate the new field with the original [WRITE i_SERVRECTRANSID_1 TO e_ZSERVRECTRANSID.]

What am
I missing?   Do I need to identify an Event [EOP or EOR]?

Thanks

Dave

[Former Member]

Here is some basic and maybe not robust ABAP that takes a Type P or Type F field and removes scientific notation:

"=========================================================================="

                   "Takes any scientific notation value and converts to regular value (type p)" START

                   "=========================================================================="

                   IF l_s_field-inttype CA 'NIbsPFae' AND

                      <l_field_ext> CA 'E'              .  "E is for Exponent 1.288E+03

                     DATA one TYPE c LENGTH 40.

                     DATA two TYPE c LENGTH 40.

                     DATA numdecimals TYPE rsbohfields-decimals.

                     DATA lv_oref TYPE REF TO cx_root.

                     DATA lv_text TYPE string.

                     DATA d0 TYPE p DECIMALS 0.

                     DATA d1 TYPE p DECIMALS 1.

                     DATA d2 TYPE p DECIMALS 2.

                     DATA d3 TYPE p DECIMALS 3.

                     DATA d4 TYPE p DECIMALS 4.

                     DATA d5 TYPE p DECIMALS 5.

                     DATA d6 TYPE p DECIMALS 6.

                     IF l_s_field-inttype = 'P'.

                       numdecimals = l_s_field-decimals.   "Holds number right of decimal place

                     ELSE.

                       numdecimals = 0                 .   "Decimals holds full length of field!

                     ENDIF.

                     FIELD-SYMBOLS <mysci> TYPE any.

                     IF numdecimals <= 6.

                       TRY.

                           "Takes any scientific notation value and converts to regular value (type p)

                           CASE numdecimals.

                             WHEN 0. ASSIGN d0 TO <mysci>.

                             WHEN 1. ASSIGN d1 TO <mysci>.

                             WHEN 2. ASSIGN d2 TO <mysci>.

                             WHEN 3. ASSIGN d3 TO <mysci>.

                             WHEN 4. ASSIGN d4 TO <mysci>.

                             WHEN 5. ASSIGN d5 TO <mysci>.

                             WHEN 6. ASSIGN d6 TO <mysci>.

                           ENDCASE.

                           IF <mysci> IS ASSIGNED.

                             CLEAR: one, two.

                             SPLIT <l_field_ext> AT 'E' INTO one two.

                             <mysci> = one * ( 10 ** two ).

                             <l_field_ext> = <mysci>.

                             UNASSIGN <mysci>.

                           ENDIF.

                         CATCH cx_root INTO lv_oref.

                           lv_text = lv_oref->get_text( ).

                       ENDTRY.

                     ENDIF.  "Six decimal places or less

                   ENDIF  .  "Numeric

                   "=========================================================================="

                   "Takes any scientific notation value and converts to regular value (type p)" END

                   "=========================================================================="

[Former Member]

Hi Udo,

I had tried the 'Move' statement but it returned the same results, where the data was converted to scientific notation and therefore unjoinable.  

The working solution we have is to:

Create a new field on the table as a data type CHAR (which is converted to NVARCHAR in HANA)

Called the new function Z_F and the new field 'Z' as well as the converted existing field.

The reason for the really short names is the limited field size of the 'insert line of code' field.

FUNCTION Z_F.
*"----------------------------------------------------------------------
*"*"Local Interface:
*"  IMPORTING
*"     REFERENCE(F) TYPE  F
*"  EXPORTING
*"     REFERENCE(H) TYPE  C
*"  EXCEPTIONS
*"      INVALID_DATA
*"----------------------------------------------------------------------
DATA: V_FLT TYPE F,
        V_TMP TYPE I,
        V_TMP2 TYPE N,
        V_CHAR TYPE C LENGTH 10.
CLEAR: V_FLT,V_TMP, V_CHAR.
  V_FLT = F.
  V_TMP = V_FLT.
  V_CHAR = V_TMP.
  H = V_CHAR.
  CONDENSE H NO-GAPS.

  IF sy-subrc NE 0.
    RAISE INVALID_DATA.
  ENDIF.

This then populates a new field on the replicated HANA table with the data type of NVARCHAR and where the data is not in scientific notation. 

Regards,

Dave

[sommerudo]

Hi,

You're right I missed the assignment to a local variable of type N or I before the assignment to the field with type C.

(My examples show that the direct assignment from float to char results in the scientific notification.)

BTW, just in case you're not aware, if you want to use longer names, you can also specify an include in column 'Insert Include Name' instead of the code in column 'Insert Line of Code'...

Regards,

Udo

[sommerudo]

Hello David,

In general, the type conversion from float to num or int should work in ABAP with the simple MOVE.

In your example the field rule should contain a statement like:

     e_ZSERVRECTRANSID =  i_SERVRECTRANSID_1.

Here's a simple report comparing the results of MOVE and WRITE TO:

     MOVE statement:
          float        3,1415926535897931E+00
          int                 3
          num        0000000003
          char        3.142E+00

     WRITE TO Statement:
          float        3,1415926535897931E+00
          int        707406.378
          num         3,142E+00
          char        3,142E+00

So, just try the simple MOVE (=) instead of WRITE TO in the field rule.

Best regards,

Udo

Source Code of the simple report that displays the different outputs:

REPORT ZDUMMY .

data:

l_float type f,

l_int type i,

l_num(10) type n,

l_char(10) type c.

l_float = '3.1415926535897932384626433832795'.

　

l_int = l_float.

l_num = l_float.

l_char = l_float.

write:

/ 'MOVE statement:',

/(10) 'float', l_float,

/(10) 'int', l_int,

/(10) 'num', l_num,

/(10) 'char', l_char.

skip.

write l_float to l_int.

write l_float to l_num.

write l_float to l_char.

write:

/ 'WRITE TO Statement:',

/(10) 'float', l_float,

/(10) 'int', l_int,

/(10) 'num', l_num,

/(10) 'char', l_char.