topic Re: SQL Join Fields of Different Length in Application Development and Automation Discussions

SQL Join Fields of Different Length

Former Member — Fri, 25 Sep 2009 06:38:30 GMT

I have encountered a problem trying to join fields of different lencth in an SQL statement. The "value" of the fields are the same (10), however, they are stored as CHAR and as such the leading zeros are resulting in mismatch.

The fields in question are:

FRET-BPOSB length = 6 (e.g. 000010)

EKPO-EBELP length = 5 (e.g. 00010)

What is the most efficient approach to create a join using these fields (or rather the most basic approach considering I am a novice APAPer)?

Here is a simple program I have written to test the join:

REPORT  Z_DOWNLOAD_XDOCK.

*Define internal table type (ty_xdock)
TYPES : BEGIN OF ty_xdock,
          PO      TYPE FRET-BLNRB,
          POITEM  TYPE FRET-BPOSB,
          STO      TYPE EKPO-EBELN,
          STOITEM  TYPE EKPO-EBELP,
        END OF ty_xdock.

*Create internal table (it_xdock)
DATA it_xdock TYPE STANDARD TABLE OF ty_xdock WITH HEADER LINE.

PARAMETERS PONO LIKE FRET-BLNRB OBLIGATORY.

*Select Data
SELECT FRET~BLNRB
       FRET~BPOSB
       EKPO~EBELN
       EKPO~EBELP
  INTO TABLE it_xdock
  FROM FRET
       INNER JOIN EKPO
       ON  FRET~BLNRB = EKPO~EBELN
       AND FRET~BPOSB = EKPO~EBELP
 WHERE FRET~BLNRB = PONO.

LOOP AT it_xdock.
 WRITE: / it_xdock-PO.
 WRITE:   it_xdock-POITEM.
 WRITE: / it_xdock-STO.
 WRITE:   it_xdock-STOITEM.
ENDLOOP.

Re: SQL Join Fields of Different Length

aabhas_wilmar — Fri, 25 Sep 2009 07:26:04 GMT

Hi there,

I don't think it would be possible to join to different data types having different value (to the system, atleast).

A solution would be to read FRET first and then modify the field in question with a converson exit to match EBELP and then go on selecting record from EKPO.

FOR ALL ENTRIES can also be used.

regards,

Aabhas

Re: SQL Join Fields of Different Length

Former Member — Fri, 25 Sep 2009 07:31:11 GMT

Hi Aabhas,

I suspected that this might need to be done in a couple of passes.

However, being an ABAP novice I have little idea how to code this. Do you have any sample code or links to helpful references?

Thanks for your advice.

Regards,

akaGus

Re: SQL Join Fields of Different Length

Former Member — Fri, 25 Sep 2009 07:32:44 GMT

Hi,

You can do one thing is that:

define a char of length "6" ( say l_bposb(6) type c. ).

and now what you can do is:

concatenate '0' EKPO~EBELP into l_bposb.

now, use the select query as:

*Select Data

SELECT FRET~BLNRB

FRET~BPOSB

EKPO~EBELN

EKPO~EBELP

INTO TABLE it_xdock

FROM FRET

INNER JOIN EKPO

ON FRET_{BLNRB = EKPO}EBELN

AND FRET~BPOSB = l_bposb

WHERE FRET~BLNRB = PONO.

just try this solution and check if it is working fine or not.

Regards,

Vishnu.

Re: SQL Join Fields of Different Length

Former Member — Fri, 25 Sep 2009 07:33:29 GMT

Hi,

You can do one thing is that:

define a char of length "6" ( say l_bposb(6) type c. ).

and now what you can do is:

concatenate '0' EKPO~EBELP into l_bposb.

now, use the select query as:

*Select Data

SELECT FRET~BLNRB

FRET~BPOSB

EKPO~EBELN

EKPO~EBELP

INTO TABLE it_xdock

FROM FRET

INNER JOIN EKPO

