Internal error during instantiating calculation model

[BenedictV]

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.

OR

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

OR

Tthe error happens in Rev48 systems.

Can we have a final judgement please?

Thanks,

Benedict

[Former Member]

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.

[former_member182114]

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

[BenedictV]

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.

[BenedictV]

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

[former_member182114]

Hi Benedict,

If you ommit the line which is restricting it works?

Also did you tried you lower case?

Regards, Fernando Da Rós

[BenedictV]

hi Fernando,

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

[former_member182114]

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

[BenedictV]

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

[Former Member]

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.

[former_member182114]

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

[Former Member]

Hi Feranado,

You are right. tested only on Modeler side only.

Thanks

Basha

[BenedictV]

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

[henrique_pinto]

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.

[RichHeilman]

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

[BenedictV]

Thanks Rich.

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

[henrique_pinto]

Thanks for the reply, Rich.