CODEWITHSUNDEEP
abapopensql
mmgro27
mmgro27

Reputation: 515

Inner join on fields with different domains

I have a query which looks a bit like this:

 SELECT a~matnr AS material,
        b~aedat AS date,
        SUM( c~ormng ) AS dummy
    INTO TABLE @gt_dummy
    FROM ekpo AS a
    INNER JOIN ekko AS b
      ON b~ebeln = a~ebeln
    INNER JOIN lips AS c
      ON c~vgbel = a~ebeln
      AND c~vgpos = a~ebelp
    INNER JOIN likp AS d
      ON d~vbeln = c~vbeln
    WHERE a~matnr IN @gr_dummy1
      AND a~werks IN @gr_dummy2
    GROUP BY a~matnr, b~aedat
    ORDER BY a~matnr, b~aedat.

It's not going to work because LIPS-VGPOS and EKPO-EBELP have different domains so '00010' in EBELP would be '000010' in VGPOS. An alternative to this would be to just get the EKPO data, use a conversion exit function and then use a FOR ALL ENTRIES query to get the LIPS entries. Then since you can't use SUM and GROUP BY with FOR ALL ENTRIES I would need to do the summations manually.

Of course it's not a huge amount of work to do all this but I'm interested if there's a quicker way to do this e.g. in a single query? Thanks.

EDIT: We're on 7.40

Upvotes: 1

Views: 5453

Answers (3)

András
András

Reputation: 1400

If you have at least ehp5 on 7.40, you can use CDS views in a workaround for FOR ALL ENTRIES with SUM.

  1. Join EKKO and EKPO in OpenSQL
  2. Create a CDS view on LIPS using fields VGBEL, VGPOS, SUM(ORMNG), with GROUP BY on the first two
  3. Call this CDS view with FOR ALL ENTRIES

Upvotes: 0

Sandra Rossi
Sandra Rossi

Reputation: 13656

Unfortunately, I only see two possibilities before ABAP 7.50:

  • FOR ALL ENTRIES as you suggested
  • Execute "native" SQL directly on the database connected to your ABAP software. This can be done with EXEC SQL or ADBC (class CL_SQL_STATEMENT and so on), or AMDP if your database is HANA.

Upvotes: 2

Sandra Rossi
Sandra Rossi

Reputation: 13656

It's not your version but for ABAP >= 7.50, there are SQL string functions LIKE for instance SUBSTRING:

 SELECT a~ebeln, a~ebelp, c~vbeln, c~posnr
    FROM ekpo AS a
    INNER JOIN lips AS c
      ON c~vgbel = a~ebeln
      AND substring( c~vgpos, 2, 5 ) = a~ebelp
    INTO TABLE @DATA(gt_dummy).

Upvotes: 1

Related Questions