topic Re: Select query join problem in Application Development and Automation Discussions

Select query join problem

Former Member — Tue, 22 Feb 2011 10:28:46 GMT

hey guys,

i've used a select query to get details from KNA1, KNVV, ADRC, KNVP, pa0002. tables by joining. but i'm getting only about 170 records. there are more than 3000 records in the database. for each tables. here is my select query

SELECT KNA1_{KUNNR KNA1}NAME1 KNA1_{NAME2 ADRC}STR_SUPPL1 ADRC_{STR_SUPPL2 ADRC}STR_SUPPL3 ADRC_{CITY1 KNVV}VWERK KNVP_{PERNR PA0002}VORNA PA0002_{NACHN KNVV}VKBUR KNVV_{KDGRP KNA1}STCD1 KNA1~STCEG

INTO CORRESPONDING FIELDS OF TABLE IT_FINAL FROM KNA1

INNER JOIN KNVV ON KNA1_{KUNNR = KNVV}KUNNR

INNER JOIN ADRC ON KNA1_{ADRNR = ADRC}ADDRNUMBER

INNER JOIN KNVP ON KNA1_{KUNNR = KNVP}KUNNR

INNER JOIN PA0002 ON KNVP_{PERNR = PA0002}PERNR

WHERE KNA1_{KUNNR IN S_KUNNR AND KNVV}VWERK in P_VWERK AND PA0002_{PERNR IN S_PERNR AND KNVV}VKBUR IN S_VKBUR AND KNVV~KDGRP IN S_KDGRP.

can anyone tell me the problem in the above query.

thanks.

Re: Select query join problem

Former Member — Tue, 22 Feb 2011 10:54:29 GMT

please help me someone..

Re: Select query join problem

Former Member — Tue, 22 Feb 2011 11:00:42 GMT

First avoid joining more than 3 table

instead you can use for all entries.

search on for all entries

it will surely resolve your issue.

thanks

Lalit Gupta

Re: Select query join problem

Former Member — Tue, 22 Feb 2011 11:01:26 GMT

HI,

don't know why your join is not working but you can analyse that.

Use SQVI (Quick viewer) and join the first 2 tables and view the result with a data record that is veryfied to be ok.

Now add always one additional table to the join and check which join causes and why you loose the data record.

Best regards, Christian

Re: Select query join problem

Former Member — Tue, 22 Feb 2011 11:02:46 GMT

how to search on for all entries? can u explain it further more?

Re: Select query join problem

Former Member — Tue, 22 Feb 2011 11:15:26 GMT

you are using tables KNA1, KNVV, ADRC, KNVP, pa0002.

Create internal table for each table mentioned above with all the primary keys and find the links between them

then select data in one table and select data from another table into your internal table for all the entries exists in firs internal table.

go for below links.

http://www.sapdev.co.uk/abap/commands/select_forallentries.htm

http://wiki.sdn.sap.com/wiki/display/ABAP/FORALLENTRIES-Home+page

http://help.sap.com/abapdocu_70/en/ABENWHERE_LOGEXP_ITAB.htm

Thanks

lalit

Re: Select query join problem

Former Member — Tue, 22 Feb 2011 13:54:14 GMT

Dear Christian ,

Could you please tell me , how to check the entries in the Transaction 'SQVI' by using the Data record.

Thanking You All.