topic Re: problem with join logic in Application Development and Automation Discussions

problem with join logic

Former Member — Mon, 08 Jan 2007 15:15:00 GMT

I am attaching the code that I am using to retrieve data for customers. We have two customers on the KNB1 table, each with a different company code. When I run my process, and enter a company code, I am getting both records instead of the one that matches the company code that I entered. I have tried different variations of the inner join statement between the KNA1 and KNB1 tables and also the select sttement but I cannot get to retrieve only the record for the selected company.

thanks in advance for the help

SELECT

KNA1_{KUNNR KNB1}BUKRS KNVV_{VKORG KNVV}VTWEG KNVV_{SPART KNA1}KTOKD

KNVV_{KKBER ADRC}TITLE ADRC_{NAME1 ADRC}NAME2 ADRC_{NAME3 ADRC}NAME4

ADRC_{SORT1 ADRC}SORT2 ADRC_{BUILDING ADRC}ROOMNUMBER ADRC~FLOOR

ADRC_{NAME_CO ADRC}STR_SUPPL1 ADRC_{STR_SUPPL2 ADRC}STREET ADRC~HOUSE_NUM1

ADRC_{HOUSE_NUM2 ADRC}STR_SUPPL3 ADRC_{LOCATION ADRC}CITY2 ADRC~HOME_CITY

ADRC_{POST_CODE1 ADRC}CITY1 ADRC_{COUNTRY ADRC}REGION ADRC~TIME_ZONE

ADRC_{TAXJURCODE ADRC}TRANSPZONE ADRC_{REGIOGROUP ADRC}DONT_USE_S

ADRC_{PO_BOX ADRC}PO_BOX_NUM ADRC_{POST_CODE2 ADRC}PO_BOX_LOC

ADRC_{PO_BOX_CTY ADRC}PO_BOX_REG ADRC_{POST_CODE3 ADRC}DONT_USE_P

ADRC_{LANGU ADRC}TEL_NUMBER ADRC_{TEL_EXTENS KNA1}TELF2

ADRC_{FAX_NUMBER ADR6}SMTP_ADDR ADRC_{DEFLT_COMM ADRCT}REMARK

KNA1_{LIFNR KNA1}BEGRU KNA1_{VBUND KNA1}KONZS KNA1_{BBBNR KNA1}BBSNR

KNA1_{BUBKZ KNA1}BRSCH KNA1_{LZONE KNA1}LOCCO KNA1_{STCD1 KNA1}STCD2

KNA1_{STKZA KNA1}STKZN KNA1_{STKZU KNA1}FISKN KNA1_{COUNC KNA1}CITYC

KNA1_{STCEG KNA1}TXJCD KNA1_{KNRZA KNA1}XZEMP KNZA_{EMPFD KNA1}NIELS

KNA1_{RPMKR KNA1}KUKLA KNA1_{HZUOR KNA1}BRSCH KNA1_{BRAN1 KNA1}BRAN2

KNA1_{BRAN3 KNA1}BRAN4 KNA1_{BRAN5 KNA1}UMSA1 KNA1_{UWAER KNA1}UMJAH

KNA1_{JMZAH KNA1}JMJAH KNA1_{PERIV KNA1}GFORM KNB1_{AKONT KNB1}ZUAWA

KNB1_{KNRZE KNB1}BEGRU KNB1_{FDGRV KNB1}WBRSL KNB1_{ALTKN KNB1}PERNR

KNB1_{EKVBD KNB1}ZTERM KNB1_{TOGRU KNB1}URLID KNB1_{WAKON KNB1}CESSION_KZ

KNB1_{XZVER KNB1}KULTG KNB1_{ZWELS KNB1}ZAHLS KNB1_{KNRZB KNB1}HBKID

KNB1_{WEBTR KNB1}ZGRUP KNB1_{XPORE KNB1}XVERR KNB1_{REMIT KNB1}XEDIP

KNB1_{LOCKB KNB1}XKNZB KNB5_{MAHNA KNB5}MANSP KNB5_{KNRMA KNB5}GMVDT

KNB5_{MADAT KNB5}MAHNS KNB5_{BUSAB KNB1}MGRUP KNB1_{BUSAB KNB1}XAUSZ

KNB1_{EIKTO KNB1}PERKZ KNB1_{ZSABE KNB1}TLFNS KNB1_{TLFXS KNB1}INTAD

KNB1_{KVERM KNB1}ZAMIM KNB1_{ZAMIV KNB1}ZAMIR KNB1_{ZAMIO KNB1}ZAMIB

KNVV_{BZIRK KNVV}AWAHR KNVV_{VKBUR KNVV}BEGRU KNVV_{VKGRP KNVV}VSORT

