topic Re:Select statement in Application Development and Automation Discussions

Re:Select statement

Former Member — Wed, 28 Nov 2007 14:35:44 GMT

In my td they are giving a select statement to retrive data by using inner join, but i need to write those statement using for all entries and how i will write for that can any one give me the code for this .

SELECT MDKP_{BESKZ MDKP}DISMM MDKP_{DSDAT MDKP}MATNR MDKP~MSGAR

MDKP_{MSGID MDKP}MSGNR MDKP_{MSGV1 MDKP}MSGV2 MDKP~MSGV3

MDKP_{MSGV4 MDKP}PLSCN MDKP_{PLWRK MDKP}SOBSL T100~ARBGB

T100_{MSGNR T100}SPRSL T100_{TEXT MAKT}MAKTX MAKT_{MATNR MAKT}SPRAS

MARC_{MATNR MARC}MMSTA MARC~WERKS

INTO (MDKP-BESKZ , MDKP-DISMM , MDKP-DSDAT , MDKP-MATNR , MDKP-MSGAR

, MDKP-MSGID , MDKP-MSGNR , MDKP-MSGV1 , MDKP-MSGV2 , MDKP-MSGV3

, MDKP-MSGV4 , MDKP-PLSCN , MDKP-PLWRK , MDKP-SOBSL , T100-ARBGB

, T100-MSGNR , T100-SPRSL , T100-TEXT , MAKT-MAKTX , MAKT-MATNR

, MAKT-SPRAS , MARC-MATNR , MARC-MMSTA , MARC-WERKS )

FROM ( MDKP

INNER JOIN T100

ON T100_{ARBGB = MDKP}MSGID

AND T100_{MSGNR = MDKP}MSGNR

INNER JOIN MAKT

ON MAKT_{MATNR = MDKP}MATNR

AND MAKT_{SPRAS = T100}SPRSL

INNER JOIN MARC

ON MARC_{MATNR = MDKP}MATNR

AND MARC_{WERKS = MDKP}PLWRK )

WHERE MDKP~DSDAT IN SP$00003

AND MDKP~PLSCN IN SP$00001

AND MDKP~PLWRK IN so_werks

AND T100~SPRSL IN SP$00004.

Re: Re:Select statement

naimesh_patel — Wed, 28 Nov 2007 14:55:01 GMT

Try to do like this:

REPORT  ZTEST_NP.

DATA: BEGIN OF IT_MDKP OCCURS 0,
      BESKZ  TYPE MDKP-BESKZ,
      DISMM  TYPE MDKP-DISMM,
      DSDAT  TYPE MDKP-DSDAT,
      MATNR  TYPE MDKP-MATNR,
      MSGAR  TYPE MDKP-MSGAR,
      MSGID  TYPE MDKP-MSGID,
      MSGNR  TYPE MDKP-MSGNR,
      MSGV1  TYPE MDKP-MSGV1,
      MSGV2  TYPE MDKP-MSGV2,
      MSGV3  TYPE MDKP-MSGV3 ,
      MSGV4  TYPE MDKP-MSGV4 ,
      PLSCN  TYPE MDKP-PLSCN,
      PLWRK  TYPE MDKP-PLWRK ,
      SOBSL  TYPE MDKP-SOBSL ,
      END   OF IT_MDKP.

DATA: BEGIN OF IT_T100 OCCURS 0,
      ARBGB TYPE T100-ARBGB,
      MSGNR TYPE T100-MSGNR ,
      SPRSL TYPE T100-SPRSL ,
      TEXT  TYPE T100-TEXT ,
      END   OF IT_T100.

DATA: BEGIN OF IT_MAKT OCCURS 0,
      MATNR TYPE MAKT-MATNR,
      MKATX TYPE MAKT-MAKTX ,
      SPRAS TYPE MAKT-SPRAS ,
      END   OF IT_MAKT.

DATA: BEGIN OF IT_MARC OCCURS 0,
      MATNR TYPE MARC-MATNR ,
      WERKS TYPE MARC-WERKS,
      MMSTA TYPE MARC-MMSTA ,
      END   OF IT_MARC.

SELECT BESKZ DISMM DSDAT MATNR MSGAR MSGID MSGNR MSGV1 MSGV2 MSGV3
       MSGV4 PLSCN PLWRK SOBSL
       INTO TABLE IT_MDKP
       FROM  MDKP.
       WHERE DSDAT IN SP[code]REPORT  ZTEST_NP.