ON FRET_{BLNRB = EKPO}EBELN

AND FRET~BPOSB = l_bposb

WHERE FRET~BLNRB = PONO.

just try this solution and check if it is working fine or not.

Regards,

Vishnu.

Re: SQL Join Fields of Different Length

Former Member — Fri, 25 Sep 2009 07:45:30 GMT

Hi Vishnu,

I have tried your suggestion, however, I have not yet obtained any EKPO records - therefore I cannot convert the EBELP field.

Below I have incorporated your code into mine. The problem is the line "concatenate '0' EKPO~EBELP into l_bposb. "

REPORT  Z_DOWNLOAD_XDOCK.
 
*Define internal table type (ty_xdock)
TYPES : BEGIN OF ty_xdock,
          PO      TYPE FRET-BLNRB,
          POITEM  TYPE FRET-BPOSB,
          STO      TYPE EKPO-EBELN,
          STOITEM  TYPE EKPO-EBELP,
        END OF ty_xdock.
 
*Create internal table (it_xdock)
DATA: it_xdock TYPE STANDARD TABLE OF ty_xdock WITH HEADER LINE,
      l_bposb(6) type c.
 
PARAMETERS PONO LIKE FRET-BLNRB OBLIGATORY.
 
concatenate '0' EKPO~EBELP into l_bposb. 

SELECT FRET~BLNRB
       FRET~BPOSB
       EKPO~EBELN
       EKPO~EBELP
  INTO TABLE it_xdock
  FROM FRET
 INNER JOIN EKPO
    ON FRET~BLNRB = EKPO~EBELN
   AND FRET~BPOSB = l_bposb
 WHERE FRET~BLNRB = PONO. 

LOOP AT it_xdock.
 WRITE: / it_xdock-PO.
 WRITE:   it_xdock-POITEM.
 WRITE: / it_xdock-STO.
 WRITE:   it_xdock-STOITEM.
ENDLOOP.

Re: SQL Join Fields of Different Length

Former Member — Fri, 25 Sep 2009 08:21:32 GMT

Hi Akagus,

In fact, previous solutions can not work since EKPO~EBELP is not known when you do the CONCATENATE! So these codes will not work...

The way you should do it, is a bit more complcated, since it requires to break the join and to convert your field in a temporary internal table. You ca do something like this :

SELECT blnrb bposb
  INTO TABLE it_fret
  FROM fret
    WHERE blnrb = pono.

" it_fret has 3 fields : blnrb / bposb / ebelp which contains bposb on 6 characters
LOOP AT it_fret ASSIGNING <fs_fret>.
  <fs_fret>-ebelp = <fs_fret>-bposb.
ENDLOOP.

SELECT ebeln ebelp
  INTO TABLE it_ekpo
  FROM ekpo
  FOR ALL ENTRIES IN it_fret
    WHERE ebeln = it_fret-blnrb AND
          ebelp = it_fret-ebelp.

Best regards,

Samuel

Re: SQL Join Fields of Different Length

Former Member — Fri, 25 Sep 2009 08:28:55 GMT

Hi,

Try like this with two seperate select queries


REPORT  Z_DOWNLOAD_XDOCK.
 
*Define internal table type (ty_xdock)
TYPES : BEGIN OF ty_fret,
          PO         TYPE FRET-BLNRB,
          POITEM  TYPE FRET-BPOSB,
          END OF ty_fret.

TYPES : BEGIN OF ty_xdock,
          PO      TYPE FRET-BLNRB,
          POITEM  TYPE FRET-BPOSB,
          STO      TYPE EKPO-EBELN,
          STOITEM  TYPE EKPO-EBELP,
        END OF ty_xdock.
 
*Create internal table (it_xdock)
DATA: it_xdock TYPE STANDARD TABLE OF ty_xdock WITH HEADER LINE,
           it_fret TYPE STANDARD TABLE OF ty_fret WITH HEADER LINE.
 
