help regarding select query

[former_member219850]

hiee gurus,

Please help me rearding this issue,

I DON'T   want to take the c~adrnr in same internal table.

Instead of that I wish to select adrnr of kna1 into different variable before below query.

And use that variable of adrnr into below query..

  SELECT a~kunnr

        c~name1 c~stras c~pstlz c~ort01 c~land1 c~telf1 c~telfx  c~adrnr

        d~name1 d~name2 d~name3 d~name4 d~city2 d~po_box d~title

*       e~smtp_addr

*       f~gbdat

FROM knb1 AS a INNER JOIN knvv AS b ON a~kunnr = b~kunnr

   INNER JOIN kna1 AS c ON a~kunnr = c~kunnr

   INNER JOIN adrc AS d ON c~ADRNR = d~addrnumber

*  INNER JOIN adr6 AS e ON c~adrnr = e~addrnumber

*  INNER JOIN knvk AS f ON a~kunnr = f~kunnr

   INTO CORRESPONDING FIELDS OF TABLE gt_cust_main

WHERE a~kunnr IN s_kunnr AND a~bukrs IN s_compco AND b~vkorg IN s_salorg AND c~ktokd IN s_accogr.

[Former Member]

Hi Darshan,

I think a variable will not be suffuciant for ADRNR result in above query.

you may get more than one address numbers (ADRNR values) according to the input given in KUNNR & BUKRS select options.

If we have more than one ADRNR values then we need to fetch all those into an internal table and use that table in the select query above.

I think above Join statement is far better than that,

Please raise if you have any concers.

thanks ,

Bhaskar

[former_member219850]

We did like this

LOOP AT S_KUNNR.

     SELECT ADRNR

      FROM KNA1  INTO TABLE GT_ADRNR

      WHERE KUNNR IN S_KUNNR.

     SELECT a~kunnr

        c~name1 c~stras c~pstlz c~ort01 c~land1 c~telf1 c~telfx "c~adrnr

        d~name1 d~name2 d~name3 d~name4 d~city2 d~po_box d~title

*       e~smtp_addr

*       f~gbdat

    FROM knb1 AS a INNER JOIN knvv AS b ON a~kunnr = b~kunnr

    INNER JOIN kna1 AS c ON a~kunnr = c~kunnr

    INNER JOIN adrc AS d ON d~addrnumber = GT_ADRNR-LV_ADRNR

*  INNER JOIN adr6 AS e ON c~adrnr = e~addrnumber

*  INNER JOIN knvk AS f ON a~kunnr = f~kunnr

    INTO CORRESPONDING FIELDS OF TABLE gt_cust_main

    WHERE a~kunnr IN s_kunnr AND a~bukrs IN s_compco AND b~vkorg IN s_salorg AND c~ktokd IN s_accogr.

ENDLOOP.

Please tell me how to should we implement proper loop???

[Former Member]

Hi ,

Firstly would like to know when u are at a time fetching based on the select-option values of kunnr  from KNA1 , what is the necessity for u to loop the values n fetch the data based on the customer values. When u can very well proceed with the select queries with out loop .

select adrnr from kna1 into table gt_adrnr where kunnr in s_kunnr .

once u get address number, for all the entries u can fetch data from individual tables n use it or else use inner join statement .