I am getting below error, how to fix it?
The work area "LT_MARC" is not long enough. long enough.
DATA: lt_marc TYPE STANDARD TABLE OF marc. SELECT * FROM marc AS mc INNER JOIN mara AS mm ON mm~matnr = mc~matnr into table lt_marc WHERE mm~laeda >= 20230801.
Update: Using RSDU_EXEC_SQL I was able to execute above SQL Query. Not sure why class cl_sql_statement method execute_query not executing above query.
Due to the
SELECT * in combination of
INNER JOIN you are selecting the fields of both
mara. But your internal table is a
TABLE OF marc, which means that it is missing the fields of
SELECT * use a list of the fields of those tables you are actually going to need (this will also improve the performance of the query, by the way). Then declare
lt_marc as a table of a custom type that has only the fields you want. For example:
TYPES: BEGIN OF ty_data_struct, matnr TYPE matnr, ersda TYPE ersda, werks TYPE werks_d, mmsta TYPE mmsta, END OF ty_data_struct. DATA: lt_marc TYPE STANDARD TABLE OF ty_data_struct. SELECT mara~matnr mara~ersda marc~werks marc~mmsta FROM marc AS mc INNER JOIN mara AS mm ON mm~matnr = mc~matnr into table lt_marc WHERE mm~laeda >= '20230801'.
Keep in mind that with this solution, the fields of
ty_data_struct and the list of fields after
SELECT must be in the same order and have the same type (but not necessarily in name). Otherwise the
SELECT will try to write the data into the wrong columns of your internal table, which will give you an error if you are lucky and give you junk data if you are unlucky. So with this solution you have to be careful that when you change your mind about what fields you want to select, you have to do that change at two places synchronously.
INTO TABLE, use
INTO CORRESPONDING FIELDS OF TABLE. That way the
SELECT will only fill the fields you have in the target table (by name). So if you really want all the fields of
marc and none of the fields of
mara (but still want a
JOIN for some reason) you can do this:
DATA: lt_marc TYPE STANDARD TABLE OF marc. SELECT * FROM marc AS mc INNER JOIN mara AS mm ON mm~matnr = mc~matnr INTO CORRESPONDING FIELDS OF TABLE lt_marc WHERE mm~laeda >= 20230801.
Instead of defining the table type explicitly with
DATA variable TYPE type, use an inline declaration with the
DATA(variable) syntax. That way the ABAP interpreter will automatically generate a custom type with all the fields you are selecting:
SELECT * FROM marc AS mc INNER JOIN mara AS mm ON mm~matnr = mc~matnr INTO TABLE @DATA(lt_marc ) WHERE mm~laeda >= '20230801'.
This is the solution I would usually favor. I would still recommend to use a field list instead of
SELECT * with this approach. Because I seriously doubt that you are going to need all the fields of both tables. When you combine
INTO @DATA(var) with a field list, then you will generate much less database load (especially on SAP HANA). Also, the resulting internal table you select into will only have the fields you selected, saving you memory on the application server.