Select Query not returning entries

[former_member801239]

I wish to write a SELECT query to find valid materials in a text file and then show them in a new page. Can someone help to explain why this does not work? f_mvke is the file name and t_mvke is the temporary table.

if f_mvke is not initial.
loop at t_mvke.
select single * from MARA WHERE matnr = t_mvke-matnr.
if sy-subrc = 0.
select single * from MVKE WHERE matnr = t_mvke-matnr AND vkorg = t_mvke-vkorg AND vtweg = '11'.
if sy-subrc = 0.
select single * from MAKT WHERE matnr = t_mvke-matnr AND spras = 'D' AND maktx = t_mvke-maktx.
endif.
skip 1.
write: / 'Verkaufs Org' ,20 t_mvke-vkorg.
write: / 'Material Nummer' ,20 t_mvke-matnr.
write: / 'Material Text' ,20 t_mvke-maktx.
ok_count = ok_count + 1.
endif.
endloop.
endif.

[former_member801239]

I wish to write a SELECT query to find valid materials in a text file and then show them in a new page. Can someone help to explain why this does not work? f_mvke is the file name and t_mvke is the temporary table.

if f_mvke is not initial.
loop at t_mvke.
select single * from MARA WHERE matnr = t_mvke-matnr.
if sy-subrc = 0.
select single * from MVKE WHERE matnr = t_mvke-matnr AND vkorg = t_mvke-vkorg AND vtweg = '11'.
if sy-subrc = 0.
select single * from MAKT WHERE matnr = t_mvke-matnr AND spras = 'D' AND maktx = t_mvke-maktx.
endif.
skip 1.
write: / 'Verkaufs Org' ,20 t_mvke-vkorg.
write: / 'Material Nummer' ,20 t_mvke-matnr.
write: / 'Material Text' ,20 t_mvke-maktx.
ok_count = ok_count + 1.
endif.
endloop.
endif.

[sandeep_suggu]

Hi spsb,

Did you debug the program

[Sandra_Rossi]

Please edit your question, select your code and press the button [CODE], which makes the code appear colored/indented, it will be easier for people to look at it. Thank you!

[dominik_jacobs]

Here is an optimized version of the code you provided (it works for me and returns entrys)

if f_mvke is not initial.
  loop at t_mvke.
    select single matnr, vkorg, vtweg, maktx from MARA JOIN MVKE ON MARA.matnr = MVKE.matnr JOIN MAKT ON MARA.matnr = MAKT.matnr 
    WHERE t_mvke-matnr = MARA.matnr AND t_mvke-vkorg = MVKE.vkorg AND MVKE.vtweg = '11' AND t_mvke-matnr = MAKT.matnr AND MAKT.spras = 'D' AND t_mvke-maktx = MAKT.maktx.
    if sy-subrc = 0.
      write: / 'Verkaufs Org' ,20 t_mvke-vkorg.
      write: / 'Material Nummer' ,20 t_mvke-matnr.
      write: / 'Material Text' ,20 t_mvke-maktx.
      ok_count = ok_count + 1.
    endif.
  endloop.
endif.

In this optimized version of the code, I have combined the three SELECT statements into one single statement using JOIN clauses. This way, you only need to make one trip to the database instead of three, which will improve the performance of your code. Also, I've removed the skip 1 statement, as it is not needed.
BR

Dom

[former_member801239]

Many. many thanks Dominik, but I am still using the old syntax. What do I need to change here to remove the ambiguous /zweideutig syntaxfehler?

if f_mvke is not initial.
loop at t_mvke.
select single matnr, vkorg, vtweg, maktx from MARA
JOIN MVKE ON @MARA-matnr = @MVKE-matnr
JOIN MAKT ON @MARA-matnr = @MAKT-matnr WHERE @t_mvke-matnr = MARA-matnr AND @t_mvke-vkorg = MVKE-vkorg AND MVKE-vtweg = '11' AND
@t_mvke-matnr = MAKT-matnr AND MAKT-spras = 'D' AND @t_mvke-maktx = MAKT-maktx.

if sy-subrc = 0.
write: / 'Verkaufs Org' ,20 t_mvke-vkorg.
write: / 'Material Nummer' ,20 t_mvke-matnr.
write: / 'Material Text' ,20 t_mvke-maktx.
ok_count = ok_count + 1.
endif.
endloop.
endif.

[Sandra_Rossi]

It doesn't compile.

spsb Header lines have been obsolete for a very long time, it doesn't compile inside a class, so the syntax "loop at t_mvke" should be replaced with "loop at t_mvke assigning ..." or "loop at t_mvke reference ...", as the OP prefers.

Also, I would highly recommend to not use SELECT inside a loop (loop at t_mvke), so possibly joining with table MVKE. To be left to the responsibility of the OP as we don't know the rest of the code.

