Procedure using CE_PROJECTION - filter issue

[former_member182500]

Hi,

On Hana AWS SP05 and looking at procedures.

Very straightforward example however when I execute the procedure (with logic as below with CE_PROJECTION with filter) I get the error "Internal error during instantiating calculation model".  If I remove only the filter part from the projection function expression (so left with the projection field list only) the contents of lt_bp are output to item and the records are shown (obviously a filter is not performed and I get the entire table content).  I cannot see the issue with the syntax of the filter, most grateful for any help.

"Could not execute 'call "_SYS_BIC"."exercises.group01.procedures/get_items_by_quantityunit_type_ce"( quantityunit => ...' in 54 ms 185 µs . SAP DBTech JDBC: [2048]: column store error: search table error:  [34023] Internal error during instantiating calculation model ".

CREATE PROCEDURE get_items_by_quantityunit_type_ce ( in quantityunit NVARCHAR(3),

                                                     out item tt_item )

    LANGUAGE SQLSCRIPT

    SQL SECURITY INVOKER

    READS SQL DATA AS

BEGIN

/*****************************

    Write your procedure logic

*****************************/

lt_bp = CE_COLUMN_TABLE("EXERCISE01"."exercises.group01.data::item",

                        ["OrderId", "OrderItem", "QuantityUnit" ]);

item = CE_PROJECTION(:lt_bp,["OrderId","OrderItem","QuantityUnit"], '"QuantityUnit" = :quantityunit');

END;

[henrique_pinto · Accepted solution]

In the SPS5 SQLScript reference there is actually a similar sample, not in CE_PROJECTION but under CE_CALC (though the filter part has nothing to do with CE_CALC itself). Check page 25 for this exert:

with_tax = CE_PROJECTION(:product, ["CID", "CNAME", "OID", "SALES",
 CE_CALC('"SALES" * :vat_rate',
 decimal(10,2)) AS "SALES_VAT"],
 '"CNAME" = '':cname''');

So, apparently, you need to have two single quotes before the variable instantiation and 3 after (don't ask me why). In your particular case, it'd be something like

item = CE_PROJECTION(:lt_bp,["OrderId","OrderItem","QuantityUnit"], '"QuantityUnit" = '':quantityunit''');

Try that and let us know how it goes.

[former_member182500 · Accepted solution]

HI all,

Many thanks for the swift response.  Unfortunately I have just tried several attempts and cannot get this working (while the meter runs on AWS, yikes).

If I use the following projection filter syntax as Henrique suggest, which would seem as per the CE_CALC example in the SQLscript guide, it should be :

' "QuantityUnit" = '':quantityitem'' '); 

where I have single quote, double quote, table field, double quote, equals, single quote single quote, colon, input parameter, single quote single quote single quote (then followed by closing bracket and semi-colon) I get the input parameter highlighted in red with message "syntax error: "':quantityitem'" is incorrect or misplaced.  Of course I can commit but activation fails.

[former_member184768 · Accepted solution]

Hi Jon,

The CE_COLUMN_TABLE definition in your code is

lt_bp = CE_COLUMN_TABLE("EXERCISE01"."exercises.group01.data::item",

                        ["OrderId", "OrderItem", "QuantityUnit" ]);

As per the documentation, the syntax for this, takes TABLE NAME as the first input parameter. Can you please check if the "EXERCISE01"."exercises.group01.data::item"  is the correct table representation. Are you able to select the data from the table in SQL editor.

Regards,

Ravi

[former_member182500 · Accepted solution]

Hi Ravindra,

See original post.  If I remove the filter in the ce function it returns all the data from the table.  This indicates the correct schema and package are being used.  I also have an equivalent SQL procedure using same schema and package which also works fine.  The problem is specifically with the filter syntax I believe.

Many thanks.

[former_member182114 · Accepted solution]

Hi Jon-Paul,

It's really confusing this quotes... SQL is one way, CE is another way and sometimes CE doesn't work always in same way (just to make things easier).

You added an important information about where you are trying it.

If you are doing this on PACKAGE or SQL Studio directly, it should work exactly as you posted.

But you are using repository (as you trying commit/activation) and this I faced that syntax can differ. Don't know why but validation ends in error.

