Validation error while creating calculation view

[Former Member]

I used the following syntax while creating a scripted calculation view-

/********* Begin Procedure Script ************/

BEGIN

  var_out =

Select ESSPLC , ESRVND , ESRSTY, ESSCOD,

SUM(ESHSQY),

FROM "JDA_SRC"."CIVS00P"

GROUP BY ESSPLC, ESRVND, ESRSTY, ESSCOD

Order by ESSPLC,ESRVND, ESRSTY, ESSCOD

END

/********* End Procedure Script ************/

I am getting the following error while attempting to validate my view(see attachment). Please help me

[lbreddemann]

Really? You make _us_ all take out our magnifier glasses to actually read the error message?

My weary eyes recognised something like "... syntax error near FROM... ".

Hey that's a good hint, ain't it?

Checking how your statement looks like around the FROM word what do we find?

...
Select ESSPLC , ESRVND , ESRSTY, ESSCOD, 
SUM(ESHSQY),
FROM "JDA_SRC"."CIVS00P"...

GOTCHA!

What you mean, you don't see it?

It's right there (I made the error bold):

Select ESSPLC , ESRVND , ESRSTY, ESSCOD, 
SUM(ESHSQY),
FROM "JDA_SRC"."CIVS00P"

Still not? Alright then:

Select ESSPLC , ESRVND , ESRSTY, ESSCOD, 
SUM(ESHSQY)   ,  <-- this comma doesn't belong here and is only there because someone tried copy&paste coding and that didn't work out so well. 

FROM "JDA_SRC"."CIVS00P"

🙂

Pro-tip: when in doubt, it can be very helpful to run a SQL statement from a procedure separately in a SQL editor window. Just to see what the error might be.

[Former Member]

Lars,

I am still getting this error-

  Internal deployment of object failed;Repository: Encountered an error in repository runtime extension;Internal Error:Deploy Calculation View: SQL: sql syntax error: incorrect syntax near "END": line 9 col 1 (at pos 422)nSet Schema DDL statement: set schema "SYSTEM"nType DDL: create type "_SYS_BIC"."picture_report_BB/PIC_REP/proc/tabletype/VAR_OUT" as table ("ESSPLC" DECIMAL(5,1), "ESRVND" VARCHAR(6), "ESRSTY" VARCHAR(15), "ESSCOD" VARCHAR(3), "ESHSQY" DECIMAL(8,1))nProcedure DDL: create procedure "_SYS_BIC"."picture_report_BB/PIC_REP/proc" ( OUT var_out "_SYS_BIC"."picture_report_BB/PIC_REP/proc/tabletype/VAR_OUT" ) language sqlscript sql security definer reads sql data as  n /********* Begin Procedure Script ************/ n BEGIN n t var_out = nSelect ESSPLC, ESRVND, ESRSTY, ESSCOD,SUM(ESHSQY)nFROM "JDA_SRC"."CIVS00P"nGROUP BY ESSPLC,ESRVND,ESRSTY,ESSCODnOrder by ESSPLC,ESRVND, ESRSTY,ESSCODnEND n/********* End Procedure Script ************/n

This time attaching u the screenshot of the View

[lbreddemann]

Alright mate, tell you what:  this is funny.

So I am going to tell you that this time

• you didn't leave something somewhere where it doesn't belong, but the opposite
• it's not the comma and not the colon but their little offspring

BTW: love your screen-snapshotting-abilities! Next round on I expect a little screen-capture-movie plus voice over

[Former Member]

This runs perfectly in SQL Editor but throws an error while validation

[Former Member]

Select ESSPLC,ESSCOD,SUM(ESHSQY)

FROM "_SYS_BIC"."picture_report_BB/CIVSOOP_ANALYTIC"

GROUP BY ESSPLC,ESSCOD

Order by ESSPLC,ESSCOD

Following is the error I get -

