how to apply cursor to achieve this:

Former Member · ‎05-17-2013

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

VXLozano · ‎05-17-2013

SELECT belnr gjahr COUNT(ktosl) AS c
FROM table
GROUP BY belnr gjahr
HAVING COUNT(ktosl) > 1.

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

Former Member · ‎05-17-2013

Hi V. Lozano!

Thankyou for quick reply... Your query will give me all records for which it has any ktosl > 1. I need only those records for which count(ktosl) > 1 only when KTOSL = WRX.

Regards,

KS

VXLozano · ‎05-17-2013

You can combine the clauses WHERE and HAVING as needed:

SELECT belnr gjahr COUNT(ktosl) AS c  
FROM table  
WHERE ktosl = 'WRX'
GROUP BY belnr gjahr  
HAVING COUNT(ktosl) > 1.

You can also expand it to get all ktosl's wich accomply those conditions:

SELECT belnr gjahr ktosl COUNT(ktosl) AS c
FROM table
GROUP BY belnr gjahr ktosl
HAVING COUNT(ktosl) > 1.

Former Member · ‎05-17-2013

Hey, i tried this but even though im giving an alias 'c', it is throwing an error because of count:

TYPES: BEGIN OF itab_1,

belnr TYPE belnr,

gjahr TYPE gjahr,

ktosl TYPE i,

END OF itab_1.

DATA: it1 TYPE STANDARD TABLE OF itab_1,

wa1 TYPE itab_1.

SELECT belnr gjahr COUNT (ktosl) AS c

FROM BSEG

INTO TABLE it1

WHERE ktosl = 'WRX'

GROUP BY belnr gjahr

HAVING COUNT(ktosl) > 1.

ERROR: unknown column name "COUNT"

(using internal table it1 only to store final data).

Regards,

KS

VXLozano · ‎05-17-2013

You have an space before your COUNT sentence... it makes the SQL think your COUNT is a column and not an aggregate clause.

I think you can ignore the alias, if you want, because you don't use it... The same code you put dropping that blank and the " AS c" should do the work...

Oh, and change the clause INTO TABLE with INTO CORRESPONDING FIELDS OF TABLE or you will get a short dump.

Former Member · ‎05-17-2013

yeah i played with the spaces. still doesnt work. And we dont need a corresponding here since retrieving in same sequence.

Thankyou,

KS

VXLozano · ‎05-17-2013

DATA: mybelnr TYPE bseg-belnr,

mygjahr TYPE bseg-gjahr,

mycount TYPE i.

SELECT SINGLE belnr gjahr COUNT( * ) AS cnt

INTO (mybelnr, mygjahr, mycount)

FROM bseg

WHERE ktosl = 'WRX'

GROUP BY belnr gjahr

HAVING cnt > 1.

IF sy-subrc <> 0.

"whatever

ENDIF.

It should do the work...

Former Member · ‎05-17-2013

thats the thing you see. this just does not support this native feature of using alias in a condition.

error: Field "cnt" unknown.

And i made no spacing mistakes.

Regards & Thanks,

KS

VXLozano · ‎05-17-2013

Did you tried to remove the alias?

Delete the "AS cnt" and change the HAVING clause from "HAVING cnt" to "HAVING COUNT( * )".

tolga_polat · ‎05-17-2013

Hi,

BSEG is cluster table, so when trying your code it gives error:

Aggregate functions and the addition DISTINCT are not supported in

field lists for pooled and cluster tables.

Extualy this means : for BSEG we cant do this without internal table.

Regards

VXLozano · ‎05-17-2013

Oh crap. Then the code must retrieve all rows and we must COLLECT them manually to get a counter. Then we must work from there.

Got the idea, K S?

I have no time to code it by myself, just let me show the master plan:

1- create an internal table with the fields BELNR, GJAHR, CNT

2- fill it with BELNR, GJAHR (without the DISTINCT of course)

