How do I use a certain column of an internal table in an SQL Select statement?

[walkerist]

I have an internal table named T_DATA and I'm planning to use the field IDNRK on a SELECT statement.

 "Code of T_DATA being looped into a structure
LOOP AT t_data INTO DATA(x_data).
ENDLOOP.
"Separate code for selection of data in AUSP/CABN table
        SELECT a~objek,
               a~atwrt
          INTO TABLE @DATA(t_item_cat)
          FROM ausp AS a INNER JOIN cabn AS b
          ON a~atinn = b~atinn
          WHERE a~objek = <--------------------I'm planning to use multiple entries of IDNRK
            AND a~atwrt IN @r_item_cat <------ range of item categories.

Currently, the T_DATA is being looped into a local structure named x_data. However, I was advised not to include the SQL Select statement inside the loop as it can degrade the performance. How can I use the multiple entries of T_DATA-IDNRK in an SQL select statement?<br>

[Tomas_Buryanek]

You can use FOR ALL ENTRIES, or sometimes better create "range table" and use it in select with using "IN" operator.

[walkerist]

I tried using FOR ENTRIES. However this is the result:

If the addition "FOR ALL ENTRIES IN itab" is used, the fields "OBJEK"

and "T_DATA-IDNRK" must have the same type and the same length.

[Tomas_Buryanek]

walkerist Yes that is correct. If you want to use FAE, then you need to modify your internal table little bit as it says. You need column with data type same like the field in DB table you are matching it to.

[walkerist]

Is it possible to transfer the column IDNRK of T_DATA into another internal table?

[Tomas_Buryanek]

walkerist Yes of course 🙂

And one more thing about FAE => always check if the FAE internal table is not empty, otherwise you will select everything which often is not wanted.

[shantraj]

Try This.
"Selection of data in AUSP/CABN table
        SELECT a~objek,
               a~atwrt
          FROM ausp AS a 
	    INNER JOIN cabn AS b
          ON a~atinn = b~atinn
	    INTO TABLE @DATA(t_item_cat)
	    FOR ALL ENTRIES IN @T_DATA
          WHERE a~objek+0(40) = @T_DATA-IDNRK 
AND a~atwrt IN @r_item_cat.

[walkerist]

shantraj thank you but I was getting this error :Unknown column name "OBJEK+0(40)

[shantraj]

Create a structure of T_DATA, loop on it and Pass the value's of T_data to T_Fin_data.

types: begin of ty_data,

obkey(80) type ausp-objey.

(Include Other fields as well).

end of ty_data.

w_fin_data type ty_data,

t_fin_data type table of ty_data.

loop at T_data into Xdata.

w_fin_data-idnrk = Xdata-idnrk.

Append w_fin_data to T_fin_data.

Clear w_fin_data.

End Loop.

Make IDNRK field of length 80.

"Selection of data in AUSP/CABN table
        SELECT a~objek,
               a~atwrt
          FROM ausp AS a 
	    INNER JOIN cabn AS b
          ON a~atinn = b~atinn
	    INTO TABLE @DATA(t_item_cat)
	    FOR ALL ENTRIES IN @T_FIN_DATA
          WHERE a~objek = @T_FIN_DATA-IDNRK 
AND a~atwrt IN @r_item_cat.

[walkerist]

shantraj thanks, however I have encountered this error: Lengths specified in combination with type "OBJEK" are ignored.

[Sandra_Rossi]

FOR ALL ENTRIES, but it has drawbacks, there were some discussions in the forum and blog posts about how to get rid of FOR ALL ENTRIES, by using GTT or AMDP.

IN can also have drawbacks like short dump due to SQL maximum length and others.