how to assign a column value to variable in SAP HANA

[former_member383962]

hi,

here i tried to give discount based on quantity ordered by using procedure. but i dont know how assign a value of column to the variable in procedure.

here my code:

create column table "KABIL_PRACTICE"."SALES_IF_ELSE"
(
"SALES_ID" integer,
"PRODUCT_ID" integer,
"QTY" integer,
"DISCOUNT" integer,
"SALES_AMOUNT" Double
);

insert into "KABIL_PRACTICE"."SALES_IF_ELSE" ("SALES_ID","PRODUCT_ID","QTY","SALES_AMOUNT") values (1,101,15,1500);
insert into "KABIL_PRACTICE"."SALES_IF_ELSE" ("SALES_ID","PRODUCT_ID","QTY","SALES_AMOUNT") values (2,102,25,2500);
insert into "KABIL_PRACTICE"."SALES_IF_ELSE" ("SALES_ID","PRODUCT_ID","QTY","SALES_AMOUNT") values (3,103,35,3500);
insert into "KABIL_PRACTICE"."SALES_IF_ELSE" ("SALES_ID","PRODUCT_ID","QTY","SALES_AMOUNT") values (4,104,40,4000);
insert into "KABIL_PRACTICE"."SALES_IF_ELSE" ("SALES_ID","PRODUCT_ID","QTY","SALES_AMOUNT") values (5,105,27,2700);
insert into "KABIL_PRACTICE"."SALES_IF_ELSE" ("SALES_ID","PRODUCT_ID","QTY","SALES_AMOUNT") values (6,106,32,3200);
insert into "KABIL_PRACTICE"."SALES_IF_ELSE" ("SALES_ID","PRODUCT_ID","QTY","SALES_AMOUNT") values (7,107,19,1900);


create procedure "KABIL_PRACTICE"."IF_ELSE_DISC"
 language sqlscript
 as begin
  declare QTY integer ;
   select "QTY" from "KABIL_PRACTICE"."SALES_IF_ELSE" := QTY;
 if ( QTY > 25)
 then
 update "KABIL_PRACTICE"."SALES_IF_ELSE" set "DISCOUNT" = 5;
 else if
 update "KABIL_PRACTICE"."SALES_IF_ELSE" set "DISCOUNT" = 1;
 end if;
 end if;
 end;

anyone help me....

[pfefferf]

Can you please explain a little bit more about that what you wanna reach? Do you wanna calculate the discount for a single entry (if yes, where is the restriction criteria) or do you wanna update the discount for all entries?

In your current example it does not make much sense to try to read a single "QTY" value, because the result will always be an array instead of a single value.

Regards,
Florian

[former_member383962]

Hi Florian,

i just want to know how to assign column values to the variable which is I declare here as "QTY"...

[lbreddemann]

I've already provided you with the answer (https://stackoverflow.com/questions/45832931/how-to-assign-a-variable-in-procedure-in-sap-hana):

This is a classic case of not using SQL enough but instead trying to force an imperative programming style on a SQL database.

All you want to do is update a table based on a condition, isn't it?

In that case, there is no need for procedural logic at all (and that's true for any SQL database).

UPDATE "KABIL_PRACTICE"."SALES_IF_ELSE" 
SET "DISCOUNT"= (CASE 
                     WHEN "QTY" >25 THEN 2
                     ELSE 1
                 END);

This will store the computed "DISCOUNT" value in the table with no looping, in a single command.

In case this is not what you want to do, please refine the problem description.