You are using a newest syntax than Dominik, you don't use the "old syntax". The variables must be on the right side of the conditions.

[former_member801239]

Hello Sandra

I tried to modify his code to compile at my end so used this newer syntax but to no avail. Where am I going wrong here?

if f_mvke is not initial.
  loop at t_mvke into st_mat.
    select single matnr, vkorg, vtweg, maktx from MARA JOIN MVKE ON MARA-matnr = MVKE-matnr JOIN MAKT ON MARA-matnr = MAKT-matnr 
    WHERE t_mvke-matnr = MARA-matnr AND t_mvke-vkorg = MVKE-vkorg AND MVKE-vtweg = '11' AND t_mvke-matnr = MAKT-matnr AND MAKT-spras = 'D' AND t_mvke-maktx = MAKT-maktx.
    if sy-subrc = 0.
      write: / 'Verkaufs Org' ,20 st_mat-vkorg.
      write: / 'Material Nummer' ,20 st_mat-matnr.
      write: / 'Material Text' ,20 st_mat-maktx.
      ok_count = ok_count + 1.
    endif.
  endloop.
endif.

Many thanks in advance

[Sandra_Rossi]

spsb Please don't Accept an answer which doesn't work, that won't help future visitors if the answer doesn't work. I didn't answer because I was waiting for your answer to the question by sandeepsuggu, "Did you debug the program?"

[former_member801239]

Sandra Rossi see above

[RaymondGiuseppi]

Did you convert the text file format of material number into internal format before trying to use it in a SELECT statement (Check; SE11, domain MATNR for conversion-exit, should be ALPHA, MATN1 or similar, so use a FM such as CONVERSION_EXIT_ALPHA_INPUT for ALPHA to convert external value to internal value)

• Add an INTO clause to put values in your original internal table
• Use a SELECT with JOIN options
• Any reason to use 'DE' and not sy-langu or 'D' (there is also a conversion-exit on language fields)

[former_member801239]

There is a method doing this for the Material when it is being read in. Best wishes S

[former_member801239]

I created the JOIN and a view as a TYPE TABLE. Thanks for the tip.

[former_member801239]

if f_mvke is not initial.

loop at t_mvke .
select single * from MARA WHERE not matnr = t_mvke-MATNR.
if sy-subrc <> 0.
select single * from MVKE WHERE not matnr = t_mvke-matnr AND vkorg = t_mvke-vkorg AND vtweg = '11'.
if sy-subrc = 0.
select single * from MAKT WHERE not matnr = t_mvke-matnr AND spras = sy-langu AND maktx = t_mvke-maktx.
if sy-subrc = 0.
st_mat-vkorg = t_mvke-vkorg.
st_mat-matnr = t_mvke-matnr.
st_mat-maktx = t_mvke-maktx.
endif.
endif.
else.
skip 1.

write: / 'Verkaufs Org' ,20 st_mat-vkorg.
write: / 'Material Nummer' ,20 st_mat-matnr.
write: / 'Material Text' ,20 st_mat-maktx.
endif.
endloop.
endif.

The 3 tables are not being read for some reason..

[matt]

You need to select the code bit of your post, and then click on code.

[former_member801239]

yes, I realised after posting but can't edit

[Sandra_Rossi]

It seems that you are not familiar with the debugging. The kind of answer Sandeep (I think) and I are expecting, is for these kind of questions:

• does T_MVKE contain anything?
• what is sy-subrc after select single * from MARA?
• If it's 4 (not found), could you show the value t_mvke-matnr from the debugger and the corresponding line which exists in the table?
• etc.

[former_member801239]

Yes, t_mvke is correctly filled. I am getting sy-subrc = 4 in method CL_BADI_FLT_DATA_TRANS_AND_DB=CP.

 LOOP AT filt_imps INTO filter1.

    CLEAR devclass.
    CALL FUNCTION 'SXE_IMPL_RELEASED_FOR_CUSTOMER'
      EXPORTING
        imp_name    = filter1-imp_name
        exit_name   = exit_name
        internal    = internal
      IMPORTING
        is_released = is_released
        devclass    = devclass.

[Sandra_Rossi]

I hope you understand that your last comment doesn't bring light to the questions I asked except the first one. Nobody can understand your comment about sy-subrc = 4 and CL_BADI_FLT_DATA_TRANS_AND_DB=CP because it's not related to your question in any way.

PS: yes, unfortunately, it's no more possible to edit a comment attached to the question after someone has posted a first answer.

[former_member801239]

As I said, when I debug then I get an error in this place.

[Sandra_Rossi]

Sorry about that, I guess you are debugging at the wrong place. I can't help more. Good luck!