KNVV_{KDGRP KNVV}EIKTO KNVV_{KLABC KNVV}MEGRU KNVV_{WAERS KNVV}KURST

KNVV_{RDOFF KNVV}PVKSM KNVV_{KONDA KNVV}KALKS KNVV_{PLTYP KNVV}VERSG

KNVV_{AGREL KNVV}BLIND KNVV_{LPRIO KNVV}KZAZU KNVV_{VSBED KNVV}CHSPL

KNVV_{VWERK KNVV}PODKZ KNVV_{PODTG KNVV}AUTLF KNVV_{KZTLF KNVV}ANTLF

KNVV_{UEBTK KNVV}UNTTO KNVV_{UEBTO KNA1}LZONE KNA1_{BAHNE KNA1}BAHNS

KNVV_{MRNKZ KNVV}BOKRE KNVV_{PRFRE KNVV}PERFK KNVV_{PERRL KNVV}INCO1

KNVV_{INCO2 KNVV}ZTERM KNVV_{KTGRD KNVI}ALAND KNVI_{TATYP KNVI}TAXKD

KNVL_{LICNR KNVL}BELIC KNVL_{DATAB KNVL}DATBI KNVP~PARVW

KNVK_{PARNR KNVP}DEFPA KNVP~KNREF

INTO TABLE it_sap_data

FROM ( kna1 INNER JOIN knb1 ON kna1_{kunnr EQ knb1}kunnr )

LEFT OUTER JOIN knbk ON kna1_{kunnr EQ knbk}kunnr

LEFT OUTER JOIN knvk ON kna1_{kunnr EQ knvk}kunnr

LEFT OUTER JOIN adrc ON kna1_{adrnr EQ adrc}addrnumber

LEFT OUTER JOIN adrct ON kna1_{adrnr EQ adrct}addrnumber

LEFT OUTER JOIN knvv ON kna1_{kunnr EQ knvv}kunnr

LEFT OUTER JOIN knza ON kna1_{kunnr EQ knza}kunnr

LEFT OUTER JOIN knb5 ON kna1_{kunnr EQ knb5}kunnr

LEFT OUTER JOIN knvi ON kna1_{kunnr EQ knvi}kunnr

LEFT OUTER JOIN knvl ON kna1_{kunnr EQ knvl}kunnr

LEFT OUTER JOIN knvp ON kna1_{kunnr EQ knvp}kunnr

LEFT OUTER JOIN adr6 ON kna1_{adrnr EQ adr6}addrnumber

WHERE knb1~KUNNR IN so_kunnr AND

knb1~bukrs IN so_cocod AND

knb1~altkn IN so_altkn.

Re: problem with join logic

Former Member — Mon, 08 Jan 2007 15:22:19 GMT

Did you tried SELECT DISTINCT???

Greetings,

Blag.

Re: problem with join logic

Former Member — Mon, 08 Jan 2007 15:26:23 GMT

no I did not. If the user does not enter the company code, the program should return all the transactions. If the user enters the company code, only the record for that company code should be returned

Re: problem with join logic

Former Member — Mon, 08 Jan 2007 15:37:25 GMT

It's a pretty complicated SELECT. The first thing I would do is simplify it (for testing purposes) to only look at KNA1 and KNB1.

Rob

Re: problem with join logic

Former Member — Mon, 08 Jan 2007 16:06:14 GMT

I have simplified the select and found out that the problem is with the KNB5 table. this table is similar to KNB1 in that there are 2 entries for the same customer but with different company codes. Is there a way for me to select only the KNB5 transaction that matches the company code that I select. I have tried to put it in the WHERE section of the select but this is not correct.

Re: problem with join logic

Former Member — Mon, 08 Jan 2007 16:16:32 GMT

You can't add a criteria in the WHERE clause of a left-outer joinned table.

I suggest you, if you can to "iNNER JOIN" KNB5 or to exit this table from your Join.

Erwan

Re: problem with join logic

Former Member — Mon, 08 Jan 2007 16:21:36 GMT

I changed the join statement from outer join to

INNER JOIN knb5 ON kna1_{kunnr EQ knb5}kunnr

and I added the following to the WHERE statement

knb5~bukrs IN so_cocod AND

It looks like it is selecting the correct data.

Re: problem with join logic

Former Member — Mon, 08 Jan 2007 16:35:52 GMT

when I run the process with the new code for the inner join and the where statement, If I select the company code, the process works correctly, If I leave the company code blank, instead of getting the two records that I would expect, I am now getting 4 records. I am thinking that there are 2 in the KNB1 table and 2 in the KNB5 table and the inner join is causing this. Is there a way to correct this?

Re: problem with join logic

Former Member — Mon, 08 Jan 2007 17:09:35 GMT

I removed the join statement for knb5 and created a little section where I select the records separately.

thanks for all he help