Skip to Content
author's profile photo Former Member
Former Member

how to apply cursor to achieve this:

Hi!

I need only those belnr gjahr pair (since they form composite key) from BSEG which has KTOSL = WRX more than once.

Example:

belnr = 11111; gjahr = 2013; ktosl = 'WRX'

belnr = 11111; gjahr = 2013; ktosl = 'KBS'

belnr = 11111; gjahr = 2013; ktosl = 'WRX'


belnr = 222222 ; gjahr = 2013; ktosl = 'EBS'

belnr = 222222 ; gjahr = 2013; ktosl = 'WRX'

belnr = 222222 ; gjahr = 2012; ktosl = 'WRX'


I need only those pair of "belnr, gjahr" which has more than 1 record for transaction type = 'WRX'

so final out put should be - 11111 2013

PS. i need to do this without internal table.

Thankyou much,

KS

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

5 Answers

  • Posted on May 17, 2013 at 12:17 PM
    SELECT belnr gjahr COUNT(ktosl) AS cFROM tableGROUP BY belnr gjahrHAVING COUNT(ktosl) > 1.

    Or something like that... take a look at the HAVING syntax.

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on May 17, 2013 at 12:25 PM

    Hi,

    I think you can write it like this.

    SELECT SINGLE belnr gjahr                 FROM bseg                  INTO (lv_belnr, lv_gjahr)             WHERE gjahr = ( SELECT MAX( gjahr )                                                      FROM bseg                                                    WHERE  ktosl = 'WRX' )                 AND ktosl = 'WRX'.

    This should give you first belnr for max gjahr.

    output must be like you want.

    Regards

    Tolga

    Add a comment
    10|10000 characters needed characters exceeded

    • Former Member

      Hi Tolga!

      Thankyou for reply..

      Apparently, max year is not what im looking for.. pair "belnr gjahr" forms a composite key to identify a record uniquely. So for a pair of this in BSEG, if BSEG has more than one record with ktosl = 'WRX', i need that pair... so basically, "1111 2013" is not same as "1111 2012".... and if for pair "1111 2012" BSEG has more than one entry with field KTOSL = 'WRX', i need that pair of "belnr gjahr".

      Regards,

      KS

  • Posted on May 17, 2013 at 12:31 PM

    Have you tried this?

    Select single blenr

    MAX( gjahr )

    INTO (lv_beln, lv_gjahr)

    From besg

    Where kostl = 'WRX'.

    Cheers!

    Abhinab

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on May 17, 2013 at 12:46 PM

    Example:

    BELNR | GJAHR | KTOSL

    11111 2013 WRX ****Record1

    11111 2013 KBS ****Record 2

    11111 2012 WRX *****Record 3

    22222 2013 WRX *****Record 4

    22222 2013 WRX ******Record 5

    22222 2013 VSE *******Record 6

    Output = > 22222 2013. (Since it has more than 1 record with ktosl = WRX)

    Records 4 & 5 are not same because they will have other columns as well, with different values.

    Regards,

    KS

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on May 17, 2013 at 01:32 PM

    Hi K S,

    As far as i know , that can happen only in case of belnr existing for multiple company codes:

    For example:

    BUKRS BELNR GJAHR BUZEI KOSTL

    1000 123 2013 01 WRX

    2000 123 2013 01 WRX

    3000 123 2013 01 WRX

    so make aware of what company code u r using, that would help u in fetchin belnr and gjahr directly.

    Regards,

    Azhar

    Add a comment
    10|10000 characters needed characters exceeded

    • Azhar uddin wrote:

      Hi K S,

      As far as i know , that can happen only in case of belnr existing for multiple company codes:

      For example:

      BUKRS BELNR GJAHR BUZEI KOSTL

      1000 123 2013 01 WRX

      2000 123 2013 01 WRX

      3000 123 2013 01 WRX

      so make aware of what company code u r using, that would help u in fetchin belnr and gjahr directly.

      Regards,

      Azhar

      not true, can happen for different BUZEI

      For example:

      BUKRS BELNR GJAHR BUZEI KOSTL

      1000 123 2013 01 WRX

      1000 123 2013 02 WRX

      1000 123 2013 03 WRX

      K S, I was trying to find something using sub querie, but I'll give up. Maybe you should use internal table after all (why not anyway?)

      Cheers,

      Custodio

      @zcust01

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.