Application Development Discussions
Join the discussions or start your own on all things application development, including tools and APIs, programming models, and keeping your skills sharp.
cancel
Showing results for 
Search instead for 
Did you mean: 

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

walkerist
Participant
0 Kudos

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>

10 REPLIES 10

Tomas_Buryanek
Active Contributor

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

0 Kudos

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.

0 Kudos

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.

0 Kudos

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

0 Kudos

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
Explorer
0 Kudos
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.

0 Kudos

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

0 Kudos

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.

0 Kudos

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

Sandra_Rossi
Active Contributor
0 Kudos

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.