Procedure using CE_PROJECTION - filter issue

[former_member182500]

Hi,

I'm making a projection as per below, stating the field list then qualifying with a filter.  When using the filter I get the error shown below which seems to suggest a search error.  If I execute the procedure without the filter all table content is returned successfully with no error.  Grateful if anyone can point out the issue with the filter syntax, many thanks.

"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;

[Former Member]

Hello John,

Replace the existing line,

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

with

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

and try again. It should work just fine.

You may also refer to document for SQL Script for HANA http://help.sap.com/hana/hana_dev_sqlscript_en.pdf page number 25 for same issue.

Regards

Nimesh Kapadia

[former_member182500]

HI,

Thanks for the reply.  This does not work either unfortunately.  The procedure fails upon server activation (client side this time it has no "in-line" syntax error regarding parameter quantity unit, with it underlined red).  Almost seems like it wants parameter quantityunit in the field list??? - "QuantityUnit" = ":quantityunit" is missing in projection

Error log as below:

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;

; SqlScript: Field :quantityunit in filter expression "QuantityUnit" = ":quantityunit" is missing in projection: line 9 col 69 at SQLScript/SqlScriptError.cpp:31

    at com.sap.ndb.studio.dwb.team.regi.impl.Regi.buildRegiErrorException(Regi.java:992)

    at com.sap.ndb.studio.dwb.team.regi.impl.Regi.activate(Regi.java:741)

    at com.sap.ndb.studio.dwb.team.model.repository.impl.DwbObjectImpl.activate(DwbObjectImpl.java:85)

    at com.sap.ndb.studio.dwb.team.jobs.impl.ActivateJob$ActivateResourceAction.doAction(ActivateJob.java:82)

    at com.sap.ndb.studio.dwb.team.jobs.FileResourceAction.doActionInternal(FileResourceAction.java:48)

    at com.sap.ndb.studio.dwb.team.jobs.FileResourceAction.action(FileResourceAction.java:33)

    at com.sap.ndb.studio.dwb.team.jobs.impl.ResourceActionAndRefreshJob.run(ResourceActionAndRefreshJob.java:160)

    at org.eclipse.core.internal.jobs.Worker.run(Worker.java:54)

Heres the procedure code as it stands.  I have already tried giving a distinctly different name for the parameter, such as p_quant, without any luck.

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

Hello Jon,

I see issue with filter condition.Writing filter condition in plain text as it would be difficult to differentiate between single quote and double quotes in this editor.

Single Quote Double Quote QuantityUnit Double Quote = Single Quote Single Quote :quantityunit Single Quote Single Quote Single Quote

Hope this time it works

Regards

Nimesh Kapadia

[danielculp]

Could you please append the relevant part of the indexserver trace file?

(diagnosis tab in dbstudio)

Thanks