Skip to Content
avatar image
Former Member

Error in procedure

Hi,

I am going through procedure from SAP help document.

I still not understand from where can I refer the correct syntax for creating a procedure.

Below is the code I directly copied form SAP help procedure but throwing below error.

Kindly note that Control_tab table already exists in schema Sumeet.

If i give like "Sumeet"."control_tab" it throws error "ID" column doesnt exists , which exists in the table control_tab.

CREATE PROCEDURE orchestrationProc( )

LANGUAGE SQLSCRIPT AS

BEGIN

   DECLARE v_id BIGINT;

   DECLARE v_name VARCHAR(30);

   DECLARE  v_pmnt BIGINT;

   DECLARE v_msg VARCHAR(200);

   DECLARE CURSOR c_cursor1 (p_payment BIGINT) FOR

     SELECT id, name, payment FROM control_tab

       WHERE payment > :p_payment ORDER BY id ASC;

   CALL init_proc();

   OPEN c_cursor1(250000);

   FETCH c_cursor1 INTO v_id, v_name, v_pmnt; v_msg := :v_name || ' (id ' || :v_id || ') earns ' || :v_pmnt || ' $.';

   CALL ins_msg_proc(:v_msg);

   CLOSE c_cursor1;

END;

Error:

Could not execute 'CREATE PROCEDURE orchestrationProc( ) LANGUAGE SQLSCRIPT AS BEGIN DECLARE v_id BIGINT; DECLARE ...' in 6 ms 740 µs .

SAP DBTech JDBC: [259] (at 267): invalid table name:  Could not find table/view CONTROL_TAB in schema Sumeet: line 9 col 36 (at pos 267)

BR

Sumeet

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

2 Answers

  • Dec 26, 2014 at 05:46 AM

        Hi Sumeet ,

        This is because table CONTROL_TAB is not in your schema or table does not exists.

        If you don't add the schema name first, then by default hana thinks that it is your schema.

        Below query will help, where it is located:

        select * from "PUBLIC"."TABLES" WHERE TABLE_NAME = 'CONTROL_TAB' ;

        Table Name should be like these:

        "BASE_SCHEMA"."DB_ACTIVITY_MAPPING"

        Hope these helps😳.

        -

        Pratik

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Dec 29, 2014 at 11:50 AM

    Hi Sumeet,

    As a best practice, use schema name before table name. Also, note that any table, schema, column names become case sensitive the moment you put them in double quotes. So if you have a column name ID (both letters in capital) then use ID or "ID" in the code. if you use "id" then it will throw the column not found error. Make sure the cases is proper.

    Regards,

    Piyush

    Add comment
    10|10000 characters needed characters exceeded