Stored procedure in HANA

[Former Member]

hello All

I converted the following stored procedure in HANA in the SBOTRANSACTION NOTIFICATION, but I am getting errors.

please can someone assist with this :

CREATE PROCEDURE "TEST".SBO_SP_TransactionNotification (in object_type nvarchar(30),

       -- SBO Object Type

in transaction_type nchar(1),

       -- [A]dd, [U]pdate, [D]elete, [C]ancel, C[L]ose

in num_of_cols_in_key int,

in list_of_key_cols_tab_del nvarchar(255),

in list_of_cols_val_tab_del nvarchar(255) ) LANGUAGE SQLSCRIPT AS -- Return values

temp_var_0 int;  -- user variable

cnt int;

error int;                    -- Result (0 for no error)

error_message nvarchar (200); -- Error string to be displayed

TruckNo nvarchar(30);

NewLoadNoYN nvarchar(1);

DocDate timestamp;

LoadID integer ;

begin error := 0;

error_message := N'Ok';

----

IF (:transaction_type = 'A' AND :object_type = '15') THEN

  --Insert Load Sheet starts here. Trigger on Delivery Note ------

BEGIN

  --assigns values

  SELECT "U_TRUCK", "U_UseNewLoadNo", "DocDate"

    INTO TruckNo, NewLoadNoYN, DocDate

    FROM ODLN

   WHERE "DocEntry" = :list_of_cols_val_tab_del;

  --gets the Load ID for the current Truck and current date

  if (exists(Select U_LoadID

              from "@LOADSHEET" 

             where U_LoadingDate = :DocDate

             and U_Truck = :TruckNo)

    ) then

  begin

     SELECT TOP 1 "U_LoadID" INTO LoadID

           FROM "@LOADSHEET"

         WHERE "U_LoadingDate" = :DocDate

            AND "U_Truck" = :TruckNo

        ORDER BY "U_LoadID" DESC;

  if (:NewLoadNoYN = 'N') then

  begin

  INSERT INTO "@LOADSHEET"

    ("Code", "Name", "U_LoadID", "U_LoadingDate", "U_Truck", "U_DeliveryNo")

  VALUES

    (:list_of_cols_val_tab_del

    ,LEFT('Truck No. ' + :TruckNo + ' on Delivery ' + :list_of_cols_val_tab_del,30)

    ,:LoadID

    ,:DocDate

    ,:TruckNo

    ,:list_of_cols_val_tab_del);

  end;

  if (:NewLoadNoYN = 'Y') then

  begin

  --get the new load number

  SELECT TOP 1 "U_LoadID" INTO LoadID

  FROM "@LOADSHEET"

  WHERE "U_LoadingDate" = :DocDate

  AND "U_Truck" = :TruckNo

  ORDER BY "U_LoadID" DESC;

  INSERT INTO "@LOADSHEET"

    ("Code", "Name", "U_LoadID", "U_LoadingDate", "U_Truck", "U_DeliveryNo")

  VALUES

    (:list_of_cols_val_tab_del

    ,:list_of_cols_val_tab_del

    ,(:LoadID + 1)

    ,:DocDate

    ,:TruckNo

    ,:list_of_cols_val_tab_del);

  end;

  end if;

  else

  begin

  -- if the truck number does not exist at all

  --get the new load number

  SELECT TOP 1 "U_LoadID"  INTO LoadID

  FROM "@LOADSHEET"

  WHERE "U_LoadingDate" = :DocDate

  AND "U_Truck" = :TruckNo

  ORDER BY "U_LoadID" DESC;

  --if (LoadID = 0 or LoadID is null) then

  -- :LoadID    = 1;

  --else

  -- :LoadID = :LoadID + 1;

  INSERT INTO "@LOADSHEET"

    ("Code", "Name", "U_LoadID", "U_LoadingDate", "U_Truck", "U_DeliveryNo")

  VALUES

    (list_of_cols_val_tab_del

    ,LEFT('Truck No. ' + :TruckNo + ' on Delivery ' + list_of_cols_val_tab_del,30)

    ,:LoadID

    ,:DocDate

    ,:TruckNo

    ,:list_of_cols_val_tab_del);

  end;

end if;

---------------------------------------------------------------------- -- Select the return values

select

       :error,

       :error_message

FROM dummy;

end;

[Former Member]

Hello.

can someone please assist me with this ...

thanks

Janice

[former_member184146]

can you share the error details?

--Manish

[Former Member]

hi Manish

I am getting a syntax error.

Could not execute 'CREATE PROCEDURE "TEST".SBO_SP_TransactionNotification (in object_type nvarchar(30), -- ...'

SAP DBTech JDBC: [257]: sql syntax error: line 455 col 1 (at pos 12300)


this line 455 is in the section on the stored proc I didn't even change :

---------------------------------------------------------------------- -- Select the return values

select

       :error,

       :error_message

FROM dummy;
end;

[former_member184146]

something is wrong with the code at line no mentioned above check it properly.

--Manish

[Former Member]

hi Manish

I have been checking it all day and still the error persists.

Can I send you my SQL code and see if you can convert it. Maybe my conversion tool is a problem?

thanks

Janice

[former_member184146]

can we look via TV?

[Former Member]

hi Manish

