SELECT COUNT(*) error

[Former Member]

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

[christian_wohlfahrt]

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

[ravi_lanjewar]

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.

[christian_wohlfahrt]

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

[Former Member]

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.....

[Former Member]

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

Rob