Skip to Content

decimal value in ABAP SQL statement

Is there something specific what doesn't allow me to use decimal in select statement in ABAP. I tested code below and works well but I need 0.9 (90% of price) where is currently 9. Like I can't use 0.9, 0.9 or even '0.9'. I've tried to use div(price*9, 10) but got message price is really decimal. Cast also didn't give results? Thanks in advance

METHOD get_cart."USING nEW oPEN sqlSELECT zcart_155~product,description,quantity,uom,price,case when price >= 100 then price * 9 else price end as totalpriceFROM zcart_155
INNER JOIN zproduct_155ON zcart_155~product = zproduct_155~product
INNER JOIN zproduct_text155ON zcart_155~product = zproduct_text155~productWHERE zproduct_text155~language = @sy-languAND zcart_155~customer = @d_customerINTO TABLE @rt_cart.ENDMETHOD.

Add comment
10|10000 characters needed characters exceeded

  • Sorry, I missed your comment:

    No error but wasn't able to do what I wish through add product , probably due I can't use field totalprice which is 0 until I launch get cart and build in select but not fill.

    Also I am a bit wondering to know why implementation of

    LOOPAT rt_cart ASSIGNINGFIELD-SYMBOL(<ls_cart>).<ls_cart>-cost =<ls_cart>-quantity *<ls_cart>-totalprice.ENDLOOP.

    sometimes works and show results while sometimes I have to launch get cart few times to see results.

    I you allow in next comment I will send all tables, codes and my comments what I wish to do.

    I a not sure if it is allowed but I am ready to pay something for your help.

  • Hi, I am a bit wondering regarding sort of comments, but in attachments is document with code and screens as well as comments what I've tried to do and which problem have had.

    I kindly ask you to support me and I am not sure how but I am ready to pay.

    picture1.jpgpicture2.jpgpicture3.jpgpicture4.jpgpicture5.jpg

    Hope txt and jpeg will have success.

    Problem with txt

    Below code

    Rgs

    picture3.jpg (42.7 kB)
    picture4.jpg (42.9 kB)
  • "BELOW ARE ALL CODES - COMMENTS AND EXPLANATIONS ARE MARK WITH"
    ZCL_SHOPPING_CART155 Global Class Codes:
    class ZCL_SHOPPING_CART155 definition
      public
      final
      create public .
    public section.
    TYPES:BEGIN OF y_cart,
      product TYPE zproduct_155-product,
      description TYPE zproduct_text155-description,
      quantity type zcart_155-quantity,
    cost type zcart_155-cost,
    totalprice type zcart_155-totalprice,
      END of y_cart,
      yt_cart TYPE STANDARD TABLE OF y_cart WITH KEY product.
    METHODS:
    constructor IMPORTING ip_customer TYPE scustom-id,
    add_product IMPORTING ip_product TYPE zproduct_155-product
     ip_quantity TYPE zcart_155-quantity optional
      ip_cost TYPE zcart_155-cost optional ,
     remove_product IMPORTING ip_product
      TYPE zproduct_155-product,
    clear_cart,
    get_cart RETURNING VALUE(rt_cart) TYPE yt_cart.
    protected section.
    private section.
    DATA d_customer TYPE scustom-id.
    ENDCLASS.
    CLASS ZCL_SHOPPING_CART155 IMPLEMENTATION.
    
    * <SIGNATURE>---------------------------------------------------------------------------------------+
    * | Instance Public Method ZCL_SHOPPING_CART155->ADD_PRODUCT
    * +-------------------------------------------------------------------------------------------------+
    * | [--->] IP_PRODUCT                     TYPE        ZPRODUCT_155-PRODUCT
    * | [--->] IP_QUANTITY                    TYPE        ZCART_155-QUANTITY(optional)
    * | [--->] IP_COST                        TYPE        ZCART_155-COST(optional)
    * +--------------------------------------------------------------------------------------</SIGNATURE>
         METHOD add_product.
    data lv_num type p DECIMALS 2 value '0.90'.
    DATA: ls_cart TYPE zcart_155.
          ls_cart-customer = d_customer.
          ls_cart-product = ip_product.
          If ip_quantity is not initial.
            ls_cart-quantity = ip_quantity.
            ELSE.
              ls_cart-quantity = 1.
              endif.
    "Here my idea was to fill also 2 fields in table ZCART:
    TOTAL PRICE should be filled:
    IF zproduct_155-price >=100 ls_cart-totalprice=0.9* zproduct_155-price
    Else ls_cart-totalprice= zproduct_155-price
    COST should be filled:
    ls_cart-cost=ls_cart-totalprice * ls_cart-quantity
    Didn’t get results due any attempt produce error regarding referencing and using parameters or 
    system didn’t show error message but field total price was empty."
    MODIFY zcart_155 FROM ls_cart.
          ENDMETHOD.
    
    * <SIGNATURE>---------------------------------------------------------------------------------------+
    * | Instance Public Method ZCL_SHOPPING_CART155->CLEAR_CART
    * +-------------------------------------------------------------------------------------------------+
    * +--------------------------------------------------------------------------------------</SIGNATURE>
    METHOD clear_cart.
          DELETE FROM zcart_155 WHERE customer = d_customer.
           ENDMETHOD.
    
    * <SIGNATURE>---------------------------------------------------------------------------------------+
    * | Instance Public Method ZCL_SHOPPING_CART155->CONSTRUCTOR
    * +-------------------------------------------------------------------------------------------------+
    * | [--->] IP_CUSTOMER                    TYPE        SCUSTOM-ID
    * +--------------------------------------------------------------------------------------</SIGNATURE>
      METHOD constructor.
        d_customer = ip_customer.
        ENDMETHOD.
    
    * <SIGNATURE>---------------------------------------------------------------------------------------+
    * | Instance Public Method ZCL_SHOPPING_CART155->GET_CART
    * +-------------------------------------------------------------------------------------------------+
    * | [<-()] RT_CART                        TYPE        YT_CART
    * +--------------------------------------------------------------------------------------</SIGNATURE>
    METHOD get_cart.
    DATA lv_num TYPE p DECIMALS 2.
    lv_num = '0.9'.
    
      "USING nEW oPEN sql
     SELECT zcart_155~product,description,quantity,price,
    
    
    Case when price >= 100 then  cast( price as dec ) * @lv_num else cast( price as dec ) end as totalprice
    FROM zcart_155
    INNER JOIN zproduct_155
    ON zcart_155~product = zproduct_155~product
             INNER JOIN zproduct_text155
             ON zcart_155~product = zproduct_text155~product
             WHERE zproduct_text155~language = @sy-langu
             AND zcart_155~customer = @d_customer
             INTO TABLE @rt_cart.
    
    
    LOOP AT rt_cart ASSIGNING FIELD-SYMBOL(<ls_cart>).
      <ls_cart>-cost = <ls_cart>-quantity * <ls_cart>-totalprice.
    ENDLOOP.
    
    
    "Here we’ve implemented in some way what we wish to do under add_product, but this is just display without really fill data in table zcart.
    LOOP sometimes provide results, sometimes no (in view some kind of delay) as well as get cart doesn’t present data correctly if
    I try to change order of last 2 columns.
    If we can add 2 fields through add_product, I think we may exclude loop and use ordinary 
    select without case when due value already will be stored in zcart under this conditions."
    
              endmethod.
    
    
    
    * <SIGNATURE>---------------------------------------------------------------------------------------+
    * | Instance Public Method ZCL_SHOPPING_CART155->REMOVE_PRODUCT
    * +-------------------------------------------------------------------------------------------------+
    * | [--->] IP_PRODUCT                     TYPE        ZPRODUCT_155-PRODUCT
    * +--------------------------------------------------------------------------------------</SIGNATURE>
    METHOD remove_product.
          DATA: ls_cart TYPE zcart_155.
          ls_cart-customer = d_customer.
          ls_cart-product = ip_product.
          DELETE zcart_155 FROM ls_cart.
          ENDMETHOD.
    ENDCLASS.
    
    
    ZCart_155 Codes:
    REPORT zcart_maint155.
    SELECTION-SCREEN BEGIN OF BLOCK cart
      WITH FRAME TITLE text-001.
    PARAMETERS:p_cust TYPE zcart_155-customer OBLIGATORY,
               p_prod TYPE zcart_155-product,
               p_qty type zcart_155-quantity.
    SELECTION-SCREEN END OF BLOCK cart.
    
    
    
    
    SELECTION-SCREEN BEGIN OF BLOCK action
      WITH FRAME TITLE text-002.
    PARAMETERS:p_view TYPE boolean RADIOBUTTON GROUP act,
               p_add TYPE boolean RADIOBUTTON GROUP act,
               p_rem TYPE boolean RADIOBUTTON GROUP act,
               p_clear TYPE boolean RADIOBUTTON GROUP act.
    SELECTION-SCREEN END OF BLOCK action.
    
    
    DATA: o_cart TYPE REF TO zcl_shopping_cart155.
    CREATE OBJECT o_cart EXPORTING ip_customer = p_cust.
    IF p_view = abap_true.
    DATA(t_cart) =
    o_cart->get_cart( ).
    WRITE:'View'.
      cl_salv_table=>factory(
      IMPORTING r_salv_table = DATA(gr_alv)
        CHANGING t_table = t_cart ).
      gr_alv->display( ).
      elseif p_add  = abap_true.
        o_cart->add_product( ip_product = p_prod ip_quantity = p_qty ).
        WRITE:'Product Add'.
        ELSEIF p_rem = abap_true.
          o_cart->remove_product( p_prod ).
          WRITE: 'product Removed'.
          ELSEIF p_clear = abap_true.
            o_cart->clear_cart( ).
            WRITE:'Cart Cleared'.
    ENDIF.
    Cart Interface:
    " PICTURE1
    For this display I wish to change order of last 2 columns due cost=quantity*discount."
    ZProduct_155 Codes:
    REPORT ZPRODUCT_MAINT155.
    
    
    
    
    SELECTION-SCREEN BEGIN OF BLOCK product
      WITH FRAME TITLE text-001.
    PARAMETERS: p_prod TYPE zproduct_155-product,
                p_desc TYPE zproduct_text155-description LOWER CASE,
                p_price TYPE zproduct_155-price,
                p_curr TYPE zproduct_155-currency,
                p_uom TYPE zproduct_155-uom.
    
    
    SELECTION-SCREEN END OF BLOCK product.
    
    
    DATA: s_product TYPE zproduct_155,
          s_product_text type zproduct_text155.
    s_product_text-product = p_prod.
    s_product-price = p_price.
    s_product-currency = p_curr.
    s_product-uom = p_uom.
    s_product_text-language = sy-langu.
    s_product_text-description = p_desc.
    
    SELECTION-SCREEN BEGIN OF BLOCK action
      WITH FRAME TITLE text-002.
    PARAMETERS: p_upd TYPE boolean RADIOBUTTON GROUP act,
                p_rem TYPE boolean RADIOBUTTON GROUP act,
                p_dis TYPE boolean RADIOBUTTON GROUP act.
    SELECTION-SCREEN END OF BLOCK action.
    IF p_upd = abap_true.
    INSERT zproduct_155 FROM s_product.
    INSERT zproduct_text155 FROM  s_product_text.
    MODIFY zproduct_155 FROM  s_product.
     MODIFY zproduct_text155 FROM  s_product_text.
      WRITE: 'Update Completed'(003).
      ELSEIF p_rem = abap_true.
        DELETE FROM zproduct_text155 WHERE product = p_prod.
        DELETE zproduct_155 FROM s_product.
        WRITE: 'DELETE COMPLETED'(004).
        ELSEIF p_dis = abap_true.
          TYPES: BEGIN OF y_products,
            product TYPE zproduct_155-product,
            description TYPE zproduct_text155-description,
            price       TYPE zproduct_155-price,
            currency    TYPE zproduct_155-currency,
            uom         TYPE zproduct_155-uom,
            End OF y_products.
         Data: t_products TYPE STANDARD TABLE OF y_products.
          SELECT zproduct_155~product,description, price, currency, uom
            INTO TABLE @t_products
            FROM zproduct_155
            INNER JOIN zproduct_text155
            ON zproduct_155~product = zproduct_text155~product
            WHERE language = @sy-langu.
    
    
            cl_salv_Table=>factory(
    IMPORTING r_salv_table = DATA(gr_alv)
    CHANGING t_table = t_products ) .
    Gr_alv->display( ).
          ENDIF.
    "Product Interface:
     PICTURE2
    zCart_155 Table:
     PICTURE3
    In some way quantity is referenced to uom in table product but didn’t find way to reference totalprice to price from table product.
    ZProduct_155 Table:
     PICTURE4
    Zproduct_Text155  Table:
     PICTURE5"
    
    "Next step is to implement some report (classic, interactive, ALV).
    I did something through quick viewer but not sure where to implement to be launch through 
    user interface as well as I did it only for one table due I got a lot of errors when I try to do it for table zcart.
    Best thing will be to do it something to present (product ,price,quantity,cost,discount) as 
    already in get_cart, but when click on product to show new column description from table zproduct_text155."
    
    
    
    
  • Get RSS Feed

