System variable types in a sqlprocedure

[AntonPierhagen]

I have created a table in a HDBCDS file;

In a store procedure i would like to use this entity and write the following code;

But when i try to build the procedure, it will return with an error.

THere is a mismatch with HEADER.PURCHASEID and the variable AP_ID.

The first one is of the type CORE/EPM.BusinessKey. My own declared variable, AP_ID is of the type BIGINT.

So in my procedure file, i also would like to address the CORE library , so i can set my variable AP_ID at the right

one.

But i cannot find anything who it is done.

Or, how can i know how the CORE data type BusinessKey is build up?

Kindly here from you!

Anton Pierhagen

[lbreddemann · Accepted solution]

You didn't read the relevant part of my first answer: it is about the NAME of the column and not about the technical data type.

This is confirmed by the error message:

Error: com.sap.hana.di.procedure: Database error 1306: : return type mismatch: Procedure shine:functions::get_po_header_data: Attribute name "ap_id" different from Attribute name: "PURCHASEORDERID" : line 40 col 2 (at pos 831) [8201003]

If you change the code in your procedure to

ap_table = SELECT PURCHASEORDERID as "ap_id"
                , GROSSAMOUNT     as "ap_netto"
                , NETAMOUNT       as "ap_brutto"
                , TAXAMOUNT       as "ap_tax"
                , GROSSAMOUNT + NETAMOUNT + TAXAMOUNT as "ap_combi"
           FROM "PO.Header" 
           ORDER BY "ap_combi" DESC LIMIT 3;

The error should not occur anymore.

Note that you have to put the lowercase column names from your table definition in quotation marks to avoid automatic case-conversion. Also, you might want to check whether GROSSAMOUNT really should map to "ap_netto"...

Note additionally how formatting the SELECT "vertically" helps matching it to the table definition and makes reading the structure of the statement easier by avoiding the "scroll to the far side of the editor".