problem with select

[Former Member]

Hi,

It should return me just one record but it generally returns four records:

What could be the reason?

SELECT CKMLHD~BWKEY CKMLHD~KALNR CKMLHD~MATNR CKMLPP~BDATJ CKMLPP~KALNR
       CKMLPP~LBKUM CKMLPP~MEINS CKMLPP~POPER MARC~MATNR MARC~PRCTR
       MARC~WERKS MBEW~BKLAS MBEW~KALN1 CKMLCR~BDATJ CKMLCR~CURTP
       CKMLCR~KALNR CKMLCR~POPER CKMLCR~SALK3 CKMLCR~WAERS
INTO (CKMLHD-BWKEY , CKMLHD-KALNR , CKMLHD-MATNR , CKMLPP-BDATJ
     , CKMLPP-KALNR , CKMLPP-LBKUM , CKMLPP-MEINS , CKMLPP-POPER
     , MARC-MATNR , MARC-PRCTR , MARC-WERKS , MBEW-BKLAS , MBEW-KALN1
     , CKMLCR-BDATJ , CKMLCR-CURTP , CKMLCR-KALNR , CKMLCR-POPER
     , CKMLCR-SALK3 , CKMLCR-WAERS )
FROM  CKMLHD
       INNER JOIN CKMLPP
       ON CKMLPP~KALNR = CKMLHD~KALNR
       INNER JOIN MARC
       ON MARC~WERKS = CKMLHD~BWKEY
       AND MARC~MATNR = CKMLHD~MATNR
       INNER JOIN MBEW
       ON MBEW~KALN1 = CKMLPP~KALNR 
       INNER JOIN CKMLCR
       ON CKMLCR~BDATJ = CKMLPP~BDATJ
       AND CKMLCR~POPER = CKMLPP~POPER
       AND CKMLCR~KALNR = MBEW~KALN1 
       WHERE CKMLHD~BWKEY IN SP$00002
         AND CKMLHD~MATNR IN SP$00001
         AND CKMLPP~BDATJ IN SP$00003
         AND CKMLPP~LBKUM IN SP$00006
         AND CKMLPP~POPER IN SP$00004
         AND MARC~PRCTR IN SP$00007
         AND MBEW~BKLAS IN SP$00008
         AND CKMLCR~CURTP IN SP$00005.

Thanks.

[Former Member]

Hi,

I will tell you what is happening. For example ekko(Purchase document header data) and ekpo(Purchase document item data) if you use inner join for the two tables, you will get all the items related to the purchase document number. It is like one to many relation and you will get many records not a single record.

Please ask your functional consultant what is the relation between all the tables that you are using in inner join.

[Former Member]

Hi Deniz,

4 records are satisfying the condition you gave in your select query. That is why you are getting those records. So if you want only one record, as said by others you can go with Select Single. So, when you use this Select Single the first record which satisfies the condition will be fetched and the rest are ignored. But among these if you want to select a particular record then just put another condition on which you want to select that particular record among the four records that are fetched. This will solve.

Regards,

Swapna.

[Former Member]

Hi,

If the table has any date field check the date field which might have the answer of your question.....

Regards,

Kunjal

[Former Member]

INNER JOIN CKMLPP

ON CKMLPP_{KALNR = CKMLHD}KALNR

INNER JOIN MARC

ON MARC_{WERKS = CKMLHD}BWKEY

AND MARC_{MATNR = CKMLHD}MATNR

INNER JOIN MBEW

ON MBEW_{KALN1 = CKMLPP}KALNR

INNER JOIN CKMLCR

ON CKMLCR_{BDATJ = CKMLPP}BDATJ

AND CKMLCR_{POPER = CKMLPP}POPER

AND CKMLCR_{KALNR = MBEW}KALN1

chec this code ( check each table u have written in inner join ) h many records are there based on the condition in each table )

[Former Member]

hi deniz,

you want only single record , so for that you can use "SELECT SINGLE " instead of single in you code

select single will give u single record only

example :

SELECT single CKMLHD_{BWKEY CKMLHD}KALNR CKMLHD_{MATNR CKMLPP}BDATJ CKMLPP~KALNR

CKMLPP_{LBKUM CKMLPP}MEINS CKMLPP_{POPER MARC}MATNR MARC~PRCTR

MARC_{WERKS MBEW}BKLAS MBEW_{KALN1 CKMLCR}BDATJ CKMLCR~CURTP

CKMLCR_{KALNR CKMLCR}POPER CKMLCR_{SALK3 CKMLCR}WAERS

INTO (CKMLHD-BWKEY , CKMLHD-KALNR , CKMLHD-MATNR , CKMLPP-BDATJ

, CKMLPP-KALNR , CKMLPP-LBKUM , CKMLPP-MEINS , CKMLPP-POPER

, MARC-MATNR , MARC-PRCTR , MARC-WERKS , MBEW-BKLAS , MBEW-KALN1

, CKMLCR-BDATJ , CKMLCR-CURTP , CKMLCR-KALNR , CKMLCR-POPER

, CKMLCR-SALK3 , CKMLCR-WAERS )

FROM CKMLHD

INNER JOIN CKMLPP

ON CKMLPP_{KALNR = CKMLHD}KALNR

INNER JOIN MARC

ON MARC_{WERKS = CKMLHD}BWKEY

AND MARC_{MATNR = CKMLHD}MATNR

INNER JOIN MBEW

ON MBEW_{KALN1 = CKMLPP}KALNR

INNER JOIN CKMLCR

ON CKMLCR_{BDATJ = CKMLPP}BDATJ

AND CKMLCR_{POPER = CKMLPP}POPER

AND CKMLCR_{KALNR = MBEW}KALN1

WHERE CKMLHD~BWKEY IN SP$00002

AND CKMLHD~MATNR IN SP$00001

AND CKMLPP~BDATJ IN SP$00003

AND CKMLPP~LBKUM IN SP$00006

AND CKMLPP~POPER IN SP$00004

AND MARC~PRCTR IN SP$00007

AND MBEW~BKLAS IN SP$00008

AND CKMLCR~CURTP IN SP$00005.

hope it will help you

regards

Rahul Sharma

Edited by: RAHUL SHARMA on Sep 2, 2008 11:27 AM

[former_member181995]

You said:

It should return me just one record but it generally returns four records:

than i can only suggest you to more eying on your where condition.

[Former Member]

Hi Dude,

Try with

Select Single .........UR code.

U didnt write single in uR Query.

Regards,

Ramki

[Former Member]

use Select Single