topic Re: Invalid column Index error - While consuming Calculation view via Native SQL in Application Development and Automation Discussions

Invalid column Index error - While consuming Calculation view via Native SQL

Former Member — Tue, 08 Apr 2014 10:19:53 GMT

Hi Experts,

I am trying to consume a Calculation view (sql script one) , which has input parameters, via Native SQL in a ABAP program .

Code snippet for the same would be as follows , Upon execution, it throws an error "Invalid Column Index (8) error " . Can anyone help what could be the issue here ?

Thanks in Advance,

Suma

REPORT ZTEST_HANA2.

*Report to consume Calculation view (script based) from ABAP

PARAMETERS: ip_docnr type BELNR_D,

ip_gjahr type GJAHR,

ip_bukrs type BUKRS,

ip_blgr type FAGL_RLDNR.

DATA: LO_SQL_STMT TYPE REF TO CL_SQL_STATEMENT,

LO_CONN TYPE REF TO CL_SQL_CONNECTION,

LO_RESULT TYPE REF TO CL_SQL_RESULT_SET,

LV_SQL TYPE STRING,

LR_DATA TYPE REF TO DATA.

DATA: LX_SQL_EXC TYPE REF TO CX_SQL_EXCEPTION,

LT_SEPMAPPS_CLSDINV TYPE TABLE OF SEPMAPPS_CLSDINV,

LV_TEXT TYPE STRING.

TRY.

lv_sql = |SELECT * FROM "_SYS_BIC"."DEMO-ABAP/CA_GET_FI_DATA" | &&

|WITH PARAMETERS ('placeholder'= ('$$p_DOCNR$$','{ ip_docnr }'),| &&

|'placeholder'=('$$p_GJAHR$$','{ ip_gjahr }')| &&

|,'placeholder'= ('$$S_BUKRS$$','{ ip_bukrs }')| &&

|,'placeholder'= ('$$p_base_ledger$$','{ ip_blgr }') )| .

LO_CONN = CL_SQL_CONNECTION=>GET_CONNECTION( ).

"Create an SQL statement to be executed via the connection

LO_SQL_STMT = LO_CONN->CREATE_STATEMENT( ).

"Execute the native SQL query

LO_RESULT = LO_SQL_STMT->EXECUTE_QUERY( LV_SQL ).

"Read the result into the internal table lt_sepmapps_clsdinv

GET REFERENCE OF LT_SEPMAPPS_CLSDINV INTO LR_DATA.

LO_RESULT->SET_PARAM_TABLE( LR_DATA ).

LO_RESULT->NEXT_PACKAGE( ).

LO_RESULT->CLOSE( ).

LO_CONN->CLOSE( ).

CATCH CX_SQL_EXCEPTION INTO LX_SQL_EXC.

LV_TEXT = LX_SQL_EXC->GET_TEXT( ).

MESSAGE LV_TEXT TYPE 'E'.

ENDTRY.

Re: Invalid column Index error - While consuming Calculation view via Native SQL

former_member182114 — Tue, 08 Apr 2014 10:50:28 GMT

Hi Suma,

Post the SQL you success run directly on Studio together with error message (even if is the Invalid column index error).

Check there if the parameters case is working properly... Is it really this confusing options:

p_GJAHR

S_BUKRS

p_base_ledger

Why not all lower or all upper? Anyhow you must test and find which option works according your modeling

Regards, Fernando Da Rós

Re: Invalid column Index error - While consuming Calculation view via Native SQL

jasmin_gruschke — Tue, 08 Apr 2014 11:11:59 GMT

Hi Suma,
in addition to Fernando's proposal, can you please check, where exactly the issue occurs? I guess the call of "next_package" fails, right?
In case yes, the question is if the usage of SEPMAPPS_CLSDINV as type definition for the output parameter is correct respectively should be avoided.

SEPMAPPS_CLSDINV is an external dictionary view for another HANA view, which might lead to issues when using "SELECT *". You can't be sure about the ordering of columns in your calc view output parameter and the external view.

As a test, you could try to define a type yourself (let's say only containing one field), an internal table of this structured type and perform a "SELECT <field>" into the internal table.

Cheers,

Jasmin

Re: Invalid column Index error - While consuming Calculation view via Native SQL

Former Member — Wed, 09 Apr 2014 10:18:11 GMT

Hi Jasmin,

Use of SEPMAPPS_CLSDINV was the issue, have declared an internal table of type external view created for the calculation view that I created earlier, it worked now.

Thanks,

Suma