Question Re: Internal error during instantiating calculation model in Technology Q&A

Internal error during instantiating calculation model

BenedictV — Thu, 20 Jun 2013 19:02:45 GMT

Hi,

I went through a lot of posts in SCN, openSAP and the SQL script guide document for the error "Internal error during instantiating calculation model". Typically the error arises when one tries to use a restriction in a CE_PROJECTION command.

I know that this topic has been widely discussed already but no where did I see the issue getting solved for the person who complains about the issue.

Can we have a final say on the syntax to be used for a CE_PROJECTION

Syntax 1:

tp2_scitairp = CE_PROJECTION(:tp_scitairp, [ "MANDT", "ID", "MASTERCITY", "COUNTRY"], '"ID" = :AIRPORT_CODE' );

notation: single quote double quote field_name double quote = variable_name singlequote

Syntax 1 activates ok but throws error when called.

Syntax 2:

tp2_scitairp = CE_PROJECTION(:tp_scitairp, [ "MANDT", "ID", "MASTERCITY", "COUNTRY"], '"ID" = :AIRPORT_CODE' );

notation: 3 single quotes field_name 2 single = variable_name singlequote

Syntax error when checking/activating the procedure

Tthe error happens in Rev48 systems.

Can we have a final judgement please?

Thanks,

Benedict

Re: Internal error during instantiating calculation model

Former Member — Fri, 21 Jun 2013 08:03:18 GMT

Hi Benedict,

The below code should work . Please post how you are calling the this procedure?

Syntax 1:

tp2_scitairp = CE_PROJECTION(:tp_scitairp, [ "MANDT", "ID", "MASTERCITY", "COUNTRY"], '"ID" = :AIRPORT_CODE' );

notation: single quote double quote field_name double quote = variable_name singlequote

Thanks

Basha.

Re: Internal error during instantiating calculation model

Former Member — Fri, 21 Jun 2013 12:13:47 GMT

Hi Benedict,

Is this parameter AIRPORT_CODE string? If yes you need two single quotes on it also as it's string. And if I remind well put it in lowercase also.

tp2_scitairp = CE_PROJECTION(:tp_scitairp,

[ "MANDT", "ID", "MASTERCITY", "COUNTRY"],

' "ID" = '':airport_code'' ' );

Regards, Fernando Da Rós

Re: Internal error during instantiating calculation model

BenedictV — Fri, 21 Jun 2013 18:51:05 GMT

Hi Basha,

My call statement looks like this,

call "_SYS_BIC"."myprojpackage.opensapproj.Models/SFLIGHT_PROCEDURE_CE_W3U3"

( AIRPORT_CODE => 'ACA' , AIRPORT_LOC => ? );

AIRPORT_CODE is nvarchar(3) and ID is also nvarchar(3) in the table from which it is selected and projected.

Re: Internal error during instantiating calculation model

BenedictV — Fri, 21 Jun 2013 18:52:44 GMT

Hi Fernando,

This is the error that I get if I use single quotes:

Description Resource Path Location Type

Repository: Internal error during statement execution, please see the database error traces for additional details;error executing statement create procedure "_SYS_BIC"."myprojpackage.opensapproj.Models/SFLIGHT_PROCEDURE_CE_W3U3" ( in AIRPORT_CODE NVARCHAR(3) , out AIRPORT_LOC "_SYS_BIC"."myprojpackage.opensapproj.Models/SFLIGHT_PROCEDURE_CE_W3U3/tabletype/tt_AIRPORT_LOC" ) language SQLSCRIPT sql security invoker reads sql data as CREATE PROCEDURE SFLIGHT_PROCEDURE_CE_W3U3 (nttin AIRPORT_CODE nVARCHAR(3),nttout AIRPORT_LOC tt_AIRPORT_LOCnt ) ntLANGUAGE SQLSCRIPTntSQL SECURITY INVOKER ntREADS SQL DATA ASnBEGINn/***************************** ntWrite your procedure logic n *****************************/nnt_scitairp = CE_COLUMN_TABLE("SFLIGHT"."SCITAIRP",nttttttt ["MANDANT", "AIRPORT", "MASTERCITY", "COUNTRY"]);nntp_scitairp = CE_PROJECTION(:t_scitairp, nttttttt["MANDANT" as "MANDT", "AIRPORT" as "ID", "MASTERCITY", "COUNTRY"]);nntp2_scitairp = CE_PROJECTION(:tp_scitairp, nttttttt[ "MANDT", "ID", "MASTERCITY", "COUNTRY"], nttttttt' "ID" = '':AIRPORT_CODE'' ' );ntttttttnt_sairport = CE_COLUMN_TABLE("SFLIGHT"."SAIRPORT", nttttttt["MANDT", "ID", "NAME", "TIME_ZONE"]);ntttttttnAIRPORT_LOC = CE_JOIN(:tp2_scitairp, :t_sairport, ntttttt["MANDT", "ID"], ntttttt["MANDT", "ID", "MASTERCITY", "NAME", "COUNTRY", "TIME_ZONE"]);nntttttt ntttttttttttttttttt tttttnEND;; sql syntax error: incorrect syntax near "SFLIGHT_PROCEDURE_CE_W3U3": line 1 col 315 (at pos 315) at qp_gram.y:34124 SFLIGHT_PROCEDURE_CE_W3U3.procedure /OpenSAPDemoProj/Models line 1 Repository Activation Problem

