topic Re: problem with Join Statement in Application Development and Automation Discussions

problem with Join Statement

Former Member — Tue, 12 Jun 2007 20:34:39 GMT

hay guys

I have written a query in ABAP OO to join 2 tables and have output in different table.Here i want only selected fields in 3 table .I dont know why this query is giving erroe.Help needed.



TYPES : BEGIN OF ty_catsubcat,
          CATEGORYID type ZNCYID,
          DESCRIPTION_CAT TYPE ZNCYID,
          SUBCATID type ZNSBCYID,
          DESCRIPTION_SUBCAT type ZNSBDS,
          END OF ty_catsubcat.



DATA :   IT_CATSUBCAT TYPE TABLE OF  ty_catsubcat.

  Select ZNCYMS~CATEGORYID ZNCYMS~DESCRIPTION ZNSBCYMS~SUBCATID ZNSBCYMS~DESCRIPTION
         INTO (IT_CATSUBCAT-CATEGORYID,
                  IT_CATSUBCAT-DESCRIPTION_CAT, 
                  IT_CATSUBCAT-SUBCATID, 
                  IT_CATSUBCAT-DESCRIPTION_SUBCAT)
         FROM ZNCYMS INNER JOIN ZNSBCYMS
         ON   ZNCYMS~CATEGORYID = ZNSBCYMS~CATEGORYID 
         order by ZNCYMS~CATEGORYID.
  endselect.

Re: problem with Join Statement

Former Member — Tue, 12 Jun 2007 20:42:27 GMT

Don't you want your TYPES statement like this:

TYPES : BEGIN OF ty_catsubcat,
          CATEGORYID LIKE ZNCYMS-CATEGORYID,
          DESCRIPTION_CAT LIKE ZNCYMS-DESCRIPTION,
          SUBCATID LIKE ZNSBCYMS-SUBCATID,
          DESCRIPTION_SUBCAT LIKE ZNSBCYMS-DESCRIPTION,
          END OF ty_catsubcat.

Re: problem with Join Statement

Former Member — Tue, 12 Jun 2007 20:59:41 GMT

A few issues..

Your table it_catsubcat does not have a header line, and you are trying to insert into the header area of the table which does not exist. Either declare a work area/header, or do a table fetch instead.

Select ZNCYMS_{CATEGORYID ZNCYMS}DESCRIPTION ZNSBCYMS_{SUBCATID ZNSBCYMS}DESCRIPTION

<b> INTO CORRESPONDING FIELDS OF TABLE IT_CATSUBCAT</b>

FROM ZNCYMS INNER JOIN ZNSBCYMS

ON ZNCYMS_{CATEGORYID = ZNSBCYMS}CATEGORYID

order by ZNCYMS~CATEGORYID.

If you use a work area to insert into, then you need to append the work area to your internal table.

data: IT_CATSUBCAT TYPE TABLE OF ty_catsubcat with header line.

Select ZNCYMS_{CATEGORYID ZNCYMS}DESCRIPTION ZNSBCYMS_{SUBCATID ZNSBCYMS}DESCRIPTION

INTO (IT_CATSUBCAT-CATEGORYID,

IT_CATSUBCAT-DESCRIPTION_CAT,

IT_CATSUBCAT-SUBCATID,

IT_CATSUBCAT-DESCRIPTION_SUBCAT)

FROM ZNCYMS INNER JOIN ZNSBCYMS

ON ZNCYMS_{CATEGORYID = ZNSBCYMS}CATEGORYID

order by ZNCYMS~CATEGORYID.

append it_catsubcat.

endselect.

Re: problem with Join Statement

Former Member — Tue, 12 Jun 2007 21:07:39 GMT

Hello,

This is how the code should look like.

TYPES : BEGIN OF ty_catsubcat,
          CATEGORYID type ZNCYID,
          DESCRIPTION_CAT TYPE ZNCYID,
          SUBCATID type ZNSBCYID,
          DESCRIPTION_SUBCAT type ZNSBDS,
          END OF ty_catsubcat.
 
 
 
DATA :   IT_CATSUBCAT TYPE TABLE OF  ty_catsubcat <b>with header line</b>.
 
  Select ZNCYMS~CATEGORYID ZNCYMS~DESCRIPTION ZNSBCYMS~SUBCATID ZNSBCYMS~DESCRIPTION
         INTO (IT_CATSUBCAT-CATEGORYID,
                  IT_CATSUBCAT-DESCRIPTION_CAT, 
                  IT_CATSUBCAT-SUBCATID, 
                  IT_CATSUBCAT-DESCRIPTION_SUBCAT)
         FROM ZNCYMS INNER JOIN ZNSBCYMS
         ON   ZNCYMS~CATEGORYID = ZNSBCYMS~CATEGORYID 
         order by ZNCYMS~CATEGORYID.
 <b>append it_catsubcat.</b>
  endselect.

Rgds,

Naren

Re: problem with Join Statement

Former Member — Tue, 12 Jun 2007 21:10:22 GMT

Do not use select endselect and it could be performance issue

See the example program :

Structure

types : begin of ty_ekpo ,

ebeln type ekko-ebeln,

ebelp type ekpo-ebelp,

matnr type ekpo-matnr,

end of ty_ekpo.

Internal table

data i_ekpo type standard table of ty_ekpo.

work area

data wa_ekpo like line of i_ekpo.

start-of-selection.

select a_{ebeln b}ebelp b~matnr into table i_ekpo

from ekko as a inner join ekpo as b on a_{ebeln = b}ebeln.

loop at i_ekpo into wa_ekpo.

endloop.