Query

[Former Member]

hello, friends.

i am trying to do SQVI query for tables KNA1 and ADRC. my intention is to display customer number, name, Street, Street 2, Street 3 and Street 4.

however, there seems to be no link between these 2 tables. what are the other tables i should join with the above and in what sequence?

many thanks.

[Former Member · Accepted solution]

Hi,

If you want to generate a report by using fields customer no, name, address..... then you can join tables KNA1 and VBPA ( partners tables) and generate the report.

Regards

Sankar

[Former Member · Accepted solution]

hello.

VBPA does not contain the Streets 2 to 4 which i need.

thanks.

[Former Member · Accepted solution]

Hi jonathan yap,

Go to table KNA1, put ur customer number and get the field - Address Number ( ADDRNUMBER )

Now take this address number and go to table ADRC

Now as per as your question

Street 2 = STR SUPPL1

Street 4 = STR SUPPL3

Street 5 = LOCATION

Hope this will solve your query

So based on these two tables your query should work.

Reward points if helpful.

Regards

Ravi

Hi John,

I think if you are in SQVI, and you have done table join for KNA1 and ADRC .

Then delete the default link that you can see by right click -> delete link

Now go to KNA1 table and find field name - ADRNR and select it and create your own manual link to tabel ADRC - field ADDRNUMBER.

This should surely work.

Reward accordingly.

Regards

Ravi

Edited by: Ravi D Mansharamani on Mar 26, 2008 2:21 PM

[Former Member · Accepted solution]

many thanks, Ravi!

[Lakshmipathi · Accepted solution]

Dear Ravi

The field ADRNR in KNA1 is not linking to ADRC and in fact, I have few months back did tried this but could not generate report.

The options left with us is that either first run SE16 / KNA1 and make a note of the datas of above field and again run SE16 / ADRC by inputting ADRNR value. The other option is to develop a zee report.

thanks

G. Lakshmipathi

[Former Member · Accepted solution]

hello, Lakshmipathi.

its working for me. i linked KNA1ADRNR to ADRC-ADDRNUMBER.

hope this works for you, too.

regards.

[Former Member · Accepted solution]

Explanation by Ravi was really good & it did help me out in taking the report.

I would like to know how can we find that table ADRC can be used for this purpose.I understand KNA1 can be identified by going to customer master and selecting the field & pressing F1, but what is thought process involved in identifying the ADRC Table.

Also what is the thought process behind finding out the two fields that can be linked between the 2 tables to get the result.

This is really interesting for me, so could you please reply with an answer.

Thank you

[Former Member · Accepted solution]

Hi,

Since this is about the tables, I would like to ask for your help.

The below is my program.

Note:

FORM 1800_GET_VEND_ADDR.

SELECT * FROM ADRC

WHERE ADDRNUMBER = ITAB-ADRNR

AND NAME1 LIFNR= ITAB-GLAC.

MOVE-CORRESPONDING LFA1 TO ITAB.

ITAB-GLAC. this get the Address because in form BSEG-LIFNR

when I add word "Select * FROM ADRC WHERE ADDRNUMBER=LFAI-ADRNR

MOVE-CORRESPONDING ADRC TO ITAB.

I generated address from tabl;e LFA1.

then if removed MOVE-CORRESPONDING LFA1 TO ITAB.

I generated nothing.

in my DAT begin of Itab.

sample:

name1 like KNA1-name1, "name

I nee to add" " in my report.

street3 like adrc-STR_SUPPL4

Another information:

ELSE.

ITAB-LOCC = 'VEND'.

PERFORM 1800_GET_VEND_ADDR.

ITAB-TOBUSAR = LFA1-NAME1. "CUSTOMER

ENDIF.

APPEND ITAB.

CLEAR ITAB.

if itab-koart = 'D'

itab-glac= bseg-kunnr.

else.

itab-glacc -bseg-lifnr.

Pls. help