topic Select statement using all fields in Application Development and Automation Discussions

Select statement using all fields

former_member201275 — Tue, 15 Sep 2020 12:21:37 GMT

I want to do a select on a database table for all fields of my work area. Is there a shorter way to do this i.e. I don't want to specify all 30 fields in the select?

All I want to do is check if the record exists in my DB table or not.

For example, I have:

  LOOP AT g_table INTO wa_line.

    SELECT SINGLE * FROM zmydb_tab

    WHERE field1 = wa_line-field1

    AND   field2 = wa_line-field2

    ".... etc ... until

    AND   field30 = wa_line-field30.

  ENDLOOP.

Re: Select statement using all fields

maheshpalavalli — Tue, 15 Sep 2020 12:32:35 GMT

a dynamic where condition should simplify your query. loop those fields and create a dynamic where and pass it to the select where condition,

eg:

https://answers.sap.com/questions/8136030/dynamic-where-condition-in-select-statement.html

Update: one more suggestion, try for all entries, so all the data will be selected in one go, then loop and check if the record exists. for creating dynamic for all entries:

https://answers.sap.com/questions/8342724/dynamic-select-with-for-all-entries-in.html

Regards,

Mahesh

Re: Select statement using all fields

michael_piesche — Tue, 15 Sep 2020 13:03:35 GMT

You would have been faster to write those thirty lines instead of posting a question and waiting for an answer.

In order to get the best performance, you will have to at least use all or parts of the key fields. Please keep in mind, that the less lines of coding does definitly not always mean better performance.

" e.g.:
" 1) select records from zmydb_tab by primary keys 
" for all entries in g_table into a second internal table (e.g. db_table) 
" 2) and then do loop on g_table with an index search on the new db_table 
" and if a record is found, also compare both structures (without addressing a field directly)

So I hope you do care about bad performance caused by a SELECT ALL ENDSELECT that is nested in a LOOP statement (yucky, yuck, yuck - I wrote that version first, but couldnt leave that there).

For the following coding, I assume the structure of g_table and zmydb_tab are identical.

DATA db_table TYPE SORTED TABLE OF zmydb WITH UNIQUE KEYS key1 key2.

SELECT * FROM zmydb_tab INTO TABLE db_table FOR ALL ENTRIES IN g_table
         WHERE key1 = g_table-key1
           AND key2 = g_table-key2.

LOOP AT g_table INTO wa_line.   
  " make sure, that if wa_line has a client attribute that is empty, to also fill it accordingly
  READ TABLE db_table WITH TABLE KEY key1 = wa_line-key1 key2 = wa_line-key2.
  IF sy-subrc = 0 AND wa_line = db_line.
    " you found a perfect match, what do you want to do?
  ELSEIF sy-subrc = 0.
    " you found a match based on a key, but with different non-key values, what to do?
  ELSE.
    " you found no match, what do you want to do?
  ENDIF.
ENDLOOP.

On a serious note: I would strongly recommend that you rework your requirements and the current coding you are wanting to put in place. Even though I dont know your entire requirements yet, your approach just doesnt feel right.

Re: Select statement using all fields

michael_piesche — Tue, 15 Sep 2020 13:17:27 GMT

gemini.twin, I hope you did not accept my answer when I was still about to present a bad coding example.

But if indeed so, here it is. It will work. But it is bad, really bad 😉

LOOP AT g_table INTO wa_line. " make sure, that if wa_line has a client attribute that is empty, to also fill it accordingly
  SELECT * FROM zmydb_tab INTO db_line.
    IF wa_line = db_line. 
      " you found a match, what do you want to do?
      EXIT.
    ENDIF.
  ENDSELECT.
  IF wa_line <> db_line. " no match found
    " you found no match, what do you want to do?
  ENDIF.
ENDLOOP.

Re: Select statement using all fields

Sandra_Rossi — Tue, 15 Sep 2020 17:23:30 GMT

No need to show a so bad example, sometimes people take the code without reading the text accompanying it ! 😉

Re: Select statement using all fields

former_member201275 — Wed, 16 Sep 2020 16:15:35 GMT

Hi Michael,

I actually took your advice at the end of your comment i.e. 'I would strongly recommend that you rework your requirement'. I changed the way the table was defined, to include a key and then it is easier to access.

Thank you kindly for your help!