how to assign a variable in procedure in SAP HANA

Question

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

score 2 · Answer 1 · answered Aug 23 '17 at 23:27

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.

score 1 · Answer 2 · edited Aug 28 '17 at 10:04

1

You can assign variables by selecting columns into them. For example:

SELECT QTY INTO QTY FROM KABIL_PRACTICE.SALES_IF_ELSE

edited Aug 28 '17 at 10:04

Rohit Poudel

1,793
2
20
24

answered Aug 28 '17 at 08:14

DaveBeck

11
2

Hi thanks for your reply, but here i used this code: 'QTY = select "QTY" from "KABIL_PRACTICE"."SALES_IF_ELSE"; if ( QTY > 25)' but it shows an error like this: inconsistent datatype: INT type is not comparable with TABLE type. – KABIL ARASAN Aug 29 '17 at 08:59
Could you explain a bit more about why you did this? The error occurred because you are comparing a temporary table to a single integer. You need to use a variable instead of a table if you want to evaluate against an integer. – DaveBeck Aug 30 '17 at 09:35
hi Dave, i just want to check the values from the "QTY" column and if the quantity is greater than 25, I would like to give the discount . – KABIL ARASAN Aug 30 '17 at 12:06

how to assign a variable in procedure in SAP HANA

2 Answers2