Question Re: System variable types in a sqlprocedure in Technology Q&A

System variable types in a sqlprocedure

AntonPierhagen — Mon, 14 Jan 2019 09:47:25 GMT

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

Re: System variable types in a sqlprocedure

lbreddemann — Mon, 14 Jan 2019 10:23:20 GMT

Without the actual error message we have to guess here. My guess would be that the problem is that the SELECT does not provide the correct column names for the table variable.

PURCHASEID simply is not the same as AP_ID.

That should be fixable by simply using column aliases.

Re: System variable types in a sqlprocedure

AntonPierhagen — Mon, 14 Jan 2019 10:31:58 GMT

Hi Lars

That's correct. I would like to set for the vairable AP_ID the same data type as the PURCHASEID.

The PurchaseID has the type CORE.Businesskey. The AP_ID doesn't. If i let it as it is in the example which is described above, the error would be;

Deploying "src/functions/get_po_header_data.hdbprocedure"...
Error: com.sap.hana.di.procedure: Could not create the database object [8250005]
at "src/functions/get_po_header_data.hdbprocedure" (40:2)
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]
at "src/functions/get_po_header_data.hdbprocedure" (40:2)
Warning: Worker 1 running the "com.sap.hana.di.procedure" plugin has encountered an error while deploying 1 objects [8212030]
Warning: Command failed [8210001]
Error: Worker 1 has encountered an error; all remaining jobs will be canceled [8214600]
Error: Processing work list... failed [8212102]
Make failed (3 errors, 1 warnings): tried to deploy 1 files, undeploy 0 files, redeploy 0 dependent files
Error: Making... failed [8211605]
Error: Starting make in the container "SHINE_HDI_DB_1" with 1 files to deploy, 0 files to undeploy... failed [8214168]
Command failed
Deployment to container SHINE_HDI_DB_1 failed - error: HDI make failed [Deployment ID: none].
Error: HDI make failed
(2s 816ms)

If i try to make it like;

so the AP_ID has the same data type as the PO.HEADER had;

Then the CORE is not regonized.

If haven't found any statement for the PROCEDURE, like;

Which i have in the CDS file who is creating the Header table.

How do i create the same data type in the procedure whom is the same as the table type?

Re: System variable types in a sqlprocedure

lbreddemann — Mon, 14 Jan 2019 11:25:48 GMT

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

Re: System variable types in a sqlprocedure

AntonPierhagen — Mon, 14 Jan 2019 11:51:38 GMT

ah check! Thanks!