Re: Internal error during instantiating calculation model

Former Member — Fri, 21 Jun 2013 18:59:56 GMT

Hi Benedict,

If you ommit the line which is restricting it works?

Also did you tried you lower case?

Regards, Fernando Da Rós

Re: Internal error during instantiating calculation model

BenedictV — Fri, 21 Jun 2013 19:06:58 GMT

hi Fernando,

It works perfectly if no restrictions are used. But has the same issue even when lower case letters are used.

Re: Internal error during instantiating calculation model

Former Member — Fri, 21 Jun 2013 19:38:00 GMT

Hi Benedict,

Follow some ways that worked in past, currently I can't say exactly for each version I used each one. My suggestion is to try each one and make some variants of it. It's surprised as a space sometimes can provoke an error.

option 1: ** worked for R/O scenario. I expected that it works for you

tp2_scitairp = CE_PROJECTION(:tp_scitairp,

[ "MANDT", "ID", "MASTERCITY", "COUNTRY"],

' "ID" = '':airport_code'' ' );

option 2: ** worked for R/O scenario, I'm affraid it's not working anymore but give a try

tp2_scitairp = CE_PROJECTION(:tp_scitairp,

[ "MANDT", "ID", "MASTERCITY", "COUNTRY"],

' "ID" = string(:airport_code) ' );

option 3: ** No bealty on this but worked even on R/W scenarios but with some performance loss

tp2_scitairp = CE_PROJECTION(:tp_scitairp,

[ "MANDT", "ID", "MASTERCITY", "COUNTRY",

CE_CALC(' string(:airport_code) ',NVARCHAR(3)) AS ID_FILTER

' "ID" = "ID_FILTER" ' );

option 4: ** always worked but as you are coding CE you may not reach the top of performance

tp2_scitairp = SELECT "MANDT", "ID", "MASTERCITY", "COUNTRY"

FROM :tp_scitairp

WHERE "ID" = :airport_code;

Regards, Fernando Da Rós

Re: Internal error during instantiating calculation model

BenedictV — Fri, 21 Jun 2013 21:28:06 GMT

Hi Fernando,

Only the SELECT option works

i think it must a problem with Rev 48. Even the 'validate' and 'edit' buttons are greyed out in the filter expression in the calculation view created through graphical view.

Thanks. Your help is greatly appreciated.

Benedict

Re: Internal error during instantiating calculation model

Former Member — Fri, 21 Jun 2013 22:31:55 GMT

Hi Benedict,

The below syntax is working fine in rev 46: I don't why it is not working Rev 48.

tp2_scitairp = CE_PROJECTION(:tp_scitairp,

[ "MANDT", "ID", "MASTERCITY", "COUNTRY"],

' "ID" = '':airport_code'' ' );

and

tp2_scitairp = CE_PROJECTION(:tp_scitairp,

[ "MANDT", "ID", "MASTERCITY", "COUNTRY"],

' "ID" = '':AIRPORT_CODE'' ' );

Thanks

Basha.

Re: Internal error during instantiating calculation model

Former Member — Fri, 21 Jun 2013 23:41:07 GMT

Hi Basha,

When reading your last post I remind one thing. There's a different behavior from SAP Modeler perspective where you have two options:

- run script on SQL console

- put it on a procedure inside a package on content

Other is the SAP HANA Developer perspective, which you can create a procedure with a file procname.hdbprocedure.

My suggestions where tested only on Modeler side, I guess the error is happening to Benedict on SAP HANA Developer perspective, project explorer.

- here I remind faced some syntax validation error of Studio (revision 47 if I'm not wrong)

Where you two are trying to code the samples?

Regards, Fernando Da Rós

Re: Internal error during instantiating calculation model

Former Member — Sat, 22 Jun 2013 00:30:52 GMT

Hi Feranado,

You are right. tested only on Modeler side only.

Thanks

Basha

Re: Internal error during instantiating calculation model

BenedictV — Sat, 22 Jun 2013 15:59:32 GMT

Yes Sir! you are so right. i didnt know that would make a difference, else I would have mentioned it in the post as well.

I am doing it from the SAP HANA Developer perspective using the '.procedure' file.

Thanks to both of you for your time and help.

Benedict

Re: Internal error during instantiating calculation model

henrique_pinto — Mon, 24 Jun 2013 16:10:20 GMT

Yes apparently the new procedure editor (Development perspective) still has a different behavior, in terms of unsupported syntaxes, of the old Procedure editor (Modeler perspective). I'm not sure it's exactly this case, but I've seen it before (i.e. new procedure editor didn't support RLang scripts, at least in older releases). I'm ccing and so they can be aware of this post.

Re: Internal error during instantiating calculation model

RichHeilman — Tue, 25 Jun 2013 18:18:35 GMT

We are aware of these types of issues, and they are being(have been) addressed in the revisions. I would highly suggest to update to the latest revision.

Cheers,

Rich Heilman

Re: Internal error during instantiating calculation model

BenedictV — Tue, 25 Jun 2013 19:29:07 GMT

Thanks Rich.

Just wanted to know if it was the revision or if I was doing somthing wrong.

Re: Internal error during instantiating calculation model

henrique_pinto — Wed, 26 Jun 2013 03:34:01 GMT

Thanks for the reply, Rich.