Select all fields from table A but single field from B?

Question

Is there a way in ABAP's OpenSQL to simplify the select columns in a JOIN when I want to grab all the fields of one table but only selected fields from the other table(s)?

For instance, in mysql we can simply do:

SELECT  tb1.*, tb2.b, tb2.d
FROM       tableA tb1
INNER JOIN tableB tb2 ON tb1.x = tb2.a

However, OpenSQL does not seem to allow selecting tb1~*, tb2~b, tb2~d so I have to resort to this:

SELECT  tb1.x, tb1.y, tb1.z, tb2.b, tb2.d
FROM       tableA tb1
INNER JOIN tableB tb2 ON tb1.x = tb2.a

For very large tables, especially standard tables, this becomes unwieldy, difficult to read and more annoying to maintain.

Is there a better way to select all fields of tb1 and some fields from tb2?

Lorislorita

Answer 1

Yes, this is possible in the OpenSQL from 7.40 SP08. See this article.

The quotation from the article has that.

Column Specification

In the SELECT list, you can specify all columns of a data source using the syntax data_source~* from 7.40, SP08 on. This can be handy when working with joins.

SELECT scarr~carrname, spfli~*, scarr~url
       FROM scarr INNER JOIN spfli ON scarr~carrid = spfli~carrid
       INTO TABLE @DATA(result).

In the previous versions unfortunately one has to specify the columns one by one or use database native SQL for example with ADBC.

Raceway

Ha, I did think that I'd already used this before. Turns out this particular system is on 7.40 SP06. – Lorislorita

Yep, know the pain. I am on the same system version 7.40 SP06. :) – Raceway