I tried the code below and it worked with Basha and Henrique suggestion, calling direct from SQL Studio Editor (catalog objects) on Studio rev47 and HDB COE (Walldorf) rev50.

drop table c5115825.item;
drop type c5115825.tt_item;
drop procedure c5115825.get_items_by_quantityunit_type_ce;
create column table c5115825.item      ("OrderId" INTEGER,"OrderItem" INTEGER,"QuantityUnit" DECIMAL(16,2));
create type  c5115825.tt_item as table ("OrderId" INTEGER,"OrderItem" INTEGER,"QuantityUnit" DECIMAL(16,2));
insert into c5115825.item values (1,1,10);
insert into c5115825.item values (1,2,20);
CREATE PROCEDURE c5115825.get_items_by_quantityunit_type_ce
( in quantityunit NVARCHAR(3),out item c5115825.tt_item )
LANGUAGE SQLSCRIPT SQL SECURITY INVOKER READS SQL DATA AS
BEGIN
lt_bp = CE_COLUMN_TABLE("C5115825"."ITEM",
          ["OrderId", "OrderItem", "QuantityUnit" ]);
item = CE_PROJECTION(:lt_bp,
          ["OrderId","OrderItem","QuantityUnit"],
          ' "QuantityUnit" = :quantityunit ');
END;
call c5115825.get_items_by_quantityunit_type_ce('10',?);

Also tested on package and it worked fine in both way:

call "_SYS_BIC"."c5115825/GET_ITEMS_BY_QUANTITYUNITY_TYPE_CE_PKG"('10',?);

I didn't tested the repository version as you are trying.

Try out the first option here.

BTW: What is the revision of HANA you are using?

Regards, Fernando Da Rós

[henrique_pinto · Accepted solution]

You mentioned a highlighted syntax error - I don't remember any syntax highlighting in previous versions. It seems to me that, as Fernando has mentioned, there is an issue with the new SQLScript editor through the Repository (HANA Development perspective). Apparently it's not accepting this syntax.

What if you try to create the procedure in SQL Editor, with the CREATE PROCEDURE method, as mentioned by Fernando?

[former_member182500 · Accepted solution]

Hi Fernando, Henrique,

Thanks very much for your feedback and hints, very helpful indeed.

Apologies for not being specific earlier in thread about creating the procedure within the repository - still performing my apprenticeship in Hana so getting to grips with several options of achieving the same result.

I'm using Hana on AWS SP05 Rev 48.  I have just replicated Fernando's example directly in the SQL editor and had this working in 2 minutes with the correctly fetched rows.

I guess it remains to figure out any alternative syntax difference from within the repository procedure or if indeed there is a bug.  Would be wonderful if I can get any confirmation from another SP05 Rev 48 installation.

Thanks again gentlemen.

[former_member182500 · Accepted solution]

Hi,

