topic Re: select quirey in Application Development and Automation Discussions

select quirey

Former Member — Wed, 09 Aug 2006 09:35:13 GMT

Hi experts ,

plz help me how to write code for below given technical description

Retrieve

• LIKP.VSTEL (shipping point)

WHERE

• LIKP.VBELN = Delivery Note Number.

Retrieve

• TVST.ADNR (address number)

WHERE

• TVST.VSTEL = LIKP.VSTEL

Retrieve

• ADRC.NAME1 (name)

• ADRC.STREET (address)

• ADRC.CITY (city)

• ADRC.REGION (state)

• ADRC.POST_CODE1 (zip)

WHERE

• ADRC.ADNR = TVST.ADNR

DEA ADRC.NAME2 from address record selected for ‘Header – From’ (DEA number stored in NAME2 field only for shipping points)

regards

siri

Re: select quirey

Former Member — Wed, 09 Aug 2006 09:42:41 GMT

Hi,

l_del_no = 432423. --> The delivery no.

SELECT VBELN

VSTEL

FROM LIKP

into table it_vstel

WHERE VBELN = l_del_no.

sort it_vstel by vbeln vstel.

if not it_vstel is initial.

select vstel

adnr

from TVST

for all entries in it_vstel

into table it_adnr

where vstel = it_vstel-vstel.

endif.

sort it_adnr by vstel adnr.

if not it_adnr is initial.

select vstel

name1

street

city

region

post_code1

for all entries in it_adnr

into table it_address

from ADRC

where adnr = it_adnr-adnr.

endif.

Best regards,

Prashant

Re: select quirey

Former Member — Wed, 09 Aug 2006 09:43:50 GMT

Hi,

select vstel from LIKP into table LIKP_IT where vbeln = <Delivery note number>.

SORT LIKP_IT by VSTEL.

select ADRNR from TVST into table TVST_IT for all entries in LIKP_IT where VSTEL= LIKP_IT-VSTEL.

SORT TVST_IT by ADRNR.

select name1 street city1 region post_code1 from ADRC into table ADRC_IT for all entries in TVST_IT where ADRNR = TVST_IT-ADRNR.

Thanks.

If this helps you reward with points.

Re: select quirey

Former Member — Wed, 09 Aug 2006 09:45:16 GMT

select likp_{vstel tvst}adnr

into table itab

from likp inner join tvst

on likp_{vstel = tvst}vstel

where likp~vbeln = l_vbeln.

Loop at itab.

select name1 street city region post_code1

from adrc where adnr = itab-adnr.

endloop.

Note 100% sure of your last requirement about DEA but it seems that you just need to include name2 in the above query.

Message was edited by: Anurag Bankley

Re: select quirey

Former Member — Wed, 09 Aug 2006 09:45:47 GMT

hi,

     select vstel from likp into v_vstel up to 1 row
           
            where vbeln = (deilery note number or parameter).
 
if sy-subrc = 0.
    
     select adrnr from tvst into v_adrnr up to 1 row
             where vstel = v_vstel.

if sy-subrc = 0.
  select name1 
         street
         city 
         region
         post_code1
          into table wa_adrc
        where adrnr = v_adrnr.

endif.
endif.

Regards

Ashok P

Re: select quirey

Former Member — Wed, 09 Aug 2006 09:51:42 GMT

Hi,

Written code for 3 selects from 3 tables

<b>can you be more clear for the last line</b>

tables: likp.

select-options:s_vbeln for likp-vbeln.

data:begin of itab1 occurs 0,

vbeln like likp-vbeln,

vstel like likp-vstel,

end of itab1.

data:begin of itab2 occurs 0,

adrnr like tvst-adrnr,

vstel like tvst-vstel,

end of itab2.

data:begin of itab3 occurs 0,

name1 like adrc-name1,

street like adrc-street,

city1 like adrc-city1,

region like adrc-region,

post_code1 like adrc-post_code1,

ADDRNUMBER like adrc-ADDRNUMBER,

end of itab3.

select vbeln vstel from likp into table itab1 where vbeln in s_vbeln.

select adrnr vstel from tvst into table itab2

for all entries in itab1

where vstel = itab1-vstel.

select name1 street city1 region post_code1 from adrc into table itab3

for all entries in itab2

where ADDRNUMBER = itab2-adrnr.

Re: select quirey

Former Member — Wed, 09 Aug 2006 10:00:54 GMT

Hi,

Find the belowsaid code :

Reward points if helpful.

DATA : BEGIN OF str_addr,
        vstel LIKE likp-vstel,
        vbeln LIKE likp-vbeln,
        adnr LIKE tvst-adrnr,
        name1 LIKE adrc-name1,
        street LIKE adrc-street,
        city LIKE adrc-city1,
        region LIKE adrc-region,
        END OF str_addr.

DATA : it_adr LIKE TABLE OF str_addr WITH HEADER LINE.

SELECT l~vstel l~vbeln t~adrnr a~name1
       a~street a~city1 a~region
       INTO CORRESPONDING FIELDS OF TABLE it_adr
       FROM  likp AS l INNER JOIN
       tvst AS t ON l~vstel = t~vstel
       INNER JOIN
       adrc AS a
       ON a~addrnumber = t~adrnr.
       WHERE l~vbeln = 'XXXXXXXXX'.

check sy-subrc = 0.

Regards

Re: select quirey

Former Member — Wed, 09 Aug 2006 10:45:23 GMT

Hi,

You can write the select statment as...

select vstel from likp into v_vstel up to 1 row where vbeln = (deilery note number or parameter).

if sy-subrc = 0.
     select adrnr from tvst into v_adrnr up to 1 row  where vstel = v_vstel.
     if sy-subrc = 0.
        select name1  street
               city   region
               post_code1
           nto table wa_adrc
          where adrnr = v_adrnr.
     endif.
endif.

Hope this code solves the issue

Regards,

Richa