PARAMETERS PONO LIKE FRET-BLNRB OBLIGATORY.
 
SELECT BLNRB  BPOSB
into table it_fret 
from FRET
where blnrb = pono.

if sy-subrc = 0.
loop at it_fret.
shift it_fret-poitem left deleting leading '0'.
modify it_fret.
endloop.
endif.

select ebeln ebelp
from ekpo
into corresponding fields of table it_xdock
for all entries in it_fret
where ebeln = it_fret-po and 
    ebelp = it_fret-poitem.

if sy-subrc = 0.

loop at it_xdock.
read table it_fret with key po = it_xdock-sto poitem = stoitem.
if sy-subrc = 0.
move-corresponding it_fret to it_xdock.
modify it_xdock.
endif.
endloop.

 
LOOP AT it_xdock.
 WRITE: / it_xdock-PO.
 WRITE:   it_xdock-POITEM.
 WRITE: / it_xdock-STO.
 WRITE:   it_xdock-STOITEM.
ENDLOOP.

Regards,

Vikranth

Re: SQL Join Fields of Different Length

Former Member — Tue, 29 Sep 2009 07:36:00 GMT

Using a combination of the advice I have received in this thread I have come up with the following solution.

It's not as elegant as I'd like, but it is surprisingly efficient. The code takes less than 5 to 10 seconds to select the required data from a few million records.

There is certainly room for improvement, however, it works.

Thankyou everyone for your advice.

Code removed due to faulty SAP Forum formatting, will try to post again later

Edited by: akagus on Sep 29, 2009 9:50 AM

Re: SQL Join Fields of Different Length

Former Member — Wed, 30 Sep 2009 20:53:48 GMT

Could you please post your solution? I am facing the same problem..

Re: SQL Join Fields of Different Length

Former Member — Wed, 30 Sep 2009 23:01:42 GMT

It appears that all formatting is removed from the code when the post exceeds 2500 characters, therefore, I will post the code in two replies.

Part A:

REPORT  Z_DOWNLOAD_XDOCK.

*Define internal table type (ty_xdock)
TYPES : BEGIN OF ty_xdock,
          PO      TYPE FRET-BLNRB,
          POITEM  TYPE EKPO-EBELP,
          POQTY   TYPE FRET-PMENB,
          DC      TYPE FRET-WERKA,
          ARTNO   TYPE FRET-MATNR,
          EAN     TYPE MEAN-EAN11,
          ARTDES  TYPE MAKT-MAKTX,
          STORE   TYPE FRET-ABNNR,
          STOQTY  TYPE FRET-PMENA,
          STO     TYPE FRET-BLNRA,
          STOITEM TYPE EKPO-EBELP,
        END OF ty_xdock,

        BEGIN OF ty_FRET,
          PO      TYPE FRET-BLNRB,
          POITEM  TYPE EKPO-EBELP,
          STO     TYPE FRET-BLNRA,
          STOITEM TYPE EKPO-EBELP,
        END OF ty_FRET,

        BEGIN OF ty_PO,
          PO      TYPE FRET-BLNRB,
          POITEM  TYPE FRET-BPOSB,
          PODEL   TYPE EKPO-LOEKZ,
          POQTY   TYPE EKPO-MENGE,
          POSITE  TYPE EKPO-WERKS,
          ARTNO   TYPE EKPO-MATNR,
          EAN     TYPE MEAN-EAN11,
          ARTDES  TYPE MAKT-MAKTX,
        END OF ty_PO,

        BEGIN OF ty_STO,
          STO     TYPE FRET-BLNRA,
          STOITEM TYPE FRET-BPOSA,
          STODEL  TYPE EKPO-LOEKZ,
          STOFDI  TYPE EKPO-EGLKZ,
          STODCI  TYPE EKPO-ELIKZ,
          STOQTY  TYPE EKPO-MENGE,
          STOSITE TYPE EKPO-WERKS,
        END OF ty_STO.

