CODEWITHSUNDEEP
hanahana-sql-script
j.doe
j.doe

Reputation: 327

How to convert MS SQL Server code into SAP HANA code?

Hello I am new in SAP HANA and I have this T-SQL code:

`if(@IsBes='E')begin

               if((select top 1 U_bBesYontem from OHEM where empID=@EmpId)='O') begin

                     set @Bes=@Brut*(select top 1 U_bBesOran from OHEM where empID=@EmpId)/100

                  end

               if((select top 1 U_bBesYontem from OHEM where empID=@EmpId)='T') begin

                     set @Bes=(select top 1 U_bBesTutar from OHEM where empID=@EmpId)

                  end

         end`

And I am trying this code for SAP HANA

IF (:IsBes = 'E') THEN IF ((SELECT TOP 1 “U_bBesYontem" FROM OHEM WHERE "empID" = :EmpId) = 'O') THEN Bes := :Brut * (SELECT TOP 1 "U_bBesOran" FROM OHEM WHERE "empID" = :EmpId) / 100; END IF; IF ((SELECT TOP 1 "U_bBesYontem" FROM OHEM WHERE "empID" = :EmpId) = 'T') THEN SELECT (SELECT TOP 1 "U_bBesTutar" FROM OHEM WHERE "empID" = :EmpId) INTO Bes FROM DUMMY; END IF; END IF;

And tried lots of code variations but I always end up getting this error:

SAP DBTech JDBC: [7]: feature not supported:
 Sub-query is not supported in a nested procedure call: 
 line 47 col 28 (at pos 2273)

I am confused so can somebody give any advise ?

Upvotes: 0

Views: 2594

Answers (1)

Eralper
Eralper

Reputation: 6622

Merhaba,

Could you please execute following SQLScript on a test database

do
begin

declare lv_EmpId int := 1;
declare lv_IsBes char(1) := 'E';
declare lv_Bes decimal(10,2); 
declare lv_Brut decimal(10,2) := 100;
declare lv_U_bBesYontem nvarchar(100);
declare lv_U_bBesOran int;
declare lv_U_bBesTutar int;

select top 1 
    U_bBesYontem, U_bBesOran, U_bBesTutar
into 
    "LV_U_BBESYONTEM", "LV_U_BBESORAN", "LV_U_BBESTUTAR"
from OHEM where EmpId = lv_EmpId;

if (:lv_IsBes = 'E') then

    if(:lv_U_bBesYontem = 'O') then
        lv_Bes = ( :lv_Brut * :lv_U_bBesOran ) /100;
    else
        if (:lv_U_bBesYontem = 'T') then
            lv_Bes = :lv_U_bBesTutar;
        end if;
    end if;

end if;

select :lv_Bes as "BES" from dummy;

end;

I had used following sample data and it seems to work quite fine, please modify the script according to your table and fields names, etc.

CREATE Column TABLE OHEM (
    empID int,
    U_bBesYontem varchar(10),
    U_bBesOran int,
    U_bBesTutar int
);
insert into OHEM select 1, 'O',5,1000 from dummy;
insert into OHEM select 2, 'O',10,500 from dummy;
insert into OHEM select 1, 'T',5,1000 from dummy;
insert into OHEM select 2, 'T',10,500 from dummy;

You can refer to variable declarations, value assignment, IF clause etc on SQLScript at given sample document.

Upvotes: 1

Related Questions