Error in Select Syntax

[former_member609685]

we are migrating S4 Hana 1610 From to S4 Hana 2020 and getting error:

 SELECT BK~MANDT, BK~BUKRS, BK~GJAHR, BS~KUNNR, K1~NAME1,  BS~SHKZG, BS~NETDT,  BK~MONAT,  BS~AUGDT,  CASE WHEN BS~SHKZG = 'S' AND BK~BUKRS = '2000' THEN BS~DMBTR * 100 WHEN BS~SHKZG = 'S' AND BK~BUKRS = '1000' THEN BS~DMBTR WHEN BS~SHKZG = 'H' AND BK~BUKRS = '2000' THEN BS~DMBTR * -100 WHEN BS~SHKZG = 'H' AND BK~BUKRS = '1000' THEN BS~DMBTR * -1 END FROM BSEG AS BS LEFT JOIN BKPF AS BK ON BK~BUKRS EQ BS~BUKRS AND BK~GJAHR EQ BS~GJAHR AND BK~BELNR EQ BS~BELNR INNER JOIN KNA1 AS K1 ON K1~KUNNR EQ BS~KUNNR INTO TABLE @GT_MOVTOSCTE WHERE BK~BLART NOT IN ('WL') AND BK~BUKRS = @P_VKORG AND BS~KOART EQ 'D' AND BS~H_BSTAT NE 'D'  AND BS~H_BSTAT NE 'M' AND BS~DMBTR GT 0.

I am getting the error:

The maximum possible number of places in the expression starting with DMBTR is 34 places with 2 decimal places. There can be, however, no more than 31 places and 14 decimal places.

I change the line from

CASE WHEN BS~SHKZG = 'S' AND BK~BUKRS = '2000' THEN BS~DMBTR * 100

To

CASE WHEN BS~SHKZG = 'S' AND BK~BUKRS = '2000' THEN cast( ( BS~DMBTR * 100 ) as dec( 15, 2 ) )

and the Syntaxis error is the same.

The error is in all programs with Select using sentence case and field DMBTR :

SELECT BK~MANDT, BK~BUKRS, BK~GJAHR, BK~BELNR, 
CASE WHEN BS~SHKZG = 'S' THEN BS~DMBTR ELSE ( BS~DMBTR * -1 ) END "IMPORTE

Any idea .

[matt]

Do you think you might be kind enough to edit your question and use that little "code" button in the browser editor? And then repost the code with proper indentation?

Those of us sacrificing our valuable time thank you.

[former_member609685]

Thanks Matthew,

You are Right, I already edit my post with the code option, Thaks for you observation.

[Sandra_Rossi]

I guess that BSEG-DMBTR in your system has length 23 digits including 2 decimals (Field Length Extension activated).

I reproduced the issue with this code (I force SFLIGHT-PRICE to be 23 digits including 2 decimals):

SELECT CAST( price AS DEC( 23, 2 ) ) * 1
FROM sflight
INTO TABLE @DATA(flight_prices).

It can be solved with:

SELECT CAST( price AS DEC( 23, 2 ) ) * cast( 1 as dec( 1 ) ) 
FROM sflight
INTO TABLE @DATA(flight_prices).

The issue is that an integer (1) is considered to be a 11 digits number, so the syntax checker considers a total length of 34 digits.

With the solution above, by casting the integer to its actual length (1 as dec( 1 ) and 100 as dec( 3 )), it will consider a final length of 24 and 26 respectively.

NB: why do you multiply by 100? I hope it's not because of decimals of the currency key, because it's handled by SAP automatically, you don't need to handle it yourself.

[former_member609685]

Yes you examplo Work fine but using case no work

other example:

REPORT ZDUMP_ISSUE.
TYPES: BEGIN OF TY_MOVTOSCTE,
      MANDT TYPE MANDT,           "MANDANTE
      BUKRS TYPE BUKRS,           "SOCIEDAD
      GJAHR TYPE GJAHR,           "EJERCICIO
      BELNR TYPE BELNR_D,         "DOCUMENTO
      BLART TYPE BLART,           "CLASE_DOC
      KUNNR TYPE KUNNR,           "CLIENTE
      NAME1 TYPE NAME1_GP,        "NOMBRE CLIENTE
      SHKZG TYPE SHKZG,           "DEBE_HABER
      NETDT TYPE NETDT,           "FECHA_VENCIMIENTO
      MONAT TYPE MONAT,           "MES_CONTABLE
      AUGDT TYPE AUGDT,           "FECHA DE COMPENSACION
      DMBTR TYPE DMBTR,           "IMPORTE