DATA: BEGIN OF IT_MDKP OCCURS 0,
      BESKZ  TYPE MDKP-BESKZ,
      DISMM  TYPE MDKP-DISMM,
      DSDAT  TYPE MDKP-DSDAT,
      MATNR  TYPE MDKP-MATNR,
      MSGAR  TYPE MDKP-MSGAR,
      MSGID  TYPE MDKP-MSGID,
      MSGNR  TYPE MDKP-MSGNR,
      MSGV1  TYPE MDKP-MSGV1,
      MSGV2  TYPE MDKP-MSGV2,
      MSGV3  TYPE MDKP-MSGV3 ,
      MSGV4  TYPE MDKP-MSGV4 ,
      PLSCN  TYPE MDKP-PLSCN,
      PLWRK  TYPE MDKP-PLWRK ,
      SOBSL  TYPE MDKP-SOBSL ,
      END   OF IT_MDKP.

DATA: BEGIN OF IT_T100 OCCURS 0,
      ARBGB TYPE T100-ARBGB,
      MSGNR TYPE T100-MSGNR ,
      SPRSL TYPE T100-SPRSL ,
      TEXT  TYPE T100-TEXT ,
      END   OF IT_T100.

DATA: BEGIN OF IT_MAKT OCCURS 0,
      MATNR TYPE MAKT-MATNR,
      MKATX TYPE MAKT-MAKTX ,
      SPRAS TYPE MAKT-SPRAS ,
      END   OF IT_MAKT.

DATA: BEGIN OF IT_MARC OCCURS 0,
      MATNR TYPE MARC-MATNR ,
      WERKS TYPE MARC-WERKS,
      MMSTA TYPE MARC-MMSTA ,
      END   OF IT_MARC.

SELECT BESKZ DISMM DSDAT MATNR MSGAR MSGID MSGNR MSGV1 MSGV2 MSGV3
       MSGV4 PLSCN PLWRK SOBSL
       INTO TABLE IT_MDKP
       FROM  MDKP.
       WHERE DSDAT IN SP$00003
       AND   PLSCN IN SP$00001
       AND   PLWRK IN so_werks.

IF NOT IT_MDKP[] IS INITIAL.
SELECT ARBGB MSGNR SPRSL TEXT
       INTO TABLE IT_T100
       FROM T100
       FOR   ALL ENTRIES IN IT_MDKP
       WHERE ARBGB = IT_MDKP-MSGID
       AND   MSGNR = IT_MDKP-MSGNR
       AND   SPRSL = SY-LANGU.

SELECT MATNR MAKTX SPRAS
       INTO TABLE IT_MAKT
       FROM MAKT
       FOR  ALL ENTRIES IN IT_MDKP
       WHERE MATNR = it_MDKP-MATNR
       AND   SPRAS = SY-LANGU.

SELECT MATNR WERKS MMSTA
       INTO TABLE IT_MARC
       FROM MARC
       FOR ALL ENTRIES IN IT_MDKP
       WHERE MATNR = IT_MDKP-MATNR
       AND   WERKS = IT_MDKP-PLWRK.
ENDIF.[/code]3
       AND   PLSCN IN SPREPORT  ZTEST_NP.

DATA: BEGIN OF IT_MDKP OCCURS 0,
      BESKZ  TYPE MDKP-BESKZ,
      DISMM  TYPE MDKP-DISMM,
      DSDAT  TYPE MDKP-DSDAT,
      MATNR  TYPE MDKP-MATNR,
      MSGAR  TYPE MDKP-MSGAR,
      MSGID  TYPE MDKP-MSGID,
      MSGNR  TYPE MDKP-MSGNR,
      MSGV1  TYPE MDKP-MSGV1,
      MSGV2  TYPE MDKP-MSGV2,
      MSGV3  TYPE MDKP-MSGV3 ,
      MSGV4  TYPE MDKP-MSGV4 ,
      PLSCN  TYPE MDKP-PLSCN,
      PLWRK  TYPE MDKP-PLWRK ,
      SOBSL  TYPE MDKP-SOBSL ,
      END   OF IT_MDKP.

DATA: BEGIN OF IT_T100 OCCURS 0,
      ARBGB TYPE T100-ARBGB,
      MSGNR TYPE T100-MSGNR ,
      SPRSL TYPE T100-SPRSL ,
      TEXT  TYPE T100-TEXT ,
      END   OF IT_T100.

DATA: BEGIN OF IT_MAKT OCCURS 0,
      MATNR TYPE MAKT-MATNR,
      MKATX TYPE MAKT-MAKTX ,
      SPRAS TYPE MAKT-SPRAS ,
      END   OF IT_MAKT.

DATA: BEGIN OF IT_MARC OCCURS 0,
      MATNR TYPE MARC-MATNR ,
      WERKS TYPE MARC-WERKS,
      MMSTA TYPE MARC-MMSTA ,
      END   OF IT_MARC.

