Skip to Content
avatar image
Former Member

I am not able to execute a procedure

Hello,

I am trying to execute a procure from few days now, But unsuccessful. I also tried finding on internet, But with no help. My objective of this task is to:-

  • 1.Creating a table( I have created the table successfully named as DB_WEEK_SERIES)
  • 2.Inserting entries from 2010 to 2030 (Could not get how to do it)
  • 3.Utilizing table replacing TT_SERIES.(Could not get how to do it)

Below is my code. Please have a look. I am new to SAP HANA. So please support me.

My table:-

table.schemaName = "BHAVYA";

table.tableType = COLUMNSTORE;

table.columns = [

{name = "REPORT_DATE"; sqlType = date; },

{name = "REPORT_WEEK"; sqlType = INTEGER; length = 20;},

{name = "WEEK_END"; sqlType = date; }

];

table.primaryKey.pkcolumns = ["REPORT_DATE"];

My procedure:-

create procedure bhavya.zz_get_series()

as

LV_START_DATE date := '2017-01-01' ;

LV_END_DATE date := current_date ;

Begin TT_WEEK_END = select report_week, max(report_date) as week_end from :TT_SERIES group by report_week ;

Create Table DB_WEEK_SERIES = (SELECT GENERATED_PERIOD_START AS REPORT_DATE, week(add_days(generated_period_start , 2)) as report_week, current_date as week_end FROM SERIES_GENERATE_DATE ( 'INTERVAL 1 DAY', :LV_START_DATE, ADD_DAYS(coalesce(current_date,:LV_END_DATE), 1)));

insert into BHAVYA.DB_WEEK_SERIES (REPORT_DATE,REPORT_WEEK,WEEK_END ) select S.report_date, w.report_week, w.week_end from :TT_WEEK_END W inner join :TT_SERIES S on w.report_week = s.report_week ;

end ;

call bhavya.zz_get_series

Please tell me what to do. Thanks.

Add comment
10|10000 characters needed characters exceeded

  • Former Member

    Sure. Below is my updated code:-

    create procedure bhavya.zz_get_series

    ( IN TT_INPUT "OPS_SCHEMA"."AM.OPS.CORE.TABLETYPES::GTT_INPUT", OUT TT_DASHBOARD "OPS_SCHEMA"."AM.OPS.CORE.TABLETYPES::GTT_GENERIC_DASHBOARD")

    LANGUAGE SQLSCRIPT

    SQL SECURITY INVOKER

    --DEFAULT SCHEMA <default_schema_name>

    READS SQL DATA

    AS

    LV_START_DATE DATE := ADD_DAYS (LAST_DAY (ADD_MONTHS(CURRENT_DATE, -4)), 1);

    LV_END_DATE DATE := LAST_DAY (CURRENT_DATE);

    LV_START_DATE date := '2010-01-01' ;

    LV_END_DATE date := current_date ;

    lv_report_date date ;

    Begin SELECT IFNULL(NULLIF(VALUE,''),:LV_START_DATE) INTO LV_START_DATE FROM :TT_INPUT where VARIABLE = 'START_DATE';

    SELECT IFNULL(NULLIF(VALUE,''),:LV_END_DATE) INTO LV_END_DATE FROM :TT_INPUT where VARIABLE = 'END_DATE';

    DB_WEEK_SERIES = (SELECT GENERATED_PERIOD_START AS REPORT_DATE, week(add_days(generated_period_start , 2)) as report_week, current_date as week_end --added ABHOOT FROM SERIES_GENERATE_DATE ( 'INTERVAL 1 DAY', :LV_START_DATE, ADD_DAYS(coalesce(current_date,:LV_END_DATE), 1)));

    TT_WEEK_END = select report_week, max(report_date) as week_end from :TT_SERIES group by report_week ;

    (select S.report_date, w.report_week, w.week_end from :TT_WEEK_END W inner join :TT_SERIES S on w.report_week = s.report_week) ;

    end ;

    call bhavya.zz_get_series

    My error which is showing currently:-

    Could not execute 'create procedure bhavya.zz_get_series( IN TT_INPUT ...' SAP DBTech JDBC: [1285]: at most one declaration is permitted in the declaration section: LV_START_DATE: line 14 col 1 (at pos 440) Could not execute 'call bhavya.zz_get_series' SAP DBTech JDBC: [328]: invalid name of function or procedure: ZZ_GET_SERIES: line 1 col 13 (at pos 12)

  • What is the error you are getting while executing this procedure?

    thanks

    Aniruddha

  • Get RSS Feed

2 Answers

  • Best Answer
    Jul 12, 2017 at 10:31 AM

    In the updated code you posted in your comment the error you receive is caused by the duplicate declaration of variables LV_START_DATE, LV_END_DATE in the declaration section. Please correct this and try again to create the procedure. If you get then further errors please post it here again.

    Regards,
    Florian

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member Former Member

      I got it executed by removing "READS SQL DATA" from my procedure. Thanks so much for your help. I just want to now if my procedure is right or wrong? I mean to say,

      My objective was to:-

      1. Creating a table(Already created)

      2. Inserting entries from 2010 to 2030. (Can you please tell me if the procedure which I have created for inserting this is right or wrong?)

      3. Utilizing table replacing TT_SERIES(I don't really understand what does this mean and how to solve it?)

      Again I am very new to SAP HANA. Please co-operate with me. It will take some time for me to understand everything. Thanks.

      My code which got executed:-

      create procedure bhavya.zz_get_series()

      LANGUAGE SQLSCRIPT

      SQL SECURITY INVOKER

      --DEFAULT SCHEMA <default_schema_name>

      AS

      LV_START_DATE date := '2010-01-01' ;

      LV_END_DATE date := '2031-01-01' ;

      LV_WEEK_END NVARCHAR(10) := 'FRIDAY';

      lv_report_date Date ;

      Begin

      TT_SERIES = (SELECT GENERATED_PERIOD_START AS REPORT_DATE, week(add_days(generated_period_start , 2)) as report_week, '2031-01-01' as week_end --added ABHOOT FROM SERIES_GENERATE_DATE ( 'INTERVAL 1 DAY', :LV_START_DATE, ADD_DAYS(coalesce(current_date,:LV_END_DATE), 1)));

      TT_WEEK_END = select report_week, max(report_date) as week_end from :TT_SERIES group by report_week ; insert into "BHAVYA"."AFS_BASE.KPI.TABLES::DB_WEEK_SERIES1" (REPORT_DATE,REPORT_WEEK,WEEK_END) select S.report_date, w.report_week, w.week_end from :TT_SERIES S left join :TT_WEEK_END W on w.report_week = s.report_week;

      end;

      call bhavya.zz_get_series

  • avatar image
    Former Member
    Jul 13, 2017 at 01:00 PM
    -1

    Hi Bhavya,

    Try to change the call procedure statement as below and check

    CALL BHAVYA.ZZ_GET_SERIES;
    
    Add comment
    10|10000 characters needed characters exceeded