1.
change procedure with error handling code
alter procedure dectab()
language sqlscript as
begin
declare i integer;
declare exit handler for sql_error_code 288
select 'There is an object already exist with same name, please
update procedure' from dummy;
create table decemp (id integer, name varchar(30), primary key(id));
for i in 0..10 do
insert into decemp values( :i,'Employee ' || :i);
end for;
end;
2. Generic way of handling any kind of error when error code is unknown
alter procedure dectab()
language sqlscript as
begin
declare i integer;
declare exit handler for sqlexception
select 'There is an error occured please contact development team'
as err, ::SQL_ERROR_CODE as err_code,
::SQL_ERROR_MESSAGE as err_msg
from dummy;
create table decemp (id integer, name varchar(30), primary key(id));
for i in 0..10 do
insert into decemp values( :i,'Employee ' || :i);
end for;
end;
3. How to check hana tables
select * from tables where table_name = 'DECEMP'
4. For loop
alter procedure calcfact(inp integer)
language sqlscript as
begin
declare i, fact integer;
fact = 1;
for i in 1..inp do
fact = fact * :i;
end for;
select fact from dummy;
end;
5. if condition
create procedure evenodd(inp integer, out result varchar(10))
language sqlscript as
begin
if mod(inp,2) = 0 then
result = 'Even';
else
result = 'Odd';
end if;
end;
6. While loop
create procedure whilelop(inp integer, out result decimal(10,2))
language sqlscript as
begin
declare counter integer;
counter = inp;
result = 0;
while :counter > 0 do
result = :result + ( :counter * 10 );
counter = :counter - 1 ;
end while;
end;
7. Arrary basics
create procedure arrbasic(out result integer)
language sqlscript as
begin
declare arr_1 integer array := array(50,60,80,90);
declare arr_2 integer array := array(3,2,1,4);
result = :arr_1[:arr_2[3]] ;
end;
8. working with cursors (parameterized)
create procedure readusingcursor(in icate varchar(80),
out p1 varchar(40), out p2 varchar(40))
language sqlscript
default schema saphanadb as
begin
declare cursor anubhav(pcat varchar(80)) for
select product_id from snwd_pd where category = :pcat;
open anubhav(:icate);
--fetches the first record's pid and sets to my output variable
fetch anubhav into p1;
--fetches the first record's pid and sets to my output variable
fetch anubhav into p2;
end;
9. working with cursors and multiple records with an array
alter procedure readusingcursor(in icate varchar(80),
out p1 varchar(40), out p2 varchar(40))
language sqlscript
default schema saphanadb as
begin
declare arr_pid varchar(40) array;
declare cursor anubhav(pcat varchar(80)) for
select product_id from snwd_pd where category = :pcat;
for my_record as anubhav(:icate) do
arr_pid[anubhav::rowcount] := my_record.product_id;
end for;
p1 = :arr_pid[1];
p2 = :arr_pid[3];
end;
10. Working with output tables and arrys which hold the data records
alter procedure readusingcursor(in icate varchar(80),
out kanishka table(product_id varchar(40)))
language sqlscript
default schema saphanadb as
begin
declare arr_pid varchar(40) array;
declare cursor anubhav(pcat varchar(80)) for
select product_id from snwd_pd where category = :pcat;
for my_record as anubhav(:icate) do
arr_pid[anubhav::rowcount] := my_record.product_id;
end for;
kanishka = unnest(:arr_pid) as (product_id);
end;
11. working with simple table output
create procedure simpletab(out employees
table( empid integer,
empname varchar(80),
salary integer,
curr varchar(3)
))
language sqlscript
default schema saphanadb as
begin
:employees.insert( (100, '�nubhav', 5000, 'EUR'), 1 );
:employees.insert( (200, 'Rohan', 4780, 'EUR'), 2 );
:employees.insert( (300, 'Sonia', 9888, 'USD'), 3 );
end;
12. Loop at itab example where we map data from one table to another
alter procedure loopatitab(in icountry varchar(2), out partners
table( bp_id integer,
company_name varchar(80),
currency_code varchar(3)
))
language sqlscript
default schema saphanadb as
begin
declare rec_count,i integer;
lt_bp = select bp_id, company_name, currency_code from
snwd_bpa as bp inner join snwd_ad as ad
on bp.address_guid = ad.node_key where ad.country = icountry;
rec_count = record_count(:lt_bp);
for i in 1..:rec_count do
:partners.insert( (:lt_bp.bp_id[i],
:lt_bp.company_name[i],
:lt_bp.currency_code[i] ) , :i);
end for;
end;
13. create reusable table type
create type tt_anu as table (bp_id integer,
company_name varchar(80),
currency_code varchar(3)
)
14. using table type
alter procedure loopatitab(in icountry varchar(2), out partners
mob5.tt_anu)
language sqlscript
default schema saphanadb as
begin
declare rec_count,i integer;
lt_bp = select bp_id, company_name, currency_code from
snwd_bpa as bp inner join snwd_ad as ad
on bp.address_guid = ad.node_key where ad.country = icountry;
rec_count = record_count(:lt_bp);
for i in 1..:rec_count do
:partners.insert( (:lt_bp.bp_id[i],
:lt_bp.company_name[i],
:lt_bp.currency_code[i] ) , :i);
end for;
end;
15. current system details
select session_context('CLIENT') as clnt,
session_context('APPLICATIONUSER') as usr from dummy
select session_context('CLIENT') as clnt,
ucase(session_context('APPLICATIONUSER')) as usr from dummy
16. Complete OIA scenario using SQL Script
alter procedure decoia(out etoia mob5.tt_oia)
language sqlscript
default schema saphanadb as
begin
declare lv_client varchar(3);
declare lv_to_curr varchar(3);
declare lv_today date;
declare lv_user varchar(10);
declare lv_th_gross decimal(15,2);
declare lv_th_days integer;
select current_date into lv_today from dummy;
select session_context('CLIENT'),
ucase(session_context('APPLICATIONUSER'))
into lv_client, lv_user from dummy;
select mandt, currency_code, max_gross_amount,
max_open_days into lv_client, lv_to_curr, lv_th_gross, lv_th_days
from zdp_cust where id = :lv_user;
lt_open = select bp.bp_id, bp.company_name,
floor( seconds_between(
to_timestamp(left(inv.changed_at,14),'YYYYMMDDHHMISS'),
to_timestamp(localtoutc(now(),'CET'))
) / ( 24 * 60 * 60 ) ) as open_days
from snwd_so_inv_head as inv inner join snwd_bpa as bp
on inv.buyer_guid = bp.node_key where
inv.payment_status = '' and
bp.client = :lv_client;
lt_open_days = select bp_id, avg(open_days) as open_days from :lt_open group by
bp_id;
lt_all_amount = select bp_id, company_name, sum( item.gross_amount ) as
gross_amount,
item.currency_code from snwd_so_inv_item as item inner join snwd_so_inv_head as
head
on item.parent_key = head.node_key inner join snwd_bpa as bpa
on bpa.node_key = head.buyer_guid
where head.payment_status = '' and
bpa.client = :lv_client group by bp_id, company_name, item.currency_code ;
lt_amounts = CE_CONVERSION(
:lt_all_amount,
[
family = 'currency',
method = 'ERP',
steps = 'shift,convert,shift_back',
client = :lv_client,
source_unit_column =
'CURRENCY_CODE',
target_unit = :lv_to_curr,
reference_date = :lv_today,
output_unit_column = 'CURR_CODE_OP'
], [gross_amount]
);
lt_final_gross = select bp_id, company_name, sum( gross_amount )
as gross_amount, :lv_to_curr as currency_code from :lt_amounts
group by bp_id, company_name, :lv_to_curr;
lt_oia = select snwd_bpa.bp_id, gross.company_name, open_days as open_days,
gross.gross_amount as gross_amount,
gross.currency_code as currency_code,
'' as tagging from snwd_bpa inner join :lt_open_days as dats
on snwd_bpa.bp_id = dats.bp_id inner join :lt_final_gross as gross
on gross.bp_id = snwd_bpa.bp_id ;
etoia = select bp_id, company_name, open_days, gross_amount,
currency_code, case when gross_amount > :lv_th_gross and
open_days > :lv_th_days then
'X'
else '' end as tagging
from :lt_oia;
end;
