Issue with select query join....

[Former Member]

Hello,

I am facing a problem with a select query. I get a message that itab_data is not long enough.

DATA: itab_data like ptrv_head occurs 0 with header line.

SELECT *    from PTRV_HEAD 
            as a inner join PA0002 as b
            on a~pernr = b~pernr
            inner join PA0001 as c
            on a~pernr = c~pernr
            INTO TABLE itab_data
       where a~PERNR in S_PERNR
       and   a~REINR in S_TRIP
       and   a~ZLAND in S_LAND
       and   a~DATV1 in S_BEGIN
       and   a~DATB1 in S_END
       and   b~NACHN in S_FIRST
       and   b~VORNA in S_LAST
       and   c~BUKRS in S_BUKRS
       and   c~KOSTL in S_KOSTL
       and   c~PERSG in S_EMPGP
       and   c~PERSK in S_SUBGP
       and   c~begda in ldbbegda
       and   c~endda in ldbendda.

Regards,

Jainam.

Edited by: Jainam Shah on Mar 27, 2009 4:13 PM

Edited by: Jainam Shah on Mar 27, 2009 4:13 PM

[former_member156446]

check the decelaration of itab_data, it need to accomdate all field from PTRV_HEAD

[Former Member]

Tahnks for reponse.

Thatswhat I am declaring using data statement as follows:

DATA: itab_data like table of ptrv_head with header line.

[former_member156446]

>

> Tahnks for reponse.
> 
> Thatswhat I am declaring using data statement as follows:
> 
> DATA: itab_data like table of ptrv_head with header line.

data: begin of t_data.

include structure PTRV_HEAD.

data: end of t_data.

data: itab_data type standard table of t_data.

*Note: select * is not performance effective approach*

[Former Member]

And you are getting all those extra fields from the PA00... tables. You'll have to code the individual fields that you want both in the SELECT statement and the internal table.

Rob

[Former Member]

HI,

SELECT <Specify the fields as per the itab>  from PTRV_HEAD 
            as a inner join PA0002 as b
            on a~pernr = b~pernr
            inner join PA0001 as c
            on a~pernr = c~pernr
            INTO TABLE itab_data
       where a~PERNR in S_PERNR
       and   a~REINR in S_TRIP
       and   a~ZLAND in S_LAND
       and   a~DATV1 in S_BEGIN
       and   a~DATB1 in S_END
       and   b~NACHN in S_FIRST
       and   b~VORNA in S_LAST
       and   c~BUKRS in S_BUKRS
       and   c~KOSTL in S_KOSTL
       and   c~PERSG in S_EMPGP
       and   c~PERSK in S_SUBGP
       and   c~begda in ldbbegda
       and   c~endda in ldbendda.

Or check the Field Itab declaration

[ThomasZloch]

You read data from three tables via your join, that's why just PTRV_HEAD is not long enough.

I suggest you declare your internal table with only those fields from the three tables that you really require for further processing, then use "SELECT * INTO CORRESPONDING FIELDS OF TABLE ...", which is not a performance stopper contrary to common misbelief.

Thomas

[Former Member]

Hi,

Try this..

DATA: t_dfies  TYPE STANDARD TABLE OF dfies.
DATA: t_fields TYPE STANDARD TABLE OF char40.
DATA: s_dfies  TYPE dfies,
      s_fields TYPE char40.

* Get the fields
CALL FUNCTION 'DDIF_FIELDINFO_GET'
  EXPORTING
    tabname        = 'PTRV_HEAD'
  TABLES
    dfies_tab      = t_dfies[]
  EXCEPTIONS
    not_found      = 1
    internal_error = 2
    OTHERS         = 3.
IF sy-subrc <> 0.
  MESSAGE ID sy-msgid TYPE sy-msgty NUMBER sy-msgno
          WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.
ENDIF.

* Build the fields to be selected.
LOOP AT t_dfies INTO s_dfies.

  CONCATENATE 'A~' s_dfies-fieldname INTO s_fields.

  APPEND s_fields TO t_fields.
  CLEAR: s_fields.

ENDLOOP.

* Select.
SELECT (t_fields)    from PTRV_HEAD 
      ...........

Thanks

Naren