topic Re: where clause in Application Development and Automation Discussions

where clause

Former Member — Wed, 12 Jul 2006 13:40:25 GMT

hi frnd

plz tell me how to join three table in where clause with example.

Re: where clause

Former Member — Wed, 12 Jul 2006 13:41:48 GMT

PARAMETERS: p_cityfr TYPE spfli-cityfrom,

p_cityto TYPE spfli-cityto.

DATA: BEGIN OF wa,

fldate TYPE sflight-fldate,

carrname TYPE scarr-carrname,

connid TYPE spfli-connid,

END OF wa.

DATA itab LIKE SORTED TABLE OF wa

WITH UNIQUE KEY fldate carrname connid.

SELECT c_{carrname p}connid f~fldate

INTO CORRESPONDING FIELDS OF TABLE itab

FROM ( ( scarr AS c

INNER JOIN spfli AS p ON p_{carrid = c}carrid

AND p~cityfrom = p_cityfr

AND p~cityto = p_cityto )

INNER JOIN sflight AS f ON f_{carrid = p}carrid

AND f_{connid = p}connid ).

LOOP AT itab INTO wa.

WRITE: / wa-fldate, wa-carrname, wa-connid.

ENDLOOP.

-Kiran

Re: where clause

Former Member — Wed, 12 Jul 2006 13:42:35 GMT

select single *

into corresponding fields of wa_vbsegs_ext

from vbsegs inner join vbkpf

on vbsegs_{ausbk = vbkpf}ausbk

and vbsegs_{bukrs = vbkpf}bukrs

and vbsegs_{belnr = vbkpf}belnr

and vbsegs_{gjahr = vbkpf}gjahr

where vbsegs~ausbk in lt_seloption

and vbsegs~belnr = it_pos-belnr

and vbsegs~gjahr = it_pos-gjahr

and vbsegs~bukrs = it_pos-bukrs

and vbsegs~buzei = it_pos-buzei

and vbkpf~budat = it_pos-budat.

from sap standard prgram rfitemar

rewardif useful

Re: where clause

Former Member — Wed, 12 Jul 2006 13:44:01 GMT

Hello,

Try like this.

SELECT EKKN_{EBELN EKKN}EBELP EKKN_{KOSTL EKKN}PS_PSP_PNR EKKN~SAKTO

EKET_{WEMNG EKET}ETENR EKET_{BEDAT EKPO}KNTTP EKPO_{LOEKZ EKPO}MATNR

EKPO_{WERKS EKPO}MATKL EKPO_{MENGE EKPO}MEINS EKPO_{NETPR EKPO}PEINH

EKPO_{NETWR EKPO}MTART EKPO_{EFFWR EKKO}BUKRS EKKO_{BSART EKKO}ERNAM

EKKO_{LIFNR EKKO}EKORG EKKO_{EKGRP EKKO}WAERS EKKO~BEDAT

EKKO~MEMORY INTO TABLE G_T_OUTTAB

FROM ( EKKN

INNER JOIN EKET

ON EKET_{EBELN = EKKN}EBELN

AND EKET_{EBELP = EKKN}EBELP

INNER JOIN EKPO

ON EKPO_{EBELN = EKET}EBELN

AND EKPO_{EBELP = EKET}EBELP

INNER JOIN EKKO

ON EKKO_{EBELN = EKPO}EBELN )

WHERE EKKN~EBELN IN S_EBELN

AND EKKN~EBELP IN S_EBELP

AND EKKN~KOSTL IN S_KOSTL

AND EKKN~PS_PSP_PNR IN S_WBS

AND EKKN~SAKTO IN S_SAKTO

AND EKPO~KNTTP IN S_KNTTP

AND EKPO~LOEKZ IN S_LOEKZ

AND EKPO~MATKL IN S_MATKL

AND EKPO~MATNR IN S_MATNR

AND EKPO~WERKS IN S_WERKS

AND EKKO~BEDAT IN S_BEDAT

AND EKKO~BSART IN S_BSART

AND EKKO~BUKRS IN S_BUKRS

AND EKKO~EKGRP IN S_EKGRP

AND EKKO~EKORG IN S_EKORG

AND EKKO~ERNAM IN S_ERNAM

AND EKKO~LIFNR IN S_LIFNR

AND EKPO~MTART IN S_MTART

AND EKKO~MEMORY IN S_MEMORY

AND EKET~BEDAT IN S_DAT_ET.

If useful reward points.

Regards,

Vasanth

Re: where clause

RichHeilman — Wed, 12 Jul 2006 13:44:15 GMT

Here is an example.



select mara~matnr mara~mtart marc~werks marc~dispo
       mard~lgort mard~labst
             from mara
                  inner join marc
                     on mara~matnr = marc~matnr
                  inner join mard
                     on marc~matnr = mard~matnr
                    and marc~werks = mard~werks
                            where mara~matnr in s_matnr
                              and mara~mtart in s_mtart
                              and marc~werks in s_werks
                              and marc~dispo in s_dispo
                              and mard~lgort in s_lgort.

Regards,

Rich Heilman

Re: where clause

Former Member — Wed, 12 Jul 2006 13:53:13 GMT

Hi Ankool,

Consider this sample program.


REPORT zarun_1.

 TYPES : BEGIN OF tp_combo,
         matnr TYPE matnr,
         werks TYPE werks_d,
         maktx TYPE maktx,
         END OF tp_combo.
 
 
 DATA: it_combo TYPE STANDARD TABLE OF tp_combo,
       wa_combo TYPE tp_combo.
 
 
 PARAMETERS : p_werks TYPE werks_d ,
              p_matnr TYPE matnr  .
 
  START-OF-SELECTION.
       SELECT m~matnr w~werks d~maktx
             INTO TABLE it_combo
             FROM ( marc AS w INNER JOIN mara AS m
                 ON m~matnr = w~matnr )             "Gets matnr from MARA & MARC
                 INNER JOIN makt AS d      "The combined result of above JOIN is JOINED with MAKT
                 ON d~matnr = m~matnr
                 WHERE m~matnr = p_matnr.  "Where matnr is the PARAMETER to be comapared with MARA-MATNR
 
       LOOP AT it_combo INTO wa_combo.
 
            WRITE : / wa_combo-matnr,
                    wa_combo-werks,
                    wa_combo-maktx.
       ENDLOOP.

Regards,

Arun Sambargi.

Message was edited by: Arun Sambargi

Re: where clause

Former Member — Wed, 12 Jul 2006 13:59:22 GMT

hi,

Check this

SELECT * INTO WA 
    FROM ( SFLIGHT AS F INNER JOIN SPFLI AS P 
             ON F~CARRID = P~CARRID AND 
                F~CONNID = P~CONNID ) 
           INNER JOIN SCARR AS C 
             ON F~CARRID = C~CARRID 
    WHERE P~CITYFROM = 'FRANKFURT' 
      AND P~CITYTO   = 'NEW YORK' 
      AND F~FLDATE BETWEEN '20010910' AND '20010920' 
      AND F~SEATSOCC < F~SEATSMAX. 
  WRITE: / WA-CARR-CARRNAME, WA-FLIGHT-FLDATE, WA-FLIGHT-CARRID, 
           WA-FLIGHT-CONNID. 
ENDSELECT.