"
Etiqueta > SQL
Supported by

Classe para garantir segurança em programação dinâmica

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:

Ler o resto do artigo! »

ABAP Code PushDown in HANA

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

Ler o resto do artigo! »

Native SQL

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.

But pay attention because, unlike the generic ABAP SQL, this SQL will have to be specifically written for the existing database. As a consequence, the code will be less flexible and more complex. So make sure you use this only if you really have no alternativa.

Photo: Photodesaster via VisualHunt.com / CC BY-NC

Greetings from Abapinho.

ZTOAD – Editor de SQL

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.

SELECT from a set of known values

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.

Thank you Sérgio Fraga for the tip.

Greetings from Abapinho.

CASE inside a SELECT (available soon)

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.

You just have to wait for version 7.40.

Thank you Sérgio Fraga for the tip.

Thank you Anil Wadghule for the photo.

Greetings from Abapinho.

Best practices
INNER JOIN vs FOR ALL ENTRIES vs artificial RANGES

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. It depends on the database server but as a rule of thumb avoid ranges with more than 1000 lines.
When using FOR ALL ENTRIES always make sure the internal table is not empty or else all lines of the database table will be fetched.

Best practices
Thou shalt not SELECT *

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. Example: SELECT KUNNR INTO V_KUNNR FROM KNA1 WHERE KUNNR = V_KUNNR.
https://abapinho.com/2010/11/select-todos-os-campos/ (portuguese)

SELECT within SELECT

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.

Ler o resto do artigo! »

SELECT… …ORDER BY PRIMARY KEY

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

Greetings from Abapinho.

SELECT comparing 2 fields of the same table

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

Ler o resto do artigo! »

More RANGEs, less SELECTs

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.

Ler o resto do artigo! »


About Abapinho
Abapinho runs on WordPress
Articles (RSS) e Comments (RSS).