decimal value in ABAP SQL statement

[Former Member]

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.

[former_member184158]

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

[Former Member]

Thanks a lot.

I didn't find anywhere idea for cast.

If you allow I wish to ask you one more question.

[Former Member]

With this method I add data in table zcart.

Works well but now I wish to add value from table zproduct into table zcart.

METHOD add_product.
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.

ls_cart-cost= ip_price '' here I try to add just same value as in table product and should be similar as ls_cart-product = ip_product."

If ip_price>=100 . "here I have idea to check price like in previous example and to fill new one field in table zcart depending on price value"
ls_cart-totalprice = 0.9 * ip_price.ELSE.

s_cart-totalprice = 0.9 * ip_price.

endif.

MODIFY zcart_155 FROM ls_cart.ENDMETHOD.

[former_member184158]

you can do like this

ls_cart-totalprice = '0.9' * ip_price.

the dot . means in ABAP end of the Statement, so you can store it as number or between Single quotes.

data lv_num type p DECIMALS 2 value '0.90'.
ls_cart_totalprice = lv_num * ip_price.

[Former Member]

Thanks a lot

[Former Member]

You are amazing but still need your help.

What is better for you to post new question, continue with comments or even if I can contact you and maybe we can make deal. For now in comment

Below is full code and in brackets situation with it :

*&---------------------------------------------------------------------**& Report ZCART_MAINT155*&*&---------------------------------------------------------------------**&*&*&---------------------------------------------------------------------*REPORT zcart_maint155.SELECTION-SCREEN BEGIN OF BLOCK cartWITH 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 actionWITH 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.

class ZCL_SHOPPING_CART155 definitionpublic
finalcreate 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_productTYPE 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_PRICE TYPE ZPRODUCT_155-PRICE* | [--->] 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 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.


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'.

{in this part everything works well but cost is 0 after launch this}
"USING nEW oPEN sqlSELECT zcart_155~product,description,quantity,
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
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.

* <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.

[Sandra_Rossi]

Radomir Brzakovic There is the button CODE to format your code.

[Sandra_Rossi]

There is the button CODE to format your code.

[former_member184158]

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

[Former Member]

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.

[former_member184158]

Hi

so what it is the error you get?

[Former Member]

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.

[Former Member]

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

[Former Member]

"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."