EXEC SQL type error when using a table variable after INTO in SELECT

[mitko1994]

Hello,

I would like to execute the following piece of code in an ABAP program.

  EXEC SQL.     
     SELECT  lv_header_line FROM :i_view WHERE :lv_where ORDER BY :lv_header_line INTO :lt_recs
 ENDEXEC.

I get the following error when I to check the code.

"LT_RECS" cannot be a table or a reference and cannot contain either of these objects or strings.

My question is can I somehow define LT_RECS to be a table of a suitable type and if not, can I somehow do an inline declaration of a table that would store the result from the query? I cannot use a fixed table type because the program processes different tables based on user input and the lv_header_line string is different depending on the user input.Running this outside of an EXEC SQL block fails to compile for the addition of an OFFSET to the SELECT which is essential for the processing of data in my case (but I excluded here for simplicity purposes) AMDP is also not an option as it expects fully typed parameters.
This is what I need to replace as it no longer compiles for some reason and nobody knows why that's the case all of a sudden.

 SELECT (lv_header_line) FROM (i_view) WHERE (lv_where) ORDER BY (lv_header_line)          

 INTO CORRESPONDING FIELDS OF TABLE @<gt_data> OFFSET @( lv_skip_pkg_cnt  * pkg_size ) 

 UP TO @pkg_size ROWS.

Regards,Dimitar

[Sandra_Rossi]

Why using EXEC SQL?

You should prefer ADBC over EXEC SQL.

Anyway using SELECT is sufficient.

If you have a problem with SELECT syntax, why not just asking the question about SELECT syntax?

I feel that it doesn't compile just because you changed it to an invalid code.

Could you just provide code which compiles (declarations and so on, not difficult) + what you tried + syntax error you have?

[mitko1994]

Hi sandra.rossi,

I will look into ADBC. The statement that doesn't compile now, used to do so and is actually running productively now. However, if you now run a "check" on the code, it gives a syntax error for that statement. I already posted an issue asking why the code doesn't compile and you confirmed that this very exact statement compiles just fine on ABAP757. I spoke to our BASIS team and it seems that nobody has a clue why and how that issue came to be, so at this stage I'm looking for a work around where I can still use generic types and execute the select statement for any view. Can I somehow use an inline declaration in the EXEC SQL block, like ... INTO @data(lt_records)?

[ThorstenHoefer]

You can use cursors in exec sql.

https://help.sap.com/doc/abapdocu_750_index_htm/7.50/en-US/abapexec_cursor.htm

Dependend on your database system, check bulk collect:

   SELECT employee_id, salary
     BULK COLLECT INTO l_employees
     FROM employees
    WHERE department_id = 50;

[Sandra_Rossi]

No you can't use something like "like ... INTO @data(lt_records)" inside EXEC SQL.

You can now understand that ABAP syntax depends on the ABAP version, so WHY not saying your version?

[mitko1994]

We are running ABAP752, and according to the BASIS team, that hasn't changed since I wrote the program in the first place.

[Sandra_Rossi]

Now that people know it's ABAP 7.52, they can investigate better.

[Sandra_Rossi]

This code compiles on ABAP 7.52:

FIELD-SYMBOLS <gt_data> TYPE table.
DATA(lv_header_line) = ``.
DATA(i_view) = ``.
DATA(lv_where) = ``.
DATA(lv_skip_pkg_cnt) = 0.
DATA(pkg_size) = 0.

SELECT (lv_header_line)
    FROM (i_view)
    WHERE (lv_where)
    ORDER BY (lv_header_line)
    INTO CORRESPONDING FIELDS OF TABLE @<gt_data>
    OFFSET @( lv_skip_pkg_cnt  * pkg_size )
    UP TO @pkg_size ROWS.

So, it's useless investigating on EXEC SQL or ADBC.

You must investigate on why it doesn't compile.

It means you must give more details on the problem.

I hope you understand my point.

[mitko1994]

sandra.rossi, I know it compiles on 7.52. I wrote it on that same version without any issues to begin with. Someone did something and either didn't realize it or doesn't care to admit it, but I can't just wait for it be fixed as I'm responsible for this program and we need it urgently. Therefore, I'm looking for a workaround right now, because I don't want to take the hit for other people's ignorance.

According to this link, ADBC should do the trick:

https://help.sap.com/doc/abapdocu_753_index_htm/7.53/en-US/abenadbc_dml_ddl_abexa.htm

DATA(result) = NEW cl_sql_statement( )->execute_query(
      `SELECT val1, val2 ` &&
      `FROM ` && dbname && ` ` &&
      `WHERE val1 = ` && `'` && key && `'` ).

If I can store the output in generic fashion like that, that should be enough for me. I will give it a try. Thank you for the feedback.

[Sandra_Rossi]

In fact, you don't help yourself by not giving details. It's 3 days lost for an issue which looks simple. You didn't even indicate the original syntax error message...

Anyway, good luck!

[mitko1994]

As I said, In the past, I had already posted a question related to the issue with the syntax error and all with no real suggested resolution (which is probably expected), so it may seem like a waste of time, but it's actually better than nothing.

[mitko1994]

The following snippet did the trick! Thank you for mentioning ADBC.

DATA(result) = NEW cl_sql_statement( )->execute_query(
     |SELECT { lv_header_line } FROM "{ i_view }" { lv_where } ORDER BY { lv_header_line } ASC LIMIT { pkg_size } OFFSET { offset }| ).
      result->set_param_table( itab_ref = REF #( <gt_data> )  ).
      result->next_package( ).