SELECT BESKZ DISMM DSDAT MATNR MSGAR MSGID MSGNR MSGV1 MSGV2 MSGV3
       MSGV4 PLSCN PLWRK SOBSL
       INTO TABLE IT_MDKP
       FROM  MDKP.
       WHERE DSDAT IN SP$00003
       AND   PLSCN IN SP$00001
       AND   PLWRK IN so_werks.

IF NOT IT_MDKP[] IS INITIAL.
SELECT ARBGB MSGNR SPRSL TEXT
       INTO TABLE IT_T100
       FROM T100
       FOR   ALL ENTRIES IN IT_MDKP
       WHERE ARBGB = IT_MDKP-MSGID
       AND   MSGNR = IT_MDKP-MSGNR
       AND   SPRSL = SY-LANGU.

SELECT MATNR MAKTX SPRAS
       INTO TABLE IT_MAKT
       FROM MAKT
       FOR  ALL ENTRIES IN IT_MDKP
       WHERE MATNR = it_MDKP-MATNR
       AND   SPRAS = SY-LANGU.

SELECT MATNR WERKS MMSTA
       INTO TABLE IT_MARC
       FROM MARC
       FOR ALL ENTRIES IN IT_MDKP
       WHERE MATNR = IT_MDKP-MATNR
       AND   WERKS = IT_MDKP-PLWRK.
ENDIF.
       AND   PLWRK IN so_werks.

IF NOT IT_MDKP[] IS INITIAL.
SELECT ARBGB MSGNR SPRSL TEXT
       INTO TABLE IT_T100
       FROM T100
       FOR   ALL ENTRIES IN IT_MDKP
       WHERE ARBGB = IT_MDKP-MSGID
       AND   MSGNR = IT_MDKP-MSGNR
       AND   SPRSL = SY-LANGU.

SELECT MATNR MAKTX SPRAS
       INTO TABLE IT_MAKT
       FROM MAKT
       FOR  ALL ENTRIES IN IT_MDKP
       WHERE MATNR = it_MDKP-MATNR
       AND   SPRAS = SY-LANGU.

SELECT MATNR WERKS MMSTA
       INTO TABLE IT_MARC
       FROM MARC
       FOR ALL ENTRIES IN IT_MDKP
       WHERE MATNR = IT_MDKP-MATNR
       AND   WERKS = IT_MDKP-PLWRK.
ENDIF.

Regards,

Naimesh Patel

Re: Re:Select statement

Former Member — Wed, 28 Nov 2007 14:57:28 GMT

Hi Naga,

Do like this

Declare independent internal tables for each table fields and declare final data table.

SELECT BESKZ DISMM DSDAT MATNR MSGAR MSGID MSGNR MSGV1 MSGV2 MSGV3 MSGV4 PLSCN PLWRK SOBSL
 FROM MDKP INTO TABLE IT_MDKP
 WHERE MDKP~DSDAT IN SP[code]SELECT BESKZ DISMM DSDAT MATNR MSGAR MSGID MSGNR MSGV1 MSGV2 MSGV3 MSGV4 PLSCN PLWRK SOBSL
 FROM MDKP INTO TABLE IT_MDKP
 WHERE MDKP~DSDAT IN SP$00003
   AND MDKP~PLSCN IN SP$00001
   AND MDKP~PLWRK IN so_werks.

IF SY-SUBRC = 0.
SELECT ARBGB MSGNR SPRSL TEXT FROM T100 INTO TABLE IT_T100 FOR ALL ENTRIES IN MDKP 
 WHERE ARBGB = IT_MDKP-MSGID AND MSGNR = IT_MDKP-MSGNR AND SPRSL IN SP$00004.
IF SY-SUBRC = 0.
 SELECT MATNR MMSTA WERKS FROM MARC INTO TABLE IT_MARC FOR ALL ENTRIES IN IT_MDKP
  WHERE MATNR = IT_MDKP-MATNR AND WERKS = IT_MDKP-WERKS.
 IF SY-SUBRC = 0.
  SELECT MATNR MAKTX SPRAS FROM MAKT INTO TABLE IT_MAKT FOR ALL ENTRIES IN IT_MARC 
    WHERE MATNR = IT_MARC AND SPRAS IN SP$00004.
 ENDIF. 
ENDIF.
ENDIF.[/code]3
   AND MDKP~PLSCN IN SPSELECT BESKZ DISMM DSDAT MATNR MSGAR MSGID MSGNR MSGV1 MSGV2 MSGV3 MSGV4 PLSCN PLWRK SOBSL
 FROM MDKP INTO TABLE IT_MDKP
 WHERE MDKP~DSDAT IN SP$00003
   AND MDKP~PLSCN IN SP$00001
   AND MDKP~PLWRK IN so_werks.

