An Update On Opensql
Note: All these things require ABAP 7.40 SP05 or higher!
The new OpenSQL implements stuff, web developers already know as S-Q-L, but anyway… With these things finally available, we can do all the neat data processings. Such fun. Much wow. Trust me, it’s true.
Don’t fear the new syntax
As my colleagues have noticed that I am using
@-signs in my SQL statements and commas in the field list, they all got very worried. Yeah, it looks strange at first glance but once you know what it does…
Short answer: nothing
There really is no special functionality behind. You just have to prefix every program-intern variable with an
at, so the query parser doesn’t get confused about its own data. Using commas to separate the selected columns already is common practice in nearly every other structured query language.
Making use of declaration operators
Declaring structures by hand is lame, time-consuming and sooooo unnecessary. Everything is there. Compiler? How about just take it by yourself?!
This is just a very basic code snippet that fetches customers from company code 3000 with their names before 7.4:
Now look at that one. Beautiful.
But what if we want to move the value of
fullname instead of
name1? We would have to declare another column named
name1 and extend
INTO TABLE with
CORRESPONDING FIELDS OF. That’s too much for me. We just do it this way:
You see: one
AS is enough to nickname a field.
More precise selection
The only thing I want to mention here is that you can now select
* from a specific table instead of just ALL.
Little guessing game: What could be the output of above code?
- All fields from table1
- All fields from table2
- All fields from all tables
Answer: “ERROR: Too many fields specified” because all fields are specified even though nobody wants to select everything from every table… Now:
I think there should be no doubt about which one is better, right?
Computing results wasn’t really possible in the old OpenSQL. I can’t even find any computation in my code at all. Here are only a few snippets of the new possibilities.
Calling inbuilt functions…
Casting (pretty useless atm as only float and dec are allowed)…
We can finally select stuff based on a condition inside queries 🎉. There are two possible forms:
- Like `COND` or `IF ... ELSEIF ... ELSE`:
- Like `CASE` or `SWITCH`:
You are free to replace … with a type compatible column or another SQL expression.
Let’s get you creative!
If you ever wondered how to correctly (in terms of debit and credit) sum up
BSID without any looping - there you go:
Wanna join names without
CONCATENATE? What do you think about this?