select statement

[Former Member]

Hi,

I need to retrrieve data from lfa1 and one field email address from adr6. How should I approach. Help me out.Final output should consists fields from lfa1 and email address from adr6. How should I correlate. Thanks in advance.

Regards,

Nagaraju

[Former Member]

Hi,

*Selecting the ADRNE from LFA1...

SELECT SINGLE ADRNR FROM LFA1 INTO I_ADRNR

WHERE LIFNR = ITAB-LIFNR.

*Passing ADRNR to ADR6 to get Email...

SELECT SMTP_ADDR INTO TABLE I_Z1EMAIL

FROM ADR6 WHERE ADDRNUMBER = I_ADRNR.

https://forums.sdn.sap.com/click.jspa?searchID=11898838&messageID=1981624

https://forums.sdn.sap.com/click.jspa?searchID=11898838&messageID=3227145

Regards

Kiran Sure

Edited by: Kiran Sure(skk) on May 18, 2008 11:54 AM

[Former Member]

Hi,

Each vendor has a address number (LFA1-ADRNR). The address is stored in table ADRC and corresponding email address in table ADR6 based on the address number.

Use this..

Select SMPT_ADDR from ADR6

into v_email

where ADDRNUMBER eq LFA1-ADRNR.

Reward points if useful.

Regards,

Satya.

[Former Member]

hi friend,

Try like this

consider

i_lfa1,i_adr6,i_final,

data : begin of i_lfa1 occurs 0,

lifnr like lfb1-lifnr,

adrnr like lfb1-adrnr,

end of i_lfa1,

data : begin of i_adr6 occurs 0,

ADDRNUMBER like adr6-ADDRNUMBER,

SMTP_ADDR like adr-SMTP_ADDR,

end of i_adr6,

data : begin of i_final occurs 0,

lifnr like lfb1-lifnr,

adrnr like lfb1-adrnr,

ADDRNUMBER like adr6-ADDRNUMBER,

SMTP_ADDR like adr-SMTP_ADDR,

end of i_final,

select lifnr adrnr from lfa1 into table

i_lfa1 for all entries in lifnr = i_final-lifnr.

if not i_lfa1[] is initial.

select addrnumber smtp_addr from adr6 into tbale

i_adr6 for all entries in i_lfa1

where addrnumber = i_lfa1-adrnr.

reward if helpfull

[Former Member]

CHECK THIS CODE

TYPES: BEGIN OF TY_FINAL,
           LIFNR TYPE LFA1-LIFNR,
           NAME1 TYPE LFA1-NAME1,
           LAND1 TYPE LFA1-LAND1,
           ORT01 TYPE LFA1-ORT01,
           ADRNR TYPE lfa1-adrnr,
           SMTP_ADDR TYPE  ADR6-SMTP_ADDR,
     END OF TY_FINAL.

TYPES: BEGIN OF TY_VEN,
           LIFNR TYPE LFA1-LIFNR,
           NAME1 TYPE LFA1-NAME1,
           LAND1 TYPE LFA1-LAND1,
           ORT01 TYPE LFA1-ORT01,
           ADRNR TYPE lfa1-adrnr,
      END OF TY_VEN.

TYPES:BEGIN OF TY_MAIL,
        ADDRNUMBER type ADR6-ADDRNUMBER,
        SMTP_ADDR TYPE  ADR6-SMTP_ADDR,
      END OF TY_MAIL.

DATA: IT_FINAL TYPE TABLE OF TY_FINAL,
      IT_VEN TYPE TABLE OF TY_VEN,
      IT_MAIL TYPE TABLE OF TY_MAIL.

DATA: WA_FINAL LIKE LINE OF it_final,
      WA_VEN LIKE LINE OF it_ven,
      WA_MAIL LIKE LINE OF it_mail.

*SELECT-OPTIONS: S_VEN FOR LFA1-LIFNR.

START-OF-SELECTION.

PERFORM F_GET_VEN.

PERFORM F_GET_DISP.

PERFORM F_GET_FINAL.

*&---------------------------------------------------------------------*
*&      Form  F_GET_VEN
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
*  -->  p1        text
*  <--  p2        text
*----------------------------------------------------------------------*
FORM F_GET_VEN .

SELECT LIFNR
       NAME1
       LAND1
       ORT01
       ADRNR
       FROM LFA1 INTO TABLE IT_VEN.
*       WHERE LIFNR IN S_VEN.
IF IT_VEN IS NOT INITIAL.

SELECT ADDRNUMBER
       SMTP_ADDR
       FROM ADR6
       INTO TABLE IT_MAIL
       FOR ALL ENTRIES IN IT_VEN
       WHERE ADDRNUMBER EQ IT_VEN-ADRNR.

ENDIF.

ENDFORM.                    " F_GET_VEN
*&---------------------------------------------------------------------*
*&      Form  F_GET_DISP
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
*  -->  p1        text
*  <--  p2        text
*----------------------------------------------------------------------*
FORM F_GET_DISP .
LOOP AT IT_VEN INTO WA_VEN.
 WA_final-lifnr = WA_VEN-LIFNR .
WA_final-name1   =  WA_VEN-NAME1.
 WA_final-land1 =  WA_VEN-LAND1 .
 WA_final-ort01 = WA_VEN-ORT01 .
read table it_mail into wa_mail with key ADDRNUMBER = wa_ven-adrnr.
if sy-subrc eq 0.
 WA_FINAL-ADRNR  = WA_MAIL-ADDRNUMBER.
 wa_final-SMTP_ADDR = wa_mail-SMTP_ADDR.

append wa_final to it_final.
ELSE.
CONTINUE.
endif.


ENDLOOP.
ENDFORM.                    " F_GET_DISP
*&---------------------------------------------------------------------*
*&      Form  F_GET_FINAL
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
*  -->  p1        text
*  <--  p2        text
*----------------------------------------------------------------------*
FORM F_GET_FINAL .

LOOP AT IT_FINAL INTO WA_FINAL.
WRITE: / WA_FINAL-LIFNR,
         wa_final-name1,
         WA_FINAL-LAND1,
         WA_FINAL-ORT01,
         WA_FINAL-ADRNR,
         WA_FINAL-SMTP_ADDR.
ENDLOOP.
ENDFORM.                    " F_GET_FINAL

REWARD IF HELPFUL