2 Answers

  • Best Answer
    Apr 19 at 08:15 PM

    Hello Radomir Brzakovic,

    you can use decimal or float as you want, but you have to take in consideration the CAST. you have to cast the data type to do your operations in the SQL-Expression.

    METHOD get_cart."USING nEW oPEN sql
    
    
    DATA lv_num TYPE p DECIMALS 2.
    lv_num = '0.9'.
    
    
    SELECT zcart_155~product,description,quantity,uom,price,
    case when price >= 100 
    then cast( price as dec ) * @lv_num 
    else cast ( price as dec ) 
    end as totalprice
    FROM zcart_155
    INNER JOIN zproduct_155ON zcart_155~product = zproduct_155~product
    INNER JOIN zproduct_text155ON zcart_155~product = zproduct_text155~product
    WHERE zproduct_text155~language = @sy-languAND 
    zcart_155~customer = @d_customer
    INTO TABLE @rt_cart.
    ENDMETHOD.
    
    
    
    REPORT ziob_test2.
    
    DATA lv_num TYPE p DECIMALS 2.
    lv_num = '0.9'.
    
    SELECT carrid, connid, price,
      CASE WHEN price >= 100
    THEN  CAST( price AS DEC ) * @lv_num
    ELSE CAST( price AS DEC )
    END AS totalprice
    FROM sflight
    
    INTO TABLE @DATA(rt_cart).
    
    BREAK-POINT.
    
    

    I hope I could help you.

    Best regards.

    Erbahim


    1.jpg (68.0 kB)
    Add comment
    10|10000 characters needed characters exceeded

  • Apr 22 at 07:11 AM

    Hallo Radomir Brzakovic,

    you know what does it mean this code?

    Case  when price >= 100 then 
    cast( price as dec ) * @lv_num 
    else 
    cast( price as dec ) end as totalprice, 
    quantity * totalprice as cost FROM zcart_155
    
    " in this column total price, the total price would be taken from table and not " from SQL Arithmetic Operators. 

    SQL Arithmetic Operators You can not do it only by adding @ before the field. quantity * @totalprice--> in this case, you can not do it also because it is a column in your table.

    So you can update your internal table with column cost by using loop.

    LOOP AT rt_cart ASSIGNING FIELD-SYMBOL(<ls_cart>).
      <ls_cart>-cost = <ls_cart>-quantity * <ls_cart>-totalprice.
    ENDLOOP. 
    

    Best regards

    Ebrahim

    Add comment
    10|10000 characters needed characters exceeded

    • thanks a lot.

      which mistake I did in add_product part where I've tried to add value price in zcart based on price >=100 in table product

      {Here I wish to fill table zcart_155-totalprice with value which already existing in table zprocut_155-price. When I add ip_price and did some definition nothing happened. When I try to define in first part of code and send together with values from form I got error. Last attempt was to use zproduct_155.price instead ip price but get error. Idea is when user insert customer, product and quantity on form to check price in table product for certain product and use that price to fill totalprice in table zcart depends on rule >=100. After that wish to fill field cost in table zcart with totalprice*quantity}

      If ip_price >= 100.
      ls_cart-totalprice = lv_num * ip_price .ELSE.
      ls_cart-totalprice = ip_price .endif.
      ls_cart-cost = ls_cart-quantity * ls_cart-totalprice.