Just to add I have changed the syntax of the filter to replicate the SQL editor based example proposed by Fernando, which works, into the repository based version, code below (single quote, space, double quote, table field, double quote, equal, colon, parameter, space, single quote, followed by closing bracket and semi-colon.  This commits and activates OK. 

However when I execute it from the SQL editor (trying each of the two calls as I wanted to try the syntax as per Fernando's call example without specification of parameters in call)  I get the "error during instantiating calculation model" internal error:

call "_SYS_BIC"."exercises.group01.procedures/get_items_by_quantityunit_type_ce"('EA', ? )

call "_SYS_BIC"."exercises.group01.procedures/get_items_by_quantityunit_type_ce"(quantityunit => 'EA',item => ?)

Could not execute 'call "_SYS_BIC"."exercises.group01.procedures/get_items_by_quantityunit_type_ce"('EA', ? )' in 54 ms 241 µs . SAP DBTech JDBC: [2048]: column store error: search table error:  [34023] Internal error during instantiating calculation model

CREATE PROCEDURE get_items_by_quantityunit_type_ce ( in quantityunit NVARCHAR(3),

                                                     out item tt_item )

    LANGUAGE SQLSCRIPT

    SQL SECURITY INVOKER

    READS SQL DATA AS

BEGIN

lt_bp = CE_COLUMN_TABLE("EXERCISE01"."exercises.group01.data::item",

                          ["OrderId", "OrderItem", "QuantityUnit" ]);

item = CE_PROJECTION(:lt_bp,["OrderId","OrderItem","QuantityUnit"],  ' "QuantityUnit" = :quantityunit ');

END;

[former_member182114 · Accepted solution]

Hi Jon-Paul,

Pay attention to the data types. I created QuantityUnit as DECIMAL(16,2)... So it can't work with EA because it's not a number.

Change to NVARCHAR(3) on table and table type and it should work, but in this case only with quotes twice.

Regards, Fernando Da Rós

[henrique_pinto · Accepted solution]

Let us introduce to the topic and see if he can add whether this is a bug, something that will be/has been introduced in later releases or if we're just missing something out. 😉

[former_member182500 · Accepted solution]

Hi Fernando,

I replicated the SQL editor version I successfully executed earlier, changing the type of QuantityUnit from DECIMAL to NVARCHAR, and adding the additional single quotes around procedure field quantityunit in the filter clause extension, so the below successfully executes and returns expected records in SQL results:

item = CE_PROJECTION(:lt_bp, ["OrderId","OrderItem","QuantityUnit"],

' "QuantityUnit" = '':quantityunit''' );

However, if I use exactly the same CE_PROJECTION syntax in the repository version (copy/paste the item = CE_PROJECTION line from the SQL editor version to repository artefact) I have a client-side syntax error with :quantityunit underlined red with context message "Syntax error: quantityunit is incorrect or misplaced".

Thanks again.

[former_member182114 · Accepted solution]

Hi Jon-Paul,

I've just upgraded to rev52 and made a test on repository and reached same fake sintax error.

Please raise a ticket on SAP support so they can correct this.

Meanwhile, your options seems to be do not use repository for some codes or write SQL:

item = select "OrderId","OrderItem","QuantityUnit" from :lt_bp where "QuantityUnit" = :quantityunit;

Kind regards, Fernando Da Rós

[former_member182500 · Accepted solution]

Fernando thanks very much for the confirmation.

As an independant AWS user of Hana im not sure how I could raise an OSS message via the support portal without my own S* user?

Can anyone forward this to OSS or bring a member of the Hana product team in here? Thanks!

[former_member182500 · Accepted solution]

Henrique see above regarss confirmation syntax issue from Fernando, know anyone in the product team?  Thank-you.

[former_member182114 · Accepted solution]

Just another information about the issue:

The syntax error with line below is fake and only happens on Studio SQL Editor (repository view). You can COMMIT + CHECK + ACTIVATE + RUN without problems...

item = CE_PROJECTION(:lt_bp,["OrderId","OrderItem","QuantityUnit"],' "QuantityUnit" = '':quantityunit'' ');

You can step over this system error and have it error fre on same Studio escaping with back slash.... BUT you have a JSON syntax error on CHECK or ACTIVATE

item = CE_PROJECTION(:lt_bp,["OrderId","OrderItem","QuantityUnit"],' "QuantityUnit" = \'\':quantityunit\'\' ');

Regards, Fernando Da Rós

[Former Member]

Hi Jon,

I tried the above procedure on SP05 , version 47 and it is working fine .

CREATE PROCEDURE "P1763569281".demo_proc6 (in artid integer,out tt_mblnr "P1763569281"."ART1")

LANGUAGE SQLSCRIPT READS SQL DATA AS

BEGIN

val1 =CE_COLUMN_TABLE("EFASHION_TUTORIAL"."SHOP_FACTS",[ARTICLE_ID,AMOUNT_SOLD,QUANTITY_SOLD]);

tt_mblnr=CE_PROJECTION(:val1,[ARTICLE_ID,AMOUNT_SOLD,QUANTITY_SOLD],'"ARTICLE_ID"=:artid')

;

END

;

CALL "P1763569281".demo_proc6(166544,?)

Thanks

Basha.

[former_member182114]

Hi Basha,

On your test sample works as it's integer and don't need the quotes.

Henrique's sample should works fine as the parameter is NVARCHAR and the quotes are necessary for CE work properly.

Regards, Fernando Da Rós

[henrique_pinto]

Now I understood why the double quotes are necessary. 🙂