How to Insert Value to FUNCTION'S TABLE

Question

My functions is here ;

CREATE FUNCTION "HS_getMaasHesapla"(EmpId INT,MaasTuru VARCHAR(1),Donem VARCHAR(50),SablonCode VARCHAR(50),
       Maas DECIMAL(18,6),Brut DECIMAL(18,6),Pek DECIMAL(18,6),NetEkKazanc DECIMAL(18,6),BrutEkKazanc DECIMAL(18,6), 
       EkKesinti DECIMAL(18,6),DvrGvMatrah DECIMAL(18,6),SonBrutMaas DECIMAL(18,6),HesaplananMatrah DECIMAL(18,6),
       IzinTutar DECIMAL(18,6),MesaiTutar DECIMAL(18,6),Agi DECIMAL(18,6),IsBes VARCHAR(1),IsIcra VARCHAR(1),
       donembasi DATE,donemsonu DATE)


RETURNS  TABLE
(
    EmpId INT, GVMatrah DECIMAL(18,6),Brut DECIMAL(18,6),SGKIsv DECIMAL(18,6),IszCal DECIMAL(18,6),
    IszIsv DECIMAL(18,6),SGKCal decimal(18,6),GV decimal(18,6),DV decimal(18,6),Net decimal(18,6),
    EkKazanc decimal(18,6),Pek decimal(18,6),IzinTutar decimal(18,6),Bes decimal(18,6),Icra decimal(18,6),
    Odenen decimal(18,6),Maliyet decimal(18,6),EkKesinti decimal(18,6),Mesai decimal(18,6)
    ,IcraOran decimal(18,6),DosyaNumarasi varchar(50)
)
LANGUAGE SQLSCRIPT
SQL SECURITY DEFINER AS
BEGIN
DV := "HS_DVTutar"(:Brut, :donembasi, :donemsonu);
IF (:MaasTuru = 'B') THEN Net := (:Brut) - :SGKCal - :IszCal - :GV - :DV;
EkKazanc := :BrutEkKazanc;
Pek := :Brut + :Pek;
ELSE Net := (:Brut) - :SGKCal - :IszCal - :GV - :DV;
EkKazanc := :BrutEkKazanc + :NetEkKazanc;
Pek := :Brut;
END IF;
Net := round(:Net, 2);
IF (:Icra > 0) THEN Icra := (:Net * :IcraOran) / 100.00;
END IF;
Odenen := :Net + :Agi - :Icra - :Bes - :EkKesinti;
Maliyet := :Brut + :SGKIsv + :IszIsv;
INSERT INTO TABLE("EmpId" , "GVMatrah", "Brut", "SGKIsv", "IszCal", "IszIsv", "SGKCal", "GV", "DV", "Net", "EkKazanc", "Pek", "IzinTutar", "Bes", "Icra", "Odenen", "Maliyet", "EkKesinti", "Mesai", "IcraOran", "DosyaNumarasi") VALUES (round("GvMatrah", 2), round(:Brut, 2), round(:SGKIsv, 2), round(:IszCal, 2), round(:IszIsv, 2), round(:SGKCal, 2), round(:GV, 2), round(:DV, 2), round(:Net, 2), round(:EkKazanc, 2), round(:Pek, 2), round(:IzinTutar, 2), round(:Bes, 2), round(:Icra, 2), round(:Odenen, 2), round(:Maliyet, 2), round(:EkKesinti, 2), round(:MesaiTutar, 2), round(:IcraOran, 2), round(:DosyaNumarasi, 2));
RETURN;
END;

But my error is here:

Could not find table/view TABLE in schema SBODEMOTR: line 79 col 13 (at pos 3861)

On the other hand I can not use Select; Could you show me how I can do this?

score 0 · Accepted Answer · answered Mar 06 '18 at 08:00

I think your message means that you need a table called TABLE to be created in your SBODEMOTR schema to be able to use in the insert for your function.

Moreover, as per the documentation:

The CREATE FUNCTION statement creates read-only functions that are free of side effects. Neither DDL or DML statements (INSERT, UPDATE, and DELETE) are allowed in the function body. Also other functions or procedures selected/called from the body of the function must be read only.

[https://help.sap.com/viewer/4fe29514fd584807ac9f2a04f6754767/2.0.02/en-US/20d42e77751910149f0ff6b879b1290f.html][doc link]

Instead your function could/should look like this:

DROP   FUNCTION MYFUNCTION;
CREATE FUNCTION MYFUNCTION (Val1 INT, Val2 INT, Val3 INT)
RETURNS TABLE (col1 INT, col2 INT) LANGUAGE SQLSCRIPT AS
BEGIN
    IF :Val1 > :Val2 THEN
        Val3 := 2  * :Val3;
    ELSE
        Val3 := -1 * :Val3;
    END IF;
    RETURN SELECT :Val1 AS col1, :Val2 * :Val3 AS col2 FROM DUMMY;
END;

SELECT * FROM MYFUNCTION(2,3,2);

BTW, your current code is missing some variable declaration to be valid.

score 0 · Answer 2 · answered Mar 29 '18 at 06:22

Merhaba @J.Doe

Your function returns a TABLE. The "TABLE" is not the name of the table object you're trying to return

In your function code, just use RETURN and SELECT statement together

Here is a sample function I use

CREATE FUNCTION "DummySelect"(article int)
RETURNS  TABLE
(
    Id INT, Type varchar(10)
)
LANGUAGE SQLSCRIPT
SQL SECURITY DEFINER AS
BEGIN

declare myId int;
declare myType varchar(10);

select ARTICLE, "TYPE" 
    into "MYID", "MYTYPE" 
    from Article_Types 
    where "TYPE" = 'AA';

RETURN (select :myId, :myType from dummy);

END;

You see in RETURN command, I'm returning the result set of the SELECT which has exactly the same types declared with TABLE definition

Instead of INSERTing into returning table, just use SELECT to return

I hope it helps

How to Insert Value to FUNCTION'S TABLE

2 Answers2