IF SY-SUBRC = 0.
SELECT ARBGB MSGNR SPRSL TEXT FROM T100 INTO TABLE IT_T100 FOR ALL ENTRIES IN MDKP 
 WHERE ARBGB = IT_MDKP-MSGID AND MSGNR = IT_MDKP-MSGNR AND SPRSL IN SP$00004.
IF SY-SUBRC = 0.
 SELECT MATNR MMSTA WERKS FROM MARC INTO TABLE IT_MARC FOR ALL ENTRIES IN IT_MDKP
  WHERE MATNR = IT_MDKP-MATNR AND WERKS = IT_MDKP-WERKS.
 IF SY-SUBRC = 0.
  SELECT MATNR MAKTX SPRAS FROM MAKT INTO TABLE IT_MAKT FOR ALL ENTRIES IN IT_MARC 
    WHERE MATNR = IT_MARC AND SPRAS IN SP$00004.
 ENDIF. 
ENDIF.
ENDIF.
   AND MDKP~PLWRK IN so_werks.

IF SY-SUBRC = 0.
SELECT ARBGB MSGNR SPRSL TEXT FROM T100 INTO TABLE IT_T100 FOR ALL ENTRIES IN MDKP 
 WHERE ARBGB = IT_MDKP-MSGID AND MSGNR = IT_MDKP-MSGNR AND SPRSL IN SP[code]SELECT BESKZ DISMM DSDAT MATNR MSGAR MSGID MSGNR MSGV1 MSGV2 MSGV3 MSGV4 PLSCN PLWRK SOBSL
 FROM MDKP INTO TABLE IT_MDKP
 WHERE MDKP~DSDAT IN SP$00003
   AND MDKP~PLSCN IN SP$00001
   AND MDKP~PLWRK IN so_werks.

IF SY-SUBRC = 0.
SELECT ARBGB MSGNR SPRSL TEXT FROM T100 INTO TABLE IT_T100 FOR ALL ENTRIES IN MDKP 
 WHERE ARBGB = IT_MDKP-MSGID AND MSGNR = IT_MDKP-MSGNR AND SPRSL IN SP$00004.
IF SY-SUBRC = 0.
 SELECT MATNR MMSTA WERKS FROM MARC INTO TABLE IT_MARC FOR ALL ENTRIES IN IT_MDKP
  WHERE MATNR = IT_MDKP-MATNR AND WERKS = IT_MDKP-WERKS.
 IF SY-SUBRC = 0.
  SELECT MATNR MAKTX SPRAS FROM MAKT INTO TABLE IT_MAKT FOR ALL ENTRIES IN IT_MARC 
    WHERE MATNR = IT_MARC AND SPRAS IN SP$00004.
 ENDIF. 
ENDIF.
ENDIF.[/code]4.
IF SY-SUBRC = 0.
 SELECT MATNR MMSTA WERKS FROM MARC INTO TABLE IT_MARC FOR ALL ENTRIES IN IT_MDKP
  WHERE MATNR = IT_MDKP-MATNR AND WERKS = IT_MDKP-WERKS.
 IF SY-SUBRC = 0.
  SELECT MATNR MAKTX SPRAS FROM MAKT INTO TABLE IT_MAKT FOR ALL ENTRIES IN IT_MARC 
    WHERE MATNR = IT_MARC AND SPRAS IN SP[code]SELECT BESKZ DISMM DSDAT MATNR MSGAR MSGID MSGNR MSGV1 MSGV2 MSGV3 MSGV4 PLSCN PLWRK SOBSL
 FROM MDKP INTO TABLE IT_MDKP
 WHERE MDKP~DSDAT IN SP$00003
   AND MDKP~PLSCN IN SP$00001
   AND MDKP~PLWRK IN so_werks.

IF SY-SUBRC = 0.
SELECT ARBGB MSGNR SPRSL TEXT FROM T100 INTO TABLE IT_T100 FOR ALL ENTRIES IN MDKP 
 WHERE ARBGB = IT_MDKP-MSGID AND MSGNR = IT_MDKP-MSGNR AND SPRSL IN SP$00004.
IF SY-SUBRC = 0.
 SELECT MATNR MMSTA WERKS FROM MARC INTO TABLE IT_MARC FOR ALL ENTRIES IN IT_MDKP
  WHERE MATNR = IT_MDKP-MATNR AND WERKS = IT_MDKP-WERKS.
 IF SY-SUBRC = 0.
  SELECT MATNR MAKTX SPRAS FROM MAKT INTO TABLE IT_MAKT FOR ALL ENTRIES IN IT_MARC 
    WHERE MATNR = IT_MARC AND SPRAS IN SP$00004.
 ENDIF. 
ENDIF.
ENDIF.[/code]4.
 ENDIF. 
ENDIF.
ENDIF.

loop through IT_MDKP.

read the enries from other tables and move to the final table along with mdkp entries.

endloop.

Regards,

Satish