When you’re going to modify existing records of a database table it is common to first SELECT them to see how they are and only then UPDATE them with what they will be.

I recently found a program which was generating an SQL statement by concatenating several variables with fixed SQL parts. But, either by not paying attention or simple ignorance, the person who did it, thought it would make sense to associate a text-symbol to each of these variables with. Something like this:

[Guest post by Artur Moreira] From ABAP version 7.4, SAP introduced the code pushdown concept, which means using more database for calculations (data aggregation, sums and previous calculations).

Sometimes ABAP SQL doesn’t allow you to do something you’d be able to do using the database’s native SQL. It can still be done. * Converte para maiúsculas e acrescenta wildcard TRANSLATE l_name1 TO UPPER CASE. CONCATENATE l_name1 '%' INTO l_name1. * Executa SQL nativo para fazer * uma pesquisa "case insensitive" pelo nome EXEC sql performing SAVE_ROW. SELECT kunnr INTO :l_kuune FROM kna1 WHERE kna1.mandt = :sy-mandt AND UPPER(kna1.name1) LIKE :l_name1 ENDEXEC.

Toad is a very famous SQL tool which allows you to build and execute SQL queries in an easy and interactive way. And now, thanks to Mr. Sébastien HERMANN, SAP has a simplified version of this wonderful tool. I’ll let him explain. Thank you Sérgio Fraga for spotting this. Greetings from Abapinho.

Someone asks you to select records based on a criteria for which you’ll have a small set of known values which you’re told will never change. Did you know you can do it directly in the SELECT? SELECT * INTO T_T005T FROM T005T WHERE LAND1 = I_LAND1 AND SPRAS IN ( 'PT', EN' ). I need to remind you that hard coding values in programs is not the best idea so make sure you know what you’re doing when you choose to apply this approach.

Get ready because you’ll soon be running into a lot of surprises. ABAP is learning new tricks. Look at this one: CONSTANTS: lc_menina TYPE STRING VALUE ‘GIRL', lc_menino TYPE STRING VALUE ‘BOY’, lc_senhor TYPE STRING VALUE ’GENTLEMAN’, lc_senhora TYPE STRING VALUE ‘LADY’. SELECT nome, CASE WHEN sexo_id = ‘M' AND idade < 18 THEN @lc_menino WHEN sexo_id = ‘F’ AND idade < 18 THEN @lc_menina WHEN sexo_id = ‘M' AND idade >=18 THEN @lc_senhor WHEN sexo_id = ‘F’ AND idade >=18 THEN @lc_senhora END AS titulo FROM zpessoa WHERE pessoa_id = @pessoa_id INTO CORRESPONDING FIELDS OF @lt_pessoas.

Since data operations are much more optimized in the database server than in ABAP, it is always better to use the first as much as possible. FOR ALL ENTRIES should only be used when INNER JOIN doesn’t give us what we need or is not possible (like with BSEG for example). Artificial ranges are also a possible alternative to FOR ALL ENTRIES but be careful not to reach the SQL parser limit.

Always try to select only the fields you’ll need. Selecting others is a waste of resources. Exception made for the use of FM *_SINGLE_READ because, even though these do select all fields, since they cache the data, they are still faster when used multiple times for the same key. If you just want to check if a record exists, select just one field, if possible the one you’re using as criteria to avoid declaring an extra variable.

ABAP programmers don’t explore the possibilities of SQL, probably for historical reasons. There are many who instead of using INNER JOINs still think it’s faster to do several SELECTs for internal tables and then process the data in ABAP. But the truth is that even if there are exceptions, the rule is: the lower the number of accesses to the database, the better the performance. And it makes sense because, after all, they were written explicitly for this; relational databases are much more adept at processing relational data than an ABAP program. There are of course things that, due to their complexity, cannot be done with a simple INNER JOIN. Nevertheless, some of these things can be done in a single SELECT.

Although I have been using ABAP since 1998, I learn new features or options every week, and sometimes even new commands. It’s hard to believe. Today, I’ll share a little SQL option that I recently discovered. SELECT * FROM T001 INTO TABLE T_T001 ORDER BY PRIMARY KEY. I have known SELECT, FROM, INTO, TABLE and even ORDER BY for a long time. I just didn’t know of PRIMARY KEY. It orders the internal table by its key.

This tip is simple and quick, but I bet 200.482 Portuguese escudos that few know it.

The dictionary has the following entry for “style”: “set of formal aspects and expressive resources that characterise a text.” It is preferable to read a text with style than one without. If ABAP were a language, a program in ABAP would be a text. When it comes to style, there are programs that seem to have been written by the feet, while there are others that one would swear the quill that wrote them was guided by the pure hand of an eighteenth century lady plagued by the troubles of love. Fortunately, I think it’s not possible to write programs in ABAP as tacky as the last sentence. The “style” label is used here in Abapinho to identify articles that talk about just that: style. These articles try to find ways to improve the style of ABAP programs. This is one of Abapinho’s favourite labels, as you can see here. And what you are reading is another of those articles. Moving on.