I am not able to execute a procedure

[Former Member]

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.

[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)

[akshinde]

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

thanks

Aniruddha

[pfefferf · Accepted solution]

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

[Former Member · Accepted solution]

Hi Florian,

Thanks for your response. I have fixed that but now I am getting an another error which says:-

Could not execute 'create procedure bhavya.zz_get_series() LANGUAGE SQLSCRIPT SQL SECURITY INVOKER --DEFAULT SCHEMA ...' SAP DBTech JDBC: [259]: invalid table name: Could not find table/view DB_WEEK_SERIES1 in schema BHAVYA: line 35 col 13 (at pos 874) 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)

My code:-

create procedure bhavya.zz_get_series()

LANGUAGE SQLSCRIPT

SQL SECURITY INVOKER

--DEFAULT SCHEMA <default_schema_name>

READS SQL DATA

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 DB_WEEK_SERIES1 = (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 :DB_WEEK_SERIES1 group by report_week ; insert into DB_WEEK_SERIES1(REPORT_DATE,REPORT_WEEK,WEEK_END)

select S.report_date, w.report_week, w.week_end from :TT_WEEK_END W right join :DB_WEEK_SERIES1 S on w.report_week = s.report_week;

end ;

call bhavya.zz_get_series

[pfefferf · Accepted solution]

Stupid question. Did you check what the message says, that the table does not exist in the mentioned schema?

PS: To not mix up things I would recommend to rename the internal table DB_WEEK_SERIES1 to a different name than the (column?) table. So the code would be more readable.

[Former Member · Accepted solution]

I have replaced DB_WEEK_SERIES1 with TT_SERIES and getting the error as:-

Could not execute 'create procedure bhavya.zz_get_series() LANGUAGE SQLSCRIPT SQL SECURITY INVOKER --DEFAULT SCHEMA ...' SAP DBTech JDBC: [7]: feature not supported: INSERT/UPDATE/DELETE is/are not supported in read-only procedure: line 35 col 1 (at pos 851) 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 needs to be done for this? Please help.

[pfefferf · Accepted solution]

Sorry to say that, but you have to read the error message. It says everything which is necessary.

You are trying to insert data in a table, but you have defined your procedure as read-only procedure. You have to remove the "READS SQL DATA" from your procedure code.

[Former Member · Accepted solution]

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

[thirus11]

Hi Bhavya,

Try to change the call procedure statement as below and check

CALL BHAVYA.ZZ_GET_SERIES;

[Former Member]

Thirumoorthy, My procedure is executing now. It's just that my output is not coming as expected. I will post my output here. Please have a look. Thanks.

[thirus11]

Bhavya

Thanks for the reply.

Yeah please post the output, Wud be helpful to look further!!

Regards,

Thiru