Internal deployment of object failed;Repository: Encountered an error in repository runtime extension;Internal Error:Deploy Calculation View: SQL: sql syntax error: incorrect syntax near "END": line 9 col 1 (at pos 404)nSet Schema DDL statement: set schema "SYSTEM"nType DDL: create type "_SYS_BIC"."picture_report_BB/PIC_REP/proc/tabletype/VAR_OUT" as table ("ESSPLC" DECIMAL(5,1), "ESRVND" VARCHAR(6), "ESRSTY" VARCHAR(15), "ESSCOD" VARCHAR(3), "ESHSQY" DECIMAL(8,1))nProcedure DDL: create procedure "_SYS_BIC"."picture_report_BB/PIC_REP/proc" ( OUT var_out "_SYS_BIC"."picture_report_BB/PIC_REP/proc/tabletype/VAR_OUT" ) language sqlscript sql security definer reads sql data as  n /********* Begin Procedure Script ************/ n BEGIN n t var_out = nSelect ESSPLC,ESSCOD,SUM(ESHSQY)nFROM "_SYS_BIC"."picture_report_BB/CIVSOOP_ANALYTIC"nGROUP BY ESSPLC,ESSCODnOrder by ESSPLC,ESSCODnENDn/********* End Procedure Script ************/n

[lbreddemann]

ok, in your SQL editor but another statement right under your one and try to run the whole thing.

No line-selecting with the mouse, just press RUN.

Any statement will do.

Something like this

SELECT current_user FROM dummy;

would be enough.

How does that work?

[Former Member]

Still getting the same error again-

I ran

/********* Begin Procedure Script ************/

BEGIN

  var_out =

Select ESSPLC

FROM "_SYS_BIC"."picture_report_BB/CIVSOOP_ANALYTIC"

END

/********* End Procedure Script ************/

[Former Member]

U suppose it may be some authorization related issue-

I am again getting

Internal deployment of object failed;Repository: Encountered an error in repository runtime extension;Internal Error:Deploy Calculation View: SQL: sql syntax error: incorrect syntax near "END": line 8 col 1 (at pos 312)nSet Schema DDL statement: set schema "SYSTEM"nType DDL: create type "_SYS_BIC"."picture_report_BB/PIC_REP/proc/tabletype/VAR_OUT" as table ("ESSPLC" DECIMAL(5,1))nProcedure DDL: create procedure "_SYS_BIC"."picture_report_BB/PIC_REP/proc" ( OUT var_out "_SYS_BIC"."picture_report_BB/PIC_REP/proc/tabletype/VAR_OUT" ) language sqlscript sql security definer reads sql data as  n /********* Begin Procedure Script ************/ n BEGIN n t var_out = nSelect ESSPLCnFROM "JDA_SRC"."CIVS00P"nnENDn/********* End Procedure Script ************/n

[lbreddemann]

It's not the authorization!

[Former Member]

Hmm...

[Former Member]

Hi lars good morning any tips..I am still facing the same error here

[lbreddemann]

Have you considered the hint I provided?

you didn't leave something somewhere where it doesn't belong, but the opposite
it's not the comma and not the colon but their little offspring

There is something missing from your syntax.

It's the very last bit of every statement...

[Former Member]

I have tried the . THE : THE ; I get the same errors from the offsprings as well as the parents haha

[lbreddemann]

So you're saying that this

/********* Begin Procedure Script ************/

BEGIN

  var_out = SELECT ESSPLC

            FROM "_SYS_BIC"."picture_report_BB/CIVSOOP_ANALYTIC";

END

/********* End Procedure Script ************/

doesn't work?

[Former Member]

Nope...but now I am getting a different error

[lbreddemann]

Okay... maybe it would be a good idea to review the examples for scripted calc views.

Looks like the output structure has not been correctly defined.

It must be exactly like what you assign to var_out.

Same column name, same data type.

[Former Member]

I have a decimal field with length 5 and scale 0 , while the length us fine it does not permit me to define scale as 0

[lbreddemann]

Does it literally not allow to enter the value into the UI or do you get an error upon activation?

[Former Member]

In the UI it does not permit me to enter a value of 0 on scale

[Former Member]

hey any tips man??

I have literally run out of ideas.. new to calculation views me haha

[lbreddemann]

I just tried to set the scale of a decimal typed output parameter for a scripted calculation view to zero.

And it worked.

I just had to click into the text cell for scale, enter 0 and press [ENTER].

To be honest, I can't recall this ever not to work (even though SAP HANA studio does admittedly features some strange keyboard shortcuts and tab-cycles...).

[Former Member]

You might be right. One of my colleagues was able to create the views no problems at all.

I dont know man may be a software glitch.

Sincerely

Ankur