topic Re: select staments?? in Application Development and Automation Discussions

select staments??

Former Member — Mon, 21 Apr 2008 09:10:55 GMT

Hi,

For one of my requirement i need some quires.Please help me.

Queires : To retrive the name1,name2

logic : pick customer No from MHND-KUNNR and use table kna1-name1.

logic : pick customer No from MHND-KUNNR and use the table kna1-name2.

for Address Line 1 to retrive the ADRC(HOUSE_NUM1),ADRC(STREET).

logic: Pick the address number from the table KNA1 and then go to the table ADRC to pull the relevant field.

for Address Line 2 to retrive the ADRC(STR_SUPPL1 ),ADRC(STR_SUPPL2 ),ADRC(STR_SUPPL2 ).

logic: Pick the address number from the table KNA1 and then go to the table ADRC to pull the relevant field.

for contact tel number ADR2(TEL_NUMBER)

logic: Pls extract the Address number from Table T001 and then extract telephone sequence no.2 from table ADR2

Please help me how to write the select quires?

Regards,

srihitha

Re: select staments??

Former Member — Mon, 21 Apr 2008 09:14:36 GMT

Hi,

select single name1 name2 from kna1 where kunnr = mhnd-kunnr.

for fetching address:

select single * from adrc where kunnr = kna1-kunnr.

similarly you can do for t001 table....

and these should be in loop.

say loop at mhnd.

Reward If Helpful

Jagadish

Re: select staments??

Former Member — Mon, 21 Apr 2008 09:23:14 GMT

select single name1 name2 from kna1 where kunnr = mhnd-kunnr.

for fetching address:

select single * from adrc where kunnr = kna1-kunnr.

similarly you can do for t001 table....

and these should be in loop.

say loop at mhnd.

with regards

pardeep sharma

Re: select staments??

Former Member — Mon, 21 Apr 2008 09:28:36 GMT

Hi,

select name1 name2 addrnumber into table lt_kna1

from kna1

where kunnr = MHND-KUNNR .

check lt_kna1 is not initial.

select HOUSE_NUM1 STREET STR_SUPPL1 STR_SUPPL2

into table lt_adrc

from adrc

for all entries in lt_kna1

where adrnr = lt_kna1-addrnumber.

select TEL_NUMBER into table lt_adr2

from adr2

for all entries in lt_kna1

where adrnr = lt_kna1-addrnumber.

Reward points if helpful.

regards,

Ramya

Re: select staments??

Former Member — Mon, 21 Apr 2008 09:36:21 GMT

Is it required to write this quires in the loop.

Regards,

srihitha

Re: select staments??

Former Member — Mon, 21 Apr 2008 09:39:33 GMT

yes then only the data would be fetched according to kunnr wise!!!!!!!!!!!!!!

reward if useful!!!!!!

With regards

pardeep sharma

Re: select staments??

Former Member — Mon, 21 Apr 2008 09:41:33 GMT

How to write this quires in the loop statament???

Re: select staments??

Former Member — Mon, 21 Apr 2008 10:21:37 GMT

Hi Srihitha,

Please see the below piese of code this can be helpful to fetch the required data.

Note: Based on the fields in my selection screen i.e, BUKRS i have got the remaining data....in your case you may have some other key fields of MHND table so you can use them also in where clause where required.

if you could have given the fields which you will have with you from table MHND we can have INNER JOINS for some tables.

But the below queries may be helpful to you or they may atleast give you an idea to proceed.

SELECTION-SCREEN BEGIN OF BLOCK b1.

SELECT-OPTIONS: s_bukrs FOR mhnd-bukrs.

SELECTION-SCREEN END OF BLOCK b1.

TYPES: BEGIN OF ty_names,

kunnr TYPE kunnr,

name1 TYPE name1_gp,

name2 TYPE name2_gp,

adrnr TYPE adrnr,

END OF ty_names,

BEGIN OF ty_kunnr,

kunnr TYPE kunnr,

END OF ty_kunnr,

BEGIN OF ty_address,

house_num1 TYPE ad_hsnm1,

street TYPE ad_street,

str_supp1 TYPE ad_strspp1,

str_supp2 TYPE ad_strspp2,

str_supp3 TYPE ad_strspp3,

END OF ty_address,

BEGIN OF ty_telephone,

tel_number TYPE ad_tlnmbr,

adrnr TYPE adrnr,

END OF ty_telephone.

DATA: lt_names TYPE STANDARD TABLE OF ty_names,

lt_kunnr TYPE STANDARD TABLE OF ty_kunnr,

lt_address TYPE STANDARD TABLE OF ty_address,

lt_telephone TYPE STANDARD TABLE OF ty_telephone.

REFRESH: lt_names,

lt_kunnr,

lt_address,

lt_telephone.

IF NOT s_bukrs IS INITIAL.

SELECT kunnr

INTO TABLE lt_kunnr

FROM mhnd

WHERE bukrs IN s_bukrs.

IF sy-subrc EQ 0.

SELECT kunnr

name1

name2

adrnr

INTO TABLE lt_names

FROM kna1

FOR ALL ENTRIES IN lt_kunnr

WHERE kunnr = lt_kunnr-kunnr.

IF sy-subrc EQ 0.

SELECT house_num1

street

str_suppl1

str_suppl2

str_suppl3

INTO TABLE lt_address

FROM adrc

FOR ALL ENTRIES IN lt_names

WHERE addrnumber = lt_names-adrnr.

IF sy-subrc EQ 0.

do nothing

ENDIF.

SELECT tel_number

addrnumber

INTO TABLE lt_telephone

FROM adr2

FOR ALL ENTRIES IN lt_names

WHERE addrnumber = lt_names-adrnr.

IF sy-subrc EQ 0.

do nothing

ENDIF.

Regards,

Rasheed.