topic Re: SELECT COUNT(*) error in Application Development and Automation Discussions

SELECT COUNT(*) error

Former Member — Tue, 12 Apr 2011 10:10:02 GMT

Hi All,

I am facing a problem while executing select count(*) into v_count from <table>.

Table contains more than 3000 crore (= 30 billion !) records so the select statement is failing, i think count(*) is crossing INT4 value limit.

Please let me know if we can find count(*) value of more than INT4 limit.

Regards

Munish Garg

Moderator message: please do not use local naming conventions.

Edited by: Thomas Zloch on Apr 12, 2011 6:36 PM

Re: SELECT COUNT(*) error

christian_wohlfahrt — Tue, 12 Apr 2011 10:19:34 GMT

Hi,

INT4 can store numbers between -2.147.483.648 and +2.147.483.647 ;

Then your count would be more than 10x to big (if I didn't fail to count the zeros correctly). -> Archiving? You might count for partial keys and add the numbers of several selects... I guess the count( * ) restriction is not only the local variable, but also early on the way from DB to ABAP.

Best regards, Christian

Re: SELECT COUNT(*) error

ravi_lanjewar — Tue, 12 Apr 2011 10:42:30 GMT

Hi,

You can define the type N to hold value.

Following example will help you to understand well


data: lv_numc(20) type n.
select single count(*) into lv_numc
from mara.
write: lv_numc.

Re: SELECT COUNT(*) error

christian_wohlfahrt — Tue, 12 Apr 2011 11:26:53 GMT

Hi,

in help (F1) of select - aggregates it says, the result of function count is of type INT4 and the variable should be defined accordingly -> this approach shouldn't work.

In a program, only partial counts would be possible, manually you can have a look with transactions DB02, DB20 or DB6STATS (depending on your release, that's all the same).

By the way: also SE16 would throw this error, isn't it?

BR, Christian

Re: SELECT COUNT(*) error

Former Member — Tue, 12 Apr 2011 12:22:58 GMT

I tried other ways including declaring variable of size numeric 20 but still it goes for dump !!!!!

If SQL can process the value more than INT4 limit in COUNT(*) then definitely SAP would have some way to process.....

Re: SELECT COUNT(*) error

Former Member — Tue, 12 Apr 2011 13:23:52 GMT

If you really want help, you're going to have to let us know what the dump says.

Rob