*Create internal table (it_FRET)
DATA: it_FRET TYPE STANDARD TABLE OF ty_FRET WITH HEADER LINE,
      it_PO TYPE STANDARD TABLE OF ty_PO WITH HEADER LINE,
      it_STO TYPE STANDARD TABLE OF ty_STO WITH HEADER LINE,
      wa_OUTPUT TYPE STANDARD TABLE OF ty_xdock WITH HEADER LINE,
      it_OUTPUT TYPE STANDARD TABLE OF ty_xdock WITH HEADER LINE.

Re: SQL Join Fields of Different Length

Former Member — Wed, 30 Sep 2009 23:03:07 GMT

Part B:

SELECTION-SCREEN BEGIN of BLOCK layar2 WITH FRAME TITLE judul2.
PARAMETER PONO LIKE FRET-BLNRB OBLIGATORY.
SELECTION-SCREEN END OF BLOCK layar2.


*Select FRET Data
SELECT FRET~BLNRB
        FRET~BPOSB
        FRET~BLNRA
        FRET~BPOSA
  INTO TABLE it_FRET
  FROM FRET
  WHERE FRET~BLNRB = PONO.

*Select PO Data
SELECT EKPO~EBELN
       EKPO~EBELP
       EKPO~LOEKZ
       EKPO~MENGE
       EKPO~WERKS
       EKPO~MATNR
       MEAN~EAN11
       MAKT~MAKTX
  INTO TABLE it_PO
  FROM EKPO
  LEFT JOIN MAKT
       ON  EKPO~MATNR = MAKT~MATNR
       LEFT JOIN MEAN
       ON  EKPO~MATNR = MEAN~MATNR
       AND MEAN~HPEAN = 'X'
   FOR ALL ENTRIES IN it_FRET
 WHERE EKPO~EBELN = it_FRET-PO AND EKPO~EBELP = it_FRET-POITEM.

*Select STO Data
 SELECT EKPO~EBELN
       EKPO~EBELP
       EKPO~LOEKZ
       EKPO~EGLKZ
       EKPO~ELIKZ
       EKPO~MENGE
       EKPO~WERKS
 FROM EKPO
      INTO TABLE it_STO
  FOR ALL ENTRIES IN it_FRET
 WHERE EKPO~EBELN = it_FRET-STO AND EKPO~EBELP = it_FRET-STOITEM.

*Output FRET Data only if matching PO and STO lines are found which are not deleted, not FDI and not DCI.
Loop at it_FRET.

*Find matching PO lines
  READ TABLE it_PO
  WITH KEY
  PO = it_FRET-PO
  POITEM = it_FRET-POITEM
  PODEL = ' '.
  IF sy-subrc = 0.
*Find matching STO lines
    READ TABLE it_STO
    WITH KEY
    STO = it_FRET-STO
    STOITEM = it_FRET-STOITEM
    STODEL = ' '
    STOFDI = ' '
    STODCI = ' '.
    IF sy-subrc = 0.
      wa_OUTPUT-PO      = it_FRET-PO.
      wa_OUTPUT-POITEM  = it_FRET-POITEM.
      wa_OUTPUT-POQTY   = it_PO-POQTY.
      wa_OUTPUT-DC      = it_PO-POSITE.
      wa_OUTPUT-ARTNO   = it_PO-ARTNO.
      wa_OUTPUT-EAN     = it_PO-EAN.
      wa_OUTPUT-ARTDES  = it_PO-ARTDES.
      wa_OUTPUT-STORE   = it_STO-STOSITE.
      wa_OUTPUT-STOQTY  = it_STO-STOQTY.
      wa_OUTPUT-STO     = it_FRET-STO.
      wa_OUTPUT-STOITEM = it_FRET-STOITEM.
      APPEND wa_OUTPUT to it_OUTPUT.

    ENDIF.

  ENDIF.

ENDLOOP.

*Output to Excel

ENDIF.