3- update the internal table giving value 1 to all CNT fields (wa-cnt = 1. MODIFY itab FROM wa TRANSPORTING CNT)

4- loop the table collecting the data into another table (LOOP AT itab INTO wa. COLLECT wa INTO itab2. ENDLOOP)

5- delete from the new table all rows with CNT < 2.

6- itab2 now contains the data you need.

Try to code this and if it doesn't work, give us the code and the possible errors

Former Member · ‎05-17-2013

yeah, i saw this error but wanted to test the logic. so i used bset instead, and another field for counting. And the logic didnt work because it wouldnt support alias in conditions or having....

And thankyou so much for actually giving time.

Regards,

KS

Former Member · ‎05-17-2013

Dear V,

i get the point... I saw this cluster table error and so tried this logic (using alias in condition) for bset (which is not a cluster table), with only different field for count. And then it would give the error that "cnt" (alias) is an unknown field (and yes, i carefully changed data types from bseg-belnr to bset-belnr, etc). We can get back to internal tables, but just want to do without it. SO forget bseg, point is not bseg, point is to meet such requirement without internal table.. and so i tried that last query but with error... anyways, will check what else to do..

Thanks & Regards,

KS.

VXLozano · ‎05-21-2013

There are two ways to work with data: SQL and internal tables. You can try to find more "artistic" approaches, but they will be risky, hard to find and not too much reliable.

About your tests with other tables, try to not use aliases: just put in you HAVING clause the aggregate function (like "HAVING COUNT( * ) > 1".

If you cannot tell us the reasons to not use internal tables, we cannot try to help you further... or, at least, I can not.

If it's a functional requirement, it is absurd. Just try to explain your functional team their requirement is not accomplishable (if this word exists).

If it's some sort of test/exam/interview question provide them the same answer: explain how you should try with SQL and its aggregate functions, and tell them that being BSEG a cluster table you cannot do it.

Another option should be to find an equivalent non-cluster table, but I do not know how to do it.

Best luck,

Vic

VXLozano · ‎05-21-2013

Check these threads... maybe you'll find a table to do your work with...

https://scn.sap.com/thread/1874501

http://scn.sap.com/message/373481

Former Member · ‎05-21-2013

No reason to not use internal table,, just was trying to break "internal table " monopoly.. nothing else...

Thanks for all your efforts!

Regards,

Khushboo

tolga_polat · ‎05-17-2013

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

Former Member · ‎05-17-2013

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

former_member212002 · ‎05-17-2013

Have you tried this?

Select single blenr

MAX( gjahr )

INTO (lv_beln, lv_gjahr)

From besg

Where kostl = 'WRX'.

Cheers!

Abhinab

tolga_polat · ‎05-17-2013

lol, why i use sub query for max gjahr

this is better.

Former Member · ‎05-17-2013

Hi Abhinab!

Thankyou for replying! Please see my reply to Tolga

for this discussion on why this would not be the answer.

Regards,

KS.

Former Member · ‎05-17-2013

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

tolga_polat · ‎05-17-2013

let me ask you this:

BELNR | GJAHR | KTOSL

11111 2013 WRX ****Record1

11111 2013 KBS ****Record 2

11111 2012 WRX *****Record 3

11111 2012 WRX *****Record 7

22222 2013 WRX *****Record 4

22222 2013 WRX ******Record 5

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

now which line should be output ?

Former Member · ‎05-17-2013

Record 5, last record immediately after count(hkont) > 1.

Regards,

KS

tolga_polat · ‎05-17-2013

Sorry i cant came up with solition without internal table.

Former Member · ‎05-17-2013

Hi Tolga!

Thankyou, for your efforts! that counts

Regards,

KS

Former Member · ‎05-17-2013

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

Former Member · ‎05-17-2013

Hi Azhar !

Thanks for replying! Only 1 company code here. So not taking that into picture for identifying unique records.

Regards,

KS

custodio_deoliveira · ‎05-17-2013

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