email me on jgviljoen77@gmail.com and I will send you my details

[Former Member]

Hi Janice,

Something like this might work:

CREATE PROCEDURE "TEST".SBO_SP_TransactionNotification (
    IN object_type NVARCHAR(30),
    -- SBO Object Type
    IN transaction_type NCHAR(1),
    -- [A]dd, [U]pdate, [D]elete, [C]ancel, C[L]ose
    IN num_of_cols_in_key INT
    ,IN list_of_key_cols_tab_del NVARCHAR(255)
    ,IN list_of_cols_val_tab_del NVARCHAR(255)
    )
    LANGUAGE SQLSCRIPT
AS -- Return values
temp_var_0 INT;-- user variable
cnt INT;

error INT;-- Result (0 for no error)
error_message NVARCHAR(200);-- Error string to be displayed

TruckNo NVARCHAR(30);
NewLoadNoYN NVARCHAR(1);
DocDate TIMESTAMP;
LoadID INTEGER;

BEGIN
    error := 0;

    error_message := N'Ok';

    ----
    IF (
            :transaction_type = 'A'
            AND :object_type = '15'
            ) THEN
        --Insert Load Sheet starts here. Trigger on Delivery Note ------

        --assigns values
        SELECT "U_TRUCK"
            ,"U_UseNewLoadNo"
            ,"DocDate"
        INTO TruckNo
            ,NewLoadNoYN
            ,DocDate
        FROM ODLN
        WHERE "DocEntry" = :list_of_cols_val_tab_del;

        --gets the Load ID for the current Truck and current date
        IF (
                EXISTS (
                    SELECT U_LoadID
                    FROM "@LOADSHEET"
                    WHERE U_LoadingDate = :DocDate
                        AND U_Truck = :TruckNo
                    )
                ) THEN

            SELECT TOP 1 "U_LoadID"
            INTO LoadID
            FROM "@LOADSHEET"
            WHERE "U_LoadingDate" = :DocDate
                AND "U_Truck" = :TruckNo
            ORDER BY "U_LoadID" DESC;

            IF :NewLoadNoYN = 'N' THEN

                INSERT INTO "@LOADSHEET" (
                    "Code"
                    ,"Name"
                    ,"U_LoadID"
                    ,"U_LoadingDate"
                    ,"U_Truck"
                    ,"U_DeliveryNo"
                    )
                VALUES (
                    :list_of_cols_val_tab_del
                    ,LEFT('Truck No. ' + :TruckNo + ' on Delivery ' + :list_of_cols_val_tab_del, 30)
                    ,:LoadID
                    ,:DocDate
                    ,:TruckNo
                    ,:list_of_cols_val_tab_del
                    );
            END IF;

            IF (:NewLoadNoYN = 'Y') THEN

                --get the new load number
                SELECT TOP 1 "U_LoadID"
                INTO LoadID
                FROM "@LOADSHEET"
                WHERE "U_LoadingDate" = :DocDate
                    AND "U_Truck" = :TruckNo
                ORDER BY "U_LoadID" DESC;

                INSERT INTO "@LOADSHEET" (
                    "Code"
                    ,"Name"
                    ,"U_LoadID"
                    ,"U_LoadingDate"
                    ,"U_Truck"
                    ,"U_DeliveryNo"
                    )
                VALUES (
                    :list_of_cols_val_tab_del
                    ,:list_of_cols_val_tab_del
                    ,(:LoadID + 1)
                    ,:DocDate
                    ,:TruckNo
                    ,:list_of_cols_val_tab_del
                    );
            END IF;
        ELSE
            -- if the truck number does not exist at all
            --get the new load number
            SELECT TOP 1 "U_LoadID"
            INTO LoadID
            FROM "@LOADSHEET"
            WHERE "U_LoadingDate" = :DocDate
                AND "U_Truck" = :TruckNo
            ORDER BY "U_LoadID" DESC;

            --if (LoadID = 0 or LoadID is null) then
            -- :LoadID    = 1;
            --else
            -- :LoadID = :LoadID + 1;
            INSERT INTO "@LOADSHEET" (
                "Code"
                ,"Name"
                ,"U_LoadID"
                ,"U_LoadingDate"
                ,"U_Truck"
                ,"U_DeliveryNo"
                )
            VALUES (
                list_of_cols_val_tab_del
                ,LEFT('Truck No. ' + :TruckNo + ' on Delivery ' + list_of_cols_val_tab_del, 30)
                ,:LoadID
                ,:DocDate
                ,:TruckNo
                ,:list_of_cols_val_tab_del
                );
        END IF;
    END IF ;
---------------------------------------------------------------------- -- Select the return values
SELECT
       :error,
       :error_message
FROM dummy;

END;

Kind regards,

Radek

[Former Member]

hi Radek

when I try your code, I get an error that a subquery has returned more than one row

[Former Member]

Hi Janice,

Unfortunately I don't have enough time to delve into this... And I don't even know what you're trying to achieve. Basically it is still your code since I only removed 'BEGIN' keywords after 'THEN' and added couple of 'IF's after 'END's to make it compliant with SQL script syntax.

I'd say that you need to check where subqueries are located in this code and check if they are not too generic. You can simply use TOP 1.

Kind regards,

Radek