topic Re: SELECT field AS in Application Development and Automation Discussions

SELECT field AS

MichiFr — Thu, 15 Sep 2011 14:26:31 GMT

Hi,

I've got this type and data definition:


  TYPES:
    BEGIN OF t_purchase,
        ebeln   TYPE ekpo-ebeln,
        ebelp   TYPE ekpo-ebelp,
        bukrs   TYPE ekko-bukrs,
        bstyp   TYPE ekko-bstyp,
        bsart   TYPE ekko-bsart,
        loekz   TYPE ekko-loekz,
        matnr   TYPE ekpo-matnr,
        txz01   TYPE ekpo-txz01,
        menge   TYPE ekpo-menge,
        meins   TYPE ekpo-meins,
        loekz2  TYPE ekpo-loekz,
    END OF t_purchase
    .
  DATA:
    l_tab_purchase TYPE STANDARD TABLE OF t_purchase.

and would like to fill the internal table with this join statement:

    SELECT
        ekkn~aufnr ekkn~ebeln ekkn~ebelp 
        ekko~loekz ekko~bedat
        ekpo~ebeln ekpo~ebelp ekko~bukrs ekko~bstyp
        ekko~bsart ekko~loekz ekpo~matnr ekpo~txz01
        ekpo~menge ekpo~loekz as loekz2
       INTO CORRESPONDING FIELDS OF TABLE l_tab_purchase
       FROM ( ( ekpo INNER JOIN ekkn ON ekpo~ebeln = ekkn~ebeln
                                    AND ekpo~ebelp = ekkn~ebelp )
                     INNER JOIN ekko ON ekko~ebeln = ekkn~ebeln )
                                  WHERE ekkn~aufnr = i_document-reference.

As one will notice the above type/table has got two different deletion markers loekz and loekz2. The first one is or should be filled from table EKKO and the second one from table EKPO.

Unfortunately both markers do have the same names in each database table which means the MOVE-CORRESPONDING will get into some troubles while finding the correct field.

Of course the statement is executed properly, however, field LOEKZ2 is not filled up correctly. I guess the statement AS LOEKZ2 is simply ignored, at least it does not do its job at all.

Is there any way to get this running? I mean to select two identical fieldnames but move them to different target fields?

cu,

Michael

Re: SELECT field AS

ThomasZloch — Thu, 15 Sep 2011 15:05:28 GMT

Strange, I can confirm that AS works as desired for me in a similar scenario. As a workaround you could arrange the selected columns in the same order as the target structure and omit the CORRESPONDING FIELDS OF.

Thomas

Re: SELECT field AS

sjeevan — Thu, 15 Sep 2011 23:30:03 GMT

A more elegant way of writing that using alias for table names is, this should work for you


 SELECT
        A~ebeln
        A~ebelp 
        C~bukrs 
        C~bstyp 
        C~bsart  
        C~loekz  
        A~matnr 
        A~txz01 
        A~menge
        A~meins 
        A~loekz
      
    INTO TABLE l_tab_purchase
       FROM

ekpo as A 
INNER JOIN ekkn as B
ON  A~ebeln = B~ebeln
AND A~ebelp = B~ebelp 

INNER JOIN ekko as C 
ON  C~ebeln = B~ebeln 

WHERE B~aufnr = i_document-reference.

NB: Using alias table names is optional though.

Re: SELECT field AS

Former Member — Thu, 15 Sep 2011 23:44:27 GMT

Remove INTO CORRESPONDING FIELDS OF TABLE and make the sequence of the SELECT fields same as of TYPES declarations. The system will automatically move the value LOEKZ of EKPO to LOEKZ2 of ITAB since the length and data type are same.

Please try below code


TYPES:
   BEGIN OF t_purchase,
       ebeln   TYPE ekpo-ebeln,
       ebelp   TYPE ekpo-ebelp,
       bukrs   TYPE ekko-bukrs,
       bstyp   TYPE ekko-bstyp,
       bsart   TYPE ekko-bsart,
       loekz   TYPE ekko-loekz,
       matnr   TYPE ekpo-matnr,
       txz01   TYPE ekpo-txz01,
       menge   TYPE ekpo-menge,
       meins   TYPE ekpo-meins,
       loekz2  TYPE ekpo-loekz,
   END OF t_purchase
   .
DATA:
  l_tab_purchase TYPE STANDARD TABLE OF t_purchase.



SELECT ekpo~ebeln
      ekpo~ebelp
      ekko~bukrs
      ekko~bstyp
      ekko~bsart
      ekko~loekz
      ekpo~matnr
      ekpo~txz01
      ekpo~menge
      ekpo~loekz
     INTO l_tab_purchase
     FROM ( ( ekpo INNER JOIN ekkn ON ekpo~ebeln = ekkn~ebeln
                                  AND ekpo~ebelp = ekkn~ebelp )
                   INNER JOIN ekko ON ekko~ebeln = ekkn~ebeln
 INNER JOIN ekko ON ekko~ebeln = ekkn~ebeln )
                                  WHERE ekkn~aufnr = i_document-reference ).

Shiva

Re: SELECT field AS

Former Member — Fri, 16 Sep 2011 02:29:52 GMT

Michael,

I checked your exact Select statement and it works FINE for me. Field LOEKZ2 is filled correctly.

I have No idea why it is not working for you, btw I am in ECC 6.0 but don't think it has anything to do with that.

Though, personally I will never use 'into corresponding fields of' unless it is MUST which is not the case here.

Regards,

Diwakar

Re: SELECT field AS

MichiFr — Fri, 16 Sep 2011 07:08:34 GMT

Damn! OK guys - got this working. But you won't guess what caused the problem or confusion.

I modified, for testing purposes, the appropriate EKPO record, however, I didn't do it in field LOEKZ but in field PUT_BACK. The later is displayed in UC_SE16N grid's header almost equal to LOEKZ hence I've updated the wrong data.

Thanks guys for reading - points awarded!

Michael