Skip to Content

Error while activating a procedure

Hello All,

I am trying a create a basic procedure but I am getting a error while activating it. Below is the snippet for your reference:

CREATE PROCEDURE get_bp_address_by_role_sql (

  in partnerrole NVARCHAR(3),

  out bp_addresses tt_bp_addresses

)

  LANGUAGE SQLSCRIPT

  SQL SECURITY INVOKER

  READS SQL DATA AS  

BEGIN

  bp_addresses =

  SELECT a."PartnerId", a."Partnerrole", a."EmailAddress", a."CompanyName", a."AddressId",

  b."City", b."PostalCode", b."Street"

  FROM "SAP_HANA_DEMO"."sap.hana.democontent.epm.data::EPM.MD.BusinessPartner" as a

  INNER JOIN "SAP_HANA_DEMO"."sap.hana.democontent.epm.data::EPM.MD.Addresses" as b

  ON a."AddressId" = b."AddressId"

  WHERE a."PartnerRole" = :partnerrolr;

END;

The logical table type associate with this procedure is as below -

create type tt_bp_addresses as table (

  PartnerId nvarchar(10),

  PartnerRole nvarchar(3),

  EmailAddress nvarchar(255),

  CompanyName nvarchar(80),

  AddressId NVARCHAR(10),

  City NVARCHAR(40),

  PostalCode NVARCHAR(10),

  Street NVARCHAR(60)

)

I am getting the below error during activation -

invalid column name: A.AddressId: line 8 col 8 (at pos 643)

I have verified the column names in EMP.hdbdd files and hence assuming that the column name is correct but fail to understand the reason behind the exception. I would be grateful if someone can help me out here.

Thanks,

-Prashant.

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

1 Answer

  • Best Answer
    Jan 05, 2015 at 09:54 PM

    I'm not certain that this is the problem but I know hana is somewhat lax in the different ways it allows you to type fields (ie: it allows quotes around field names and also allows blank and also allows the way you typed it without quotes around the table alias but with quotes around the field name only) but can you try removing quotations completely like this?

    SELECT a.PartnerId, a.Partnerrole, a.EmailAddress, a.CompanyName, a.AddressId,

      b.City, b.PostalCode, b.Street

    FROM "SAP_HANA_DEMO"."sap.hana.democontent.epm.data::EPM.MD.BusinessPartner" a

    Also I noticed the A.Address in your error is showing capitalized even though your code shows lower case.  Did you cut & paste all of this or is it possible that indeed you have a capital A somewhere?  Also try removing AS from the table alias like above.

    -Patrick

    Add comment
    10|10000 characters needed characters exceeded