Getting empty result with SELECT ... FOR ALL ENTRIES

Question

I have the following problem.

I need to write a script that reads a specific file, from the presentation layer, containing one numerical string per line, the contents of this file need to be entered into an internal table, and then the internal table needs to be inner joined, with table dfkkop to produce the equivalent lines that correspond to each numerical string in the internal table.

I recently read that a conventional inner join cannot be done between an internal table ("itab") and a database table, and instead I should use FOR ALL ENTRIES IN. The problem is that it doesn't seem to work. Here's the code:

TYPES:
  BEGIN OF type_dfkkop_vkont,
    vkont TYPE dfkkop-vkont,
  END OF type_dfkkop_vkont.

DATA: gt_dfkkop_file TYPE STANDARD TABLE OF type_dfkkop_vkont,
      wa_gt_dfkkop_file LIKE LINE OF gt_dfkkop_file,
      gt_dfkkop LIKE DFKKOP OCCURS 0 WITH HEADER LINE,
      wa_gt_dfkkop LIKE dfkkop,
DATA: gv_filename TYPE string,
      gv_filetype TYPE char10.

START-OF-SELECTION.
  PERFORM read_file.
  PERFORM process_data.


FORM read_file.
  gv_filename = 'C:\Users\p.doulgeridis\Desktop\data.txt'.

  CALL FUNCTION 'GUI_UPLOAD'
    EXPORTING
      filename            = gv_filename
      filetype            = 'ASC'
      has_field_separator = 'X'
    TABLES
     data_tab            = gt_dfkkop_file.

ENDFORM.

FORM process_data.

  SELECT *
    FROM dfkkop
    INTO CORRESPONDING FIELDS OF TABLE gt_dfkkop
    FOR ALL ENTRIES IN gt_dfkkop_file
    WHERE vkont = gt_dfkkop_file-vkont.

  IF gt_dfkkop_file IS NOT INITIAL.
    WRITE: / 'TABLE GT_DFKKOP_FILE IS NOT INITIAL'.
  ELSE.
    WRITE: / 'TABLE GT_DFKKOP_FILE IS INITIAL'.
  ENDIF.

  IF gt_dfkkop IS NOT INITIAL.
    WRITE: / 'TABLE GT_DFKKOP IS NOT INITIAL'.
  ELSE.
    WRITE: / 'TABLE GT_DFKKOP IS INITIAL'.
  ENDIF.

  LOOP AT gt_dfkkop_file INTO wa_gt_dfkkop_file.
    WRITE: / wa_gt_dfkkop_file-vkont.
  ENDLOOP.

  LOOP AT gt_dfkkop INTO wa_gt_dfkkop.
    WRITE: / wa_gt_dfkkop-vkont, wa_gt_dfkkop-opbel, wa_gt_dfkkop-OPUPW.
  ENDLOOP.

ENDFORM.

At the moment, I do not receive a syntax error, the program executes, but produces no output whatsoever. Could someone help me?

Edit 1: The output looks like this, it's like it doesn't find the values in the text file to query with, keep in mind the file I'm reading has around 11 lines worth of values, which already exist in table dfkkop so they should be producing a result, instead I get:

[blank11spaces]             4000000187   000

Exact output:

This question has been changed several times by the OP, so there are different answers, one for each fluctuating situation. Here is the summary: 1) The original code was using the wrong internal table after `FOR ALL ENTRIES IN` -> answers by Sergio and szako 2) The original code was fixed still no output -> comment by szako under his answer to say that "DFKKOP-VKONT field has alpha conversion" (the column is 12 characters in database and its values contain 2 leading zeroes, but ABAP doesn't display them) -> Ray posted it as an answer — Sandra Rossi, Sep 18 '21 at 06:57

score 4 · Accepted Answer · answered Jun 23 '17 at 11:11

4

In the for all entries part you have to refer to the internal table containing the key entries. Forget ENDSELECT also because you can move all entries into the internal table at once.

The proper syntax would be:

  SELECT *
    FROM dfkkop
    INTO CORRESPONDING FIELDS OF TABLE gt_dfkkop
    FOR ALL ENTRIES IN gt_dfkkop_file
    WHERE vkont = gt_dfkkop_file-vkont.

More about the command here. Btw, the most important part of this command is the following, keep this in mind:

Before using an internal table itab after FOR ALL ENTRIES, always check that the internal table is not initial. In an initial internal tables, all rows are read from the database regardless of any further conditions specified after WHERE. This is not usually the required behavior.

answered Jun 23 '17 at 11:11

szako

1,271
1
9
12

The above doesnt give a syntax error, but doesnt output the correct result, i ve editted my original question to reflect this, please help – onlyf Jun 23 '17 at 11:26
i added the check you suggested, and it returned that the table is initial, how do i resolve that? – onlyf Jun 23 '17 at 11:49
4

The DFKKOP-VKONT field has alpha conversion. As I see the ids you have are 10 chars length. In the file do you have leading zeros? If not it will never find the corresponding records. Use input alpha-conversion method for the input values. – szako Jun 23 '17 at 12:09
Did you succeed? – szako Jun 27 '17 at 04:38

score 2 · Answer 2 · answered Jun 23 '17 at 11:32

2

The problem in your select is that you are using "FOR ALL ENTRIES IN gt_dfkkop" instead of "FOR ALL ENTRIES IN gt_dfkkop_file", besides, you can use INTO instead of SELECT - ENDSELECT, that will lead to the query szako has done.

If you deal with large volumes of data and you expect to find many contract account (VKONT) duplicates in gt_dfkkop_file you can increase performance moving vkont to another temporal table and use it instead of gt_dfkkop_file. Check allways that the table you use is not empty.

answered Jun 23 '17 at 11:32

Sergio Prats

1,043
1
14
19

Thanks for your quick answer, if you check my current code, you ll see that i have incorporated your suggestion (the "FOR ALL ENTRIES") one, and the output is still erroneous. – onlyf Jun 23 '17 at 11:34
Can you check if there is any record in DFKKOP with VKONT = 4000000187. If there is no record with that contract account, that's the reason why you are not getting anything – Sergio Prats Jun 23 '17 at 12:21

score 2 · Answer 3 · answered Jun 26 '17 at 14:56

2

szako seems to have answered this in the comment above:

The DFKKOP-VKONT field has alpha conversion. As I see the ids you have are 10 chars length. In the file do you have leading zeros? If not it will never find the corresponding records. Use input alpha-conversion method for the input values. – szako Jun 23 at 12:09

answered Jun 26 '17 at 14:56

Ray Mannion

19
3

The original question was different. szako has answered the original question, and the comment was posted to reflect the fixed question. That demonstrates well why questions must not be changed. About the fixed question, the column DFKKOP-VKONT is **12 characters long** in database. The values in the database table start with zeroes, for instance `003000054748`. I guess the OP has been mistaken because if the database table is displayed in SAP GUI, the leading zeroes are removed. He should enter values like `003000054748` in the input file, and it will work. – Sandra Rossi Sep 18 '21 at 07:02

Getting empty result with SELECT ... FOR ALL ENTRIES

3 Answers3