Create an SQLScript Procedure With SELECT Statement

[Former Member]

Hi

I am trying to create an SQL script Procedure with select statement as below , but I am getting error while checking ...........

Code in SQL Script Procure is

CREATE PROCEDURE get_pi_quntity_sql (

in InvoivingStatus nvarchar(1),

out pi_quntity tt_pi_quntity)

          LANGUAGE SQLSCRIPT

          SQL SECURITY INVOKER

          --DEFAULT SCHEMA <schema>

          READS SQL DATA AS

BEGIN

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

          Write your procedure logic

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

pi_quntity = 

select a."PurchaseOrderID",a."CreatedAt",a."partnerId",b."PurchaseOrderItem",b."currency",b."GrossAmount",b."ProductId",b."Quantity", b."QuantityUnit" from

"SAP_HANA_EPM_DEMO"."sap.hana.democontent.epm.data::purchaseOrder" as a

inner join "SAP_HANA_EPM_DEMO"."sap.hana.democontent.epm.data::purchaseOrderItem" as b

  on a."PurchaseOrderId" = b."PurchaseOrderId"

  where a."InvoivingStatus" = :InvoivingStatus;

END;

Local Table Type is

create type tt_pi_quntity as table (

PurcharseOrderID NVARCHAR(10),

CreatedAt DATE ,

PartnerId NVARCHAR(10),

PurchaseOrderItem NVARCHAR(10),

Currency nVARCHAR(5),

GrossAmount DECIMAL(15,2),

ProductId NvARCHAR(10),

Quntity DECIMAL(13,3),

QuantityUnit NvARCHAR(3)

)

I am getting error like this

ERROR      workshop/sessiona/00/procedures/get_pi_quntity_sql.procedure

           Repository: Internal error during statement execution, please see the database error traces for additional details;error executing statement; invalid column name: A.PurchaseOrderID: line 7 col 11 (at pos 408) at ptime/query/checker/check_expr.cc:694

ERROR      workshop/sessiona/00/procedures/get_pi_quntity_sql.procedure

           Repository: Encountered an error in repository runtime extension;object was not activated due to previous activation errors

ERROR      workshop/sessiona/00/procedures/get_pi_quntity_sql.procedure

           Object potentially broken due to transaction rollback.

ERROR                 exception 40177:

repository/base/activation/activator.cpp:828

Object potentially broken due to transaction rollback.

Any in puts are really helpful.

Regards

hari

[thomas_jung · Accepted solution]

You are selecting PurchaseOrderID from sap.hana.democontent.epm.data::purchaseOrder, but the valid column name is PurchaseOrderId.  The columns are case sensitive.

[Former Member · Accepted solution]

Hi Thomas ,

Good to see your replying

I have corrected it , it's working now

Thanks

Hari

[Former Member]

Hi Hari,

According to error message your posted, it has invalid column name (PurchaseOrderID) in "SAP_HANA_EPM_DEMO"."sap.hana.democontent.epm.data::purchaseOrder" table, so could you double confirm existing or not, if no, please define this column name or revise incorrect name.

Hope this can help you.

Regards,

Jerry.