topic Re: Select Query not returning entries in Application Development and Automation Discussions

Select Query not returning entries

former_member801239 — Mon, 16 Jan 2023 10:19:37 GMT

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.

Re: Select Query not returning entries

sandeep_suggu — Mon, 16 Jan 2023 10:43:22 GMT

Hi spsb,

Did you debug the program

Re: Select Query not returning entries

Sandra_Rossi — Mon, 16 Jan 2023 10:46:48 GMT

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!

Re: Select Query not returning entries

dominik_jacobs — Mon, 16 Jan 2023 11:21:29 GMT

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

Re: Select Query not returning entries

former_member801239 — Mon, 16 Jan 2023 11:57:40 GMT

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.

Re: Select Query not returning entries

RaymondGiuseppi — Mon, 16 Jan 2023 12:03:47 GMT

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)

Re: Select Query not returning entries

Sandra_Rossi — Mon, 16 Jan 2023 12:10:34 GMT

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.

Re: Select Query not returning entries

former_member801239 — Mon, 16 Jan 2023 15:14:06 GMT

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

Re: Select Query not returning entries

former_member801239 — Mon, 16 Jan 2023 15:19:10 GMT

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

Re: Select Query not returning entries

Sandra_Rossi — Mon, 16 Jan 2023 16:23:58 GMT

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?"

Re: Select Query not returning entries

former_member801239 — Tue, 17 Jan 2023 12:16:29 GMT

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..

Re: Select Query not returning entries

former_member801239 — Tue, 17 Jan 2023 12:29:32 GMT

Sandra Rossi see above

Re: Select Query not returning entries

matt — Tue, 17 Jan 2023 12:59:57 GMT

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

Re: Select Query not returning entries

former_member801239 — Tue, 17 Jan 2023 13:03:20 GMT

yes, I realised after posting but can't edit

Re: Select Query not returning entries

Sandra_Rossi — Tue, 17 Jan 2023 13:03:25 GMT

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.

Re: Select Query not returning entries

former_member801239 — Tue, 17 Jan 2023 13:35:01 GMT

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.

Re: Select Query not returning entries

Sandra_Rossi — Tue, 17 Jan 2023 13:51:20 GMT

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.

Re: Select Query not returning entries

former_member801239 — Tue, 17 Jan 2023 13:53:47 GMT

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

Re: Select Query not returning entries

Sandra_Rossi — Tue, 17 Jan 2023 14:37:02 GMT

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

Re: Select Query not returning entries

former_member801239 — Fri, 20 Jan 2023 13:51:08 GMT

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