SAP HANA Eclipse Syntax error. ";" is incorrect or misplaced. SERIES_GENERATE_DATE

[Former Member]

My code:-

PROCEDURE"AFS_BASE"."AFS_BASE.KPI.DASHBOARD::PR_REV_CTRL_BOX_ORDER_BY_STAGE_V1"

(

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 AFS_BASE

AS

LV_TAB NVARCHAR(50) ;

lv_measure nvarchar(50) ;

lv_report_date date ;

LV_UID NVARCHAR(255);

LV_SUB_HEADING NVARCHAR(255);

LV_START_DATEDATE := '2010-01-01';

LV_END_DATEDATE := '2031-01-01';

LV_WEEKEND NVARCHAR(10) := 'FRIDAY';

/**********************************

Created by Junaid on 13th May 2017

**********************************/

BEGIN

DECLARE REPORT_WEEK_INIT integer := ABS(WEEKDAY(LV_START_DATE) - 4);

SELECT VALUE INTO LV_UID FROM :TT_INPUT WHERE VARIABLE = 'UID';

SELECT VALUE INTO LV_TAB FROM :TT_INPUT WHERE VARIABLE = 'TAB';

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_DATEFROM :TT_INPUT where VARIABLE = 'END_DATE';

BEGIN

DECLARE EXIT HANDLER FOR SQL_ERROR_CODE 1299

LV_SUB_HEADING := '';

SELECT VALUE INTO LV_SUB_HEADINGFROM :TT_INPUT WHERE VARIABLE = 'SUB_HEADING';

END;

TT_HDR = SELECT DISTINCT SERIAL_NUMBER

FROM"AFS_BASE"."AFS_BASE.TRENDS.TABLES::OPV_KPI_MEASURES"

WHERE MEASURE_DATE BETWEEN :LV_START_DATE AND :LV_END_DATE;

TT_ZOOM = SELECT 'ATTRIBUTE' AS ZOOM_TYPE,

Z.ATTRIBUTE AS ZOOM_ATTRIBUTE,

Z.ATTRIBUTE_VALUE AS ZOOM_ATTRIBUTE_VALUE

FROM"BASE_SCHEMA"."AM.BASE.TABLES::DB_TAB_ZOOM_VALUES"

WHERE Z.TAB_NAME = :LV_TAB

AND Z.SCENARIO_NUMBER = SESSION_CONTEXT('$vssod_scenario')

AND Z.SESSION_ID = SESSION_CONTEXT('$vssod_sid')

AND Z.USER_NAME = SESSION_USER

AND Z.ATTRIBUTE IN (SELECT ATTRIBUTE FROM"BASE_SCHEMA"."AM.BASE.TABLES::DB_CONFIG_ATTRIBUE"

WHERE CLIENT = SESSION_CONTEXT('CLIENT') AND DISPLAY = 'Y')

UNION ALL

SELECT 'TAG' AS ZOOM_TYPE,

Z.ATTRIBUTE AS ZOOM_ATTRIBUTE,

Z.ATTRIBUTE_VALUE AS ZOOM_ATTRIBUTE_VALUE

FROM "BASE_SCHEMA"."AM.BASE.TABLES::DB_TAB_ZOOM_VALUES"

WHERE TAB_NAME = :LV_TAB

AND Z.SCENARIO_NUMBER= SESSION_CONTEXT('$vssod_scenario')

ANDZ.SESSION_ID = SESSION_CONTEXT('$vssod_sid')

AND Z.USER_NAME = SESSION_USER

AND Z.ATTRIBUTE IN (SELECT TAG_NAME FROM"BASE_SCHEMA"."AM.BASE.TABLES::DB_CONFIG_TAGS"

WHERE CLIENT = SESSION_CONTEXT('CLIENT') AND DISPLAY = 'Y');

CALL"AFS_BASE"."AFS_BASE.TRENDS.CORE/PR_KPI_FILTER_WITH_ZOOM"(:TT_INPUT, :TT_ZOOM, :TT_HDR, TT_FSN);

TT_SERIES =

(SELECT

GENERATED_PERIOD_START AS REPORT_DATE,

CASE WHEN WEEKDAY(GENERATED_PERIOD_START) > 4 THEN

ADD_DAYS(GENERATED_PERIOD_START, WEEKDAY(GENERATED_PERIOD_START) +

CASE WHEN WEEKDAY(GENERATED_PERIOD_START)=5 THEN 1 ELSE -1 END)

ELSE

ADD_DAYS(GENERATED_PERIOD_START, 4-WEEKDAY(GENERATED_PERIOD_START))

END AS WEEK_END

FROM SERIES_GENERATE_DATE ( 'INTERVAL 1 DAY', :LV_START_DATE, COALESCE (:LV_END_DATE, CURRENT_DATE))) ;

INSERT INTO bhavya.DB_WEEK_SERIES2 ( REPORT_DATE, REPORT_WEEK, WEEK_END )

SELECT REPORT_DATE, WEEK(WEEK_END), WEEK_END

FROM :TT_SERIES;

select SUBSTR_AFTER(:lv_uid, ':') intolv_measurefromdummy ;

select SUBSTR_BEFORE (:lv_uid, ':') intolv_report_datefromdummy ;

IF :LV_UID = 'ALL' THEN

TT_INVOICED = SELECT 0 as serial_number , week_end as REPORT_DATE, SUM(MEASURE_AMOUNT) AS INV_AMT

FROM"AFS_BASE"."AFS_BASE.TRENDS.TABLES::OPV_KPI_MEASURES"K

INNER JOIN :TT_SERIES TS

ON TS.REPORT_DATE = K.MEASURE_DATE

where MEASURE = 'Invoiced'

and k.serial_number in (select distinct serial_number from :TT_FSN )

GROUP BYweek_end;

TT_INVOICE = SELECT REPORT_DATE, 'Invoiced' AS MEASURE, SUM(INV_AMT) AS AMT

FROM :TT_INVOICEDI

GROUP BY report_date ;

TT_REMAINING = SELECT 0 as SERIAL_NUMBER, ts.week_end as REPORT_DATE, MEASURE, SUM(MEASURE_AMOUNT) AS AMT

FROM"AFS_BASE"."AFS_BASE.TRENDS.TABLES::OPV_KPI_MEASURES"K

INNER JOIN :TT_week_end TS

ON TS.WEEK_END BETWEEN K.VALID_FROM_DATE AND K.VALID_TO_DATE

where k.MEASURE IN ('In Pick', 'In Production', 'In Transit', 'Late', 'Not Covered', 'On Hand', 'Open Direct Ship')

and k.serial_number in (select distinct serial_number from :TT_FSN )

GROUP BY TS.week_end, MEASURE ;

TT_DATA = SELECT REPORT_DATE, MEASURE, SUM(AMT) AS TOT_AMT, 1 AS SORT FROM :TT_INVOICE

GROUP BY REPORT_DATE, MEASURE

UNION ALL

SELECT REPORT_DATE, MEASURE, SUM(AMT) AS TOT_AMT, 2 AS SORT FROM :TT_REMAINING

GROUP BY REPORT_DATE, MEASURE;

ELSEIF :lv_measure = 'Invoiced' THEN

TT_DRILLDOWN = SELECT K.serial_number, week_end as REPORT_DATE, k.measure, SUM(MEASURE_AMOUNT) AS AMT

FROM"AFS_BASE"."AFS_BASE.TRENDS.TABLES::OPV_KPI_MEASURES"K

INNERJOIN :TT_SERIES TS

ON TS.REPORT_DATE = K.MEASURE_DATE

and ts.week_end = :lv_report_date

where MEASURE = 'Invoiced'

and k.serial_numberin (select distinct serial_number from :TT_FSN )

GROUPBYweek_end, k.measure, K.serial_number ;

ELSEIF :lv_measure <> 'Invoiced' THEN

TT_DRILLDOWN = SELECTk.SERIAL_NUMBER, :lv_report_dateasREPORT_DATE, MEASURE, SUM(MEASURE_AMOUNT) ASAMT

FROM"AFS_BASE"."AFS_BASE.TRENDS.TABLES::OPV_KPI_MEASURES"K

wherek.MEASURE = :lv_measure

and :lv_report_dateBETWEENK.VALID_FROM_DATEANDK.VALID_TO_DATE

andk.serial_numberin (selectdistinctserial_numberfrom :TT_FSN )

GROUPBYMEASURE, K.serial_number ;

ENDIF ;

/* TT_WEEKLY = SELECT REPORT_DATE, COALESCE(ADD_DAYS(LAG(REPORT_DATE,1) OVER (ORDER BY REPORT_DATE), 1), :LV_START_DATE) AS PREV_REPORT_DATE

FROM ( SELECT REPORT_DATE FROM :TT_SERIES WHERE DAYNAME(REPORT_DATE) = 'FRIDAY'); */

IF :LV_UID = 'ALL' THEN

TT_DASHBOARD = SELECTREPORT_DATEAS"NAME",

NULLAS"HEADING",

'GRAPH' AS"SUB_HEADING",

MEASUREAS"SUB_CAT1",

NULLAS"SUB_CAT2",

SORTAS"SORT1",

NULLAS"SORT2",

ROUND(TOT_AMT/1000000, 2) AS"VALUE",

NULLAS"SERIAL_NUMBER",

NULLAS"UNIT",

TO_CHAR (REPORT_DATE) || ':' || measureAS"UID",

NULLASFREQUENCY,

'DRILL_DOWN' AS"HYPER_LINK",

'$POST=M$;$Y=Value in M$' AS"AI",

NULLAS"TYPE",

NULLASSIZE

FROM :TT_DATA

ORDERBYREPORT_DATE;

ELSE

TT_DASHBOARD = SELECTNULLAS"NAME",

NULLAS"HEADING",

NULLAS"SUB_HEADING",

NULLAS"SUB_CAT1",

NULLAS"SUB_CAT2",

NULLAS"SORT1",

NULLAS"SORT2",

NULLAS"VALUE",

SERIAL_NUMBERAS"SERIAL_NUMBER",

NULLAS"UNIT",

NULLAS"UID",

NULLASFREQUENCY,

NULLAS"HYPER_LINK",

NULLAS"AI",

NULLAS"TYPE",

NULLASSIZE

FROM :TT_DRILLDOWNORDERBYAMTDESC

LIMIT 10000;

ENDIF;

END;

[Pradeep_Emmidi]

Hi, May i know are you able to resolve this issue? I'm having same issue.. Appreciate your quick reply.

Regards,

Pradeep