topic Re: query in Application Development and Automation Discussions

query

Former Member — Fri, 18 May 2007 11:06:17 GMT

Hi experts,

Can you please convert this query from inner join to for all entries in statement query.

***********************************

SELECT v_{vbeln v}vdatu v_{kunnr v}erdat

p_{posnr p}matnr p~kwmeng

INTO CORRESPONDING FIELDS OF TABLE it_tab1

FROM vbak AS v

INNER JOIN vbap AS p

ON p_{vbeln = v}vbeln

WHERE v~vbeln IN s_vbeln

AND v~kunnr IN s_kunnr

AND v~erdat IN s_erdat

AND v~vbtyp eq 'C'

AND v~vkorg IN s_vkorg

AND p~matnr IN s_matnr

AND p~vstel EQ s_vstel.

*************************************************

Change this in to for all entries in ,please help me.

Re: query

Former Member — Fri, 18 May 2007 11:12:12 GMT

When you are fetching data from VBAK and VBAP it is good to use a join like what you wrote. Forall entries is not required,

but if you need see.

declare IT_VBAK and IT_VBAP for VBAK and VBAP table fields.

<b>SELECT vbeln vdatu kunnr erdat

INTO TABLE it_vbak FROM vbak

WHERE vbeln IN s_vbeln

AND kunnr IN s_kunnr

AND erdat IN s_erdat

AND vbtyp eq 'C'

AND vkorg IN s_vkorg.

if not it_vbak[] is initial.

SELECT vbeln posnr matnr kwmeng

INTO TABLE it_vbap

FROM vbap

forall entries in it_VBAK

WHERE vbeln = it_vbak-vbeln

AND matnr IN s_matnr

AND vstel EQ s_vstel.

endif.</b>

Reward points if useful

Regards

Anji

Re: query

Former Member — Fri, 18 May 2007 11:12:39 GMT

select vbeln vdatu kunnr erdat from vbak into table i_vbak.

if not i_vbak[] is initial.

select posnr matnr kwmeng from vbap into table i_vbap

for allentries in i_vbak

where vbeln = i_vbak-vbeln.

endif.

Re: query

seshatalpasai_madala — Fri, 18 May 2007 11:12:42 GMT

Hi,

DATA: it_vbap type table vbap occurs n.

SELECT * from VBAP into table it_vbap where matnr IN s_matnr and vstel = s_vstel.

and then

SELECT v_{vbeln v}vdatu v_{kunnr v}erdat

p_{posnr p}matnr p~kwmeng

INTO CORRESPONDING FIELDS OF TABLE it_tab1

FROM vbak AS v

FOR ALL ENTRIES IN it_vbap

WHERE it_vbap-vbeln = v~vbeln

v~vbeln IN s_vbeln

AND v~kunnr IN s_kunnr

AND v~erdat IN s_erdat

AND v~vbtyp eq 'C'

AND v~vkorg IN s_vkorg.

Regards,

Sesh

Re: query

Former Member — Fri, 18 May 2007 11:16:16 GMT

hi,

SELECT vbeln vdatu kunnr erdat

INTO CORRESPONDING FIELDS OF TABLE it_tab1

FROM vbak where vbeln in s_vbeln and

kunnr IN s_kunnrAND

erdat IN s_erdat AND

vbtyp eq 'C' and

vkorg IN s_vkorg

if not it_tab1[] is initial.

select posnr matnr kwmeng into corresponding fields of table it_tab2 for all entries of table it_tab1 where vbeln in s_vbeln AND

matnr IN s_matnr AND

vstel EQ s_vstel.

endif.

Reward with points if helpful.

Re: query

Former Member — Fri, 18 May 2007 11:17:09 GMT

Hi,

Check this code.



SELECT VBELN
       VDATU
       KUNNR
       ERDAT
  INTO TABLE IT_VBAK
  FROM VBAK
WHERE VBELN IN S_VBELN
AND KUNNR IN S_KUNNR
AND ERDAT IN S_ERDAT
AND VBTYP EQ 'C'
AND VKORG IN S_VKORG.

IF NOT IT_VBAK[] IS INITIAL.
  SELECT POSNR MATNR KWMENG
    INTO TABLE IT_VBAP
    FROM VBAP
    FOR ALL ENTRIES IN IT_VBAK
   WHERE VBELN EQ IT_VBAK-VBELN
    AND MATNR IN S_MATNR
    AND VSTEL EQ S_VSTEL.
ENDIF.

LOOP AT IT_VBAP.
  CLEAR IT_VBAK.
  READ TABLE IT_VBAK WITH KEY VBELN = IT_VBAP-VBELN.
  IF SY-SUBRC EQ 0.
    IT_FNAL-VBELN = IT_VBAK-VBELN.
    IT_FNAL-VDATU = IT_VBAK-VDATU.
    IT_FNAL-KUNNR = IT_VBAK-KUNNR.
    IT_FNAL-ERDAT = IT_VBAK-ERDAT.

    IT_FNAL-POSNR = IT_VBAP-POSNR.
    IT_FNAL-MATNR = IT_VBAP-MATNR.
    IT_FNAL-KWMENG = IT_VBAP-KWMENG.

    APPEND IT_FNAL.
    CLEAR IT_FNAL

  ENDIF.
ENDLOOP.

Regards

sailaja.