Skip to Content
avatar image
Former Member

problem with select

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.

Add comment
10|10000 characters needed characters exceeded

  • Follow
  • Get RSS Feed

8 Answers

  • avatar image
    Former Member
    Sep 02, 2008 at 09:21 AM

    use Select Single

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Sep 02, 2008 at 09:22 AM

    Hi Dude,

    Try with

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

    U didnt write single in uR Query.

    Regards,

    Ramki

    Add comment
    10|10000 characters needed characters exceeded

  • Sep 02, 2008 at 09:27 AM

    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.

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Sep 02, 2008 at 09:27 AM

    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 CKMLHDBWKEY CKMLHDKALNR CKMLHDMATNR CKMLPPBDATJ CKMLPP~KALNR

    CKMLPPLBKUM CKMLPPMEINS CKMLPPPOPER MARCMATNR MARC~PRCTR

    MARCWERKS MBEWBKLAS MBEWKALN1 CKMLCRBDATJ CKMLCR~CURTP

    CKMLCRKALNR CKMLCRPOPER CKMLCRSALK3 CKMLCRWAERS

    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 CKMLPPKALNR = CKMLHDKALNR

    INNER JOIN MARC

    ON MARCWERKS = CKMLHDBWKEY

    AND MARCMATNR = CKMLHDMATNR

    INNER JOIN MBEW

    ON MBEWKALN1 = CKMLPPKALNR

    INNER JOIN CKMLCR

    ON CKMLCRBDATJ = CKMLPPBDATJ

    AND CKMLCRPOPER = CKMLPPPOPER

    AND CKMLCRKALNR = MBEWKALN1

    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

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Sep 02, 2008 at 09:28 AM

    INNER JOIN CKMLPP

    ON CKMLPPKALNR = CKMLHDKALNR

    INNER JOIN MARC

    ON MARCWERKS = CKMLHDBWKEY

    AND MARCMATNR = CKMLHDMATNR

    INNER JOIN MBEW

    ON MBEWKALN1 = CKMLPPKALNR

    INNER JOIN CKMLCR

    ON CKMLCRBDATJ = CKMLPPBDATJ

    AND CKMLCRPOPER = CKMLPPPOPER

    AND CKMLCRKALNR = MBEWKALN1

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

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Sep 02, 2008 at 09:30 AM

    Hi,

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

    Regards,

    Kunjal

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Sep 02, 2008 at 09:30 AM

    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.

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Sep 02, 2008 at 09:40 AM

    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.

    Add comment
    10|10000 characters needed characters exceeded