topic Re: Help on SELECT Query - count in Application Development and Automation Discussions

Help on SELECT Query - count

Former Member — Tue, 27 Apr 2010 08:55:12 GMT

Hi Gurus,

I am new to Abap and have a specific requirement.

I have attached my coding below.

I need to count the number of exidv in my internal table. Now my coding displays 10 exidvs correctly, but not counting as 10.

Pls help.

Coding follows.

Types: begin of t_final,

tknum type vttk-tknum,

vbeln type vttp-vbeln,

wadat type likp-wadat, bolnr type likp-bolnr, btgew type likp-btgew, volum type likp-volum, gewei type likp-gewei,

vgbel type lips-vgbel,

name1 type adrc-name1," vbeln type vbpa-vbeln,

street type adrc-street, city1 type adrc-city1, post_code1 type adrc-post_code1,

exidv type vekp-exidv,

count type n length 3,

end of t_final.

Data: it_final type t_final occurs 0,

wg_final type t_final.

data: exidv_chk type vekp-exidv, count type n length 3.

select a_{tknum b}vbeln

c_{wadat c}bolnr c_{btgew c}volum c~gewei

d~vgbel

f_{street f}name1 f_{city1 f}post_code1

h~exidv

count( distinct h~exidv )

from vttk as a inner join vttp as b on a_{tknum = b}tknum

inner join likp as c on c_{vbeln = b}vbeln

inner join lips as d on c_{vbeln = d}vbeln

inner join vbpa as e on d_{vgbel = e}vbeln

inner join adrc as f on e_{adrnr = f}addrnumber

inner join vepo as g on c_{vbeln = g}vbeln

inner join vekp as h on g_{venum = h}venum

into corresponding fields of table it_final where a_{tknum = g_tknum and e}parvw = 'WE'

group by a_{tknum b}vbeln c_{wadat c}bolnr c_{btgew c}volum c_{gewei d}vgbel f_{street f}name1 f_{city1 f}post_code1 h~exidv.

Re: Help on SELECT Query - count

vallamuthu_madheswaran2 — Tue, 27 Apr 2010 09:43:57 GMT

In select query remove the count.

after select query use the following.

DELETE itab WHERE exidv is initial.

data: num type i.

DESCRIBE TABLE ITAB LINES num.

num ==> count of the internal table.

Thanks & Regards,

Vallamuthu.M

Re: Help on SELECT Query - count

Former Member — Tue, 27 Apr 2010 10:01:56 GMT

Thanks Vallamuthu.

But this method ultimately counts the number of lines of the internal table and not exidv.

I mean if internal table contains 10 exidvs in which 2 are repeating, I need the count as 8. But this method counts as 10 which is size of internal table.

Any other suggestions pls...

Re: Help on SELECT Query - count

Former Member — Tue, 27 Apr 2010 10:16:45 GMT

Hi,

In the above code you can add the following lines :-

DELETE itab WHERE exidv is initial.

sort itab with exidv.

delete adjascent duplicates from itab comparing exidv.

data: num type i.

DESCRIBE TABLE ITAB LINES num.

num ==> count of the internal table.

This will give you the exact count of exidv without repetitions.

Hope this helps.

Regards,

Samreen.

Re: Help on SELECT Query - count

Former Member — Tue, 27 Apr 2010 10:20:41 GMT

Well then you could declare a separate internal table for the exidv values alone and make the count.

after the select statement,


data: begin of itab1 occurs 0,
         exidv  type exidv .
        end of itab1.

loop at itab.
itab-exidv  = itab1-exidv .
append itab1.
clear itab1.
endloop.

delete adjacent duplicates from itab1.

describe table itab1 lines l1.

Vikranth

Re: Help on SELECT Query - count

Former Member — Tue, 27 Apr 2010 10:21:25 GMT

Hi,

Please refer reply down.

Regards,

Smart Varghese

Re: Help on SELECT Query - count

Former Member — Tue, 27 Apr 2010 10:23:01 GMT

Hi,

Do like this after your select statement with out the COUNT option.

SORT itab BY exidv. " itab the internal table having the selected records

DELETE itab WHERE exidv IS INITIAL. " Deletes lines where there is no exidv

DELETE ADJACENT DUPLICATES FROM itab COMPARING exidv.

count = lines( itab ). " Here the count will be stored by the number of unique exidv

Note: Instead of COUNT you can use DESCRIBE to get the lines of ITAB

Hope this may help you.

Regards,

Smart Varghese

Re: Help on SELECT Query - count

Former Member — Tue, 27 Apr 2010 10:36:03 GMT

Thanks everyone,

Now the issue is solved.

But anyone know why the count in select query doesnot work? If it is wrong it should have given syntax error. But its not.

Can someone explain?

Again thanks for timely help.