Validation error while creating calculation view

[former_member937061]

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

[former_member185165]

Hi Ankur,

Try the below. Just copy and paste this code in your scripted calculation view. It should work.

/********* 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 ************/

[lbreddemann]

You do realise that this thread moved on a couple of days ago, right?

[former_member185165]

No Lars, I did not realize that since the question was not marked as answered.

[lbreddemann]

How is that a reason for not reading up on what's going on in a conversation before rushing in with something?

You are welcome to add something, by all means. But try to keep up with the flow of the discussion. Pretty much what you would do in a normal conversation, too.

[former_member185165]

No issues. Totally agreed (y)

[former_member937061]

What does this error essentially mean-

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 320)nSet Schema DDL statement: set schema "JDA_SRC"nType DDL: create type "_SYS_BIC"."picture_report_BB/PIC_REP/proc/tabletype/VAR_OUT" as table ("ESSPLC" DECIMAL(5,1), "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 n t var_out = nSelect ESSPLC,ESHSQYnFROM "JDA_SRC"."CIVS00P"nENDn/********* End Procedure Script ************/n

[mangesh_kothuri]

Ankur,

Can you try adding semicolon  Order by ESSPLC,ESRVND, ESRSTY, ESSCOD ;

>>> There is something missing from your syntax.

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

Thanks

Kothuri

[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_member937061]

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_member937061]

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

[former_member937061]

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_member937061]

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_member937061]

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_member937061]

Hmm...

[former_member937061]

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_member937061]

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_member937061]

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_member937061]

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_member937061]

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

[former_member937061]

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_member937061]

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