END OF TY_MOVTOSCTE.
data GT_MOVTOSCTE TYPE STANDARD TABLE OF TY_MOVTOSCTE WITH HEADER LINE.
    SELECT
        BK~MANDT,            "MANDANTE
        BK~BUKRS,            "SOCIEDAD
        BK~GJAHR,            "EJERCICIO
        BK~BELNR,            "DOCUMENTO
        BK~BLART,            "CLASE_DOC
        BS~KUNNR,            "CLIENTE
        K1~NAME1,            "NOMBRE CLIENTE
        BS~SHKZG,            "DEBE_HABER
        BS~NETDT,            "FECHA DE VENCIMIENTO
        BK~MONAT,            "MES_CONTABLE
        BS~AUGDT,            "FECHA DE COMPENSACION
        CASE WHEN BS~SHKZG = 'S' THEN BS~DMBTR ELSE ( BS~DMBTR * -1 ) END             "IMPORTE
    FROM BSEG AS BS
        LEFT JOIN BKPF AS BK
            ON BK~BUKRS EQ BS~BUKRS
            AND BK~GJAHR EQ BS~GJAHR
            AND BK~BELNR EQ BS~BELNR
        INNER JOIN KNA1 AS K1
            ON K1~KUNNR EQ BS~KUNNR
    INTO TABLE @GT_MOVTOSCTE
    WHERE BK~BLART NOT IN ('WL')
        AND BS~KOART EQ 'D'
        AND BS~H_BSTAT NE 'D'
        AND BS~H_BSTAT NE 'M'
        AND BS~AUGBL = ''
        AND BS~DMBTR GT 0
        AND BS~KUNNR IN ( SELECT KN~KUNNR            "CLIENTE
                          FROM KNVP AS KN
                          WHERE KN~PARVW EQ 'Z8'
                          AND KN~KUNNR NOT BETWEEN '0000300000' AND '0000399999'
                          GROUP BY KN~KUNNR ).

Same error, already change

CASE WHEN BS~SHKZG = 'S' THEN CAST( BS~DMBTR ASDEC(23,2))* cast(1 as dec(1)) ELSE ( BS~DMBTR * -1 ) END             "IMPORTE

[Sandra_Rossi]

Just a remark, the following:

CAST( BS~DMBTR AS DEC( 23, 2 ) )

Is equivalent to the following because column BSEG-DMBTR is already 23, 2 in your system:

BS~DMBTR

[Sandra_Rossi]

-1 is also an integer and you must use CAST also to avoid overflow.

Instead of:

CASE 
WHEN BS~SHKZG ='S' THEN CAST( BS~DMBTR AS DEC( 23, 2 ) ) * cast( 1 as dec( 1 ) ) 
ELSE ( BS~DMBTR * -1 )
END

Use:

CASE 
WHEN BS~SHKZG = 'S' THEN BS~DMBTR * cast( 1 as dec( 1 ) ) 
ELSE BS~DMBTR * cast( -1 as dec( 1 ) ) 
END

[former_member609685]

Hello Sandra, The cast work fine there is not Syntaxis Error.

Thanks for you help.

Regards.

[matt]

francisco.ramirez.sanchez

Thank you. And some newlines and indentation?

[former_member609685]

Hello again,

the error in syntaxis is ok but when is executed other error:

TYPES: BEGIN OF TY_MOVTOSCTE,

 KUNNR TYPE KUNNR, "CLIENTE

SHKZG TYPE SHKZG, "DEBE_HABER 

NETDT TYPE NETDT, "FECHA_VENCIMIENTO

AUGDT TYPE AUGDT, "FECHA DE COMPENSACION 

DMBTR TYPE DMBTR, "IMPORTE

END OF TY_MOVTOSCTE.

data GT_MOVTOSCTE TYPE STANDARD TABLE OF TY_MOVTOSCTE WITH HEADER LINE.

SELECT BS~KUNNR, "CLIENTE

BS~SHKZG, "DEBE_HABER 

BS~NETDT, "FECHA DE VENCIMIENTO

BS~AUGDT, "FECHA DE COMPENSACION 

CASE WHEN BS~SHKZG = 'S' THEN BS~DMBTR * cast( 100 as dec( 1 ) ) ELSE BS~DMBTR * cast( -100 as dec( 1 ) ) END "IMPORTE FROM BSEG AS BS

INTO TABLE @GT_MOVTOSCTE 

WHERE BS~BUKRS EQ '2000' 

AND BS~KOART EQ 'D' 

AND BS~H_BSTAT NE 'D' 

AND BS~H_BSTAT NE 'M' AND BS~AUGBL = '' 

AND BS~DMBTR GT 0 

AND BS~KUNNR IN ( SELECT KN~KUNNR "CLIENTE FROM KNVP AS KN

 WHERE KN~PARVW EQ 'Z8' AND KN~KUNNR NOT BETWEEN '0000300000' 

AND '0000399999' 

GROUP BY KN~KUNNR ).

Error Executing:

Texto breve SQL error "SQL code: 314" occurred while accessing table "BSEG".

¿Qué ha sucedido?

Database error text: "SQL message: numeric overflow: the precision of the decimal value is larger than the target precision: 1 (at pos 151) "

¿Qué puede hacer?

Note which actions and entries caused the error to occur.

Consult your SAP administrator.

Using transaction ST22 for ABAP dump analysis, you can view, manage, and retain termination messages for longer periods. Note which actions and entries caused the error to occur. Consult your SAP administrator. Using transaction ST22 for ABAP dump analysis, you can view, manage, and retain termination messages for longer periods.

[timmcardle]

It works for me as below. Nice answer to the problem. Thanks!

DATA minus_one type p decimals 0 value '1-'.

Select....

BSEG~DMBTR * cast( @minus_one as dec( 1 ) )

from BSEG as bseg

[former_member814031]

Check keyword spelling by referring to the documentation for the type of SQL you are using.

Check table spelling by referring to the database schema.

Check column spelling by referring to the database schema or doing SELECT * FROM the table you are trying to check the column name on.

Regards,

Rachel Gomez