Question Re: Validation error while creating calculation view in Technology Q&A

Validation error while creating calculation view

Former Member — Thu, 27 Aug 2015 19:36:55 GMT

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

Re: Validation error while creating calculation view

lbreddemann — Thu, 27 Aug 2015 19:56:45 GMT

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.

Re: Validation error while creating calculation view

Former Member — Thu, 27 Aug 2015 20:25:56 GMT

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

Re: Validation error while creating calculation view

lbreddemann — Thu, 27 Aug 2015 20:41:52 GMT

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

Re: Validation error while creating calculation view

Former Member — Thu, 27 Aug 2015 20:53:03 GMT

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

Re: Validation error while creating calculation view

Former Member — Thu, 27 Aug 2015 20:57:10 GMT

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

Re: Validation error while creating calculation view

lbreddemann — Thu, 27 Aug 2015 21:00:53 GMT

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?

Re: Validation error while creating calculation view

Former Member — Thu, 27 Aug 2015 21:17:39 GMT

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

Re: Validation error while creating calculation view

Former Member — Thu, 27 Aug 2015 21:29:31 GMT

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

Re: Validation error while creating calculation view

lbreddemann — Thu, 27 Aug 2015 21:37:07 GMT

It's not the authorization!

Re: Validation error while creating calculation view

Former Member — Thu, 27 Aug 2015 21:43:32 GMT

Hmm...

Re: Validation error while creating calculation view

Former Member — Fri, 28 Aug 2015 13:48:37 GMT

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

Re: Validation error while creating calculation view

Former Member — Fri, 28 Aug 2015 13:55:02 GMT

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

Re: Validation error while creating calculation view

lbreddemann — Fri, 28 Aug 2015 15:00:50 GMT

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...

Re: Validation error while creating calculation view

Former Member — Fri, 28 Aug 2015 15:09:55 GMT

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

Re: Validation error while creating calculation view

lbreddemann — Fri, 28 Aug 2015 15:25:57 GMT

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?

Re: Validation error while creating calculation view

Former Member — Fri, 28 Aug 2015 15:32:47 GMT

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

Re: Validation error while creating calculation view

lbreddemann — Fri, 28 Aug 2015 15:38:33 GMT

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.

Re: Validation error while creating calculation view

Former Member — Fri, 28 Aug 2015 15:51:47 GMT

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

Re: Validation error while creating calculation view

lbreddemann — Fri, 28 Aug 2015 16:27:41 GMT

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