topic Re: select count(*) from in Application Development and Automation Discussions

select count(*) from

glittle_tjc — Mon, 02 Feb 2009 08:58:56 GMT

Hello experts

I use the following statements to obtain the number of entries in a db table matching my criteria:

data: lv_extractrows TYPE i.

SELECT COUNT(*) FROM (P_TABNAME) BYPASSING BUFFER

WHERE (cond_syntax).

lv_extractrows = sy-dbcnt.

What is strange is that this works fine for large numbers of entries but when it runs with a cond_syntax that should return 2 entries it returns 0 entries...(sy-dbcnt = 0).

If I check in SE11 'number of entries' for cond_syntax it returns 2.

Can anyone say why the code works fine for 12k entries but not for 2?

Thanks

Re: select count(*) from

I355602 — Mon, 02 Feb 2009 09:01:51 GMT

Hi,

Try it this way:


data: lv_extractrows TYPE i.

SELECT COUNT(*)
FROM (P_TABNAME)
INTO (lv_extractrows)
WHERE (cond_syntax).

Hope this solves your problem.

Thanks & Regards,

Tarun Gambhir

Re: select count(*) from

SuhaSaha — Mon, 02 Feb 2009 09:04:54 GMT

Hello Graham,

What is the value in cond_syntax at run-time for which it is giving an error ?

Can you share this detail with us?

BR,

Suhas

Re: select count(*) from

glittle_tjc — Mon, 02 Feb 2009 09:06:01 GMT

Hi Tarun

Thanks for the code - I already tried this way originally but it doesn't work either.

I changed it to the way above after looking at the code in SE11.

The problem is the select doesn't return the value 2 (it returns sy-dbcnt = 0)

Graham

Re: select count(*) from

Former Member — Mon, 02 Feb 2009 09:07:42 GMT

This may be beacause of any errors in the condition in the cond_syntax. some thing may be missed in that..can pls specify the code. ie,values of P_TABNAME and cond_syntax.

Re: select count(*) from

glittle_tjc — Mon, 02 Feb 2009 09:09:03 GMT

Hi Suhas

The vales are filled depending on the call of the routine.

In this case P_TABNAME is BSEG and for the testing the values in cond_syntax are:

BELNR EQ 0100000000 AND BUKRS EQ 0001 AND BUZEI GE 001 AND GJAHR EQ 1995

If I try with BUKRS EQ 0001 AND GJAHR EQ 1995 I get 0 entries back when I expect 2

If I try with BUKRS EQ 1000 AND GJAHR EQ 1995 I get 12k entries back when I expect 12K - so correct.

thanks

Graham

Re: select count(*) from

Former Member — Mon, 02 Feb 2009 09:17:24 GMT

The SELECT statement has to return the dbcount.

You just Make sure that WHERE condition is passed on properly.

You can also check the DB Table for which you want to see the count(SE11) and see if the your condition is working here for at least one value.

Re: select count(*) from

Former Member — Mon, 02 Feb 2009 09:18:28 GMT

Hi Graham,

In below statement

BUKRS EQ 0001 AND GJAHR EQ 1995

when you fill condition for BUKRS make sure that it is '0001' and not '1'

Regards,

Manoj Kumar P

Re: select count(*) from

glittle_tjc — Mon, 02 Feb 2009 09:20:25 GMT

Hi Yogesh

Yes, this is what I don't understand.

Using the same code with the same style cond_syntax, one case returns the correct value and the other not.

In SE11 it returns the correct values all the time.

This makes me think I've missed out something somewhere but I have no idea what.

Thanks

Graham

Re: select count(*) from

ThomasZloch — Mon, 02 Feb 2009 09:21:35 GMT

It's probably because BUKRS = 0001 is selecting effectively as BUKRS = 1, so try with BUKRS = '0001'.

Thomas

Re: select count(*) from

former_member222860 — Mon, 02 Feb 2009 09:21:52 GMT

Hi,

Pass the values of BUKRS and BELNR with single( ' ) quotes like '0001'.

Re: select count(*) from

glittle_tjc — Mon, 02 Feb 2009 09:22:44 GMT

Hi Manoj

It is 0001, the line in the post is a direct cut and paste from debugging.

Maybe I should try it as '0001'.

Thanks

Graham

Re: select count(*) from

glittle_tjc — Mon, 02 Feb 2009 09:23:19 GMT

Hi Mahesh

I'll try it now.

Thanks

Graham

Re: select count(*) from

glittle_tjc — Mon, 02 Feb 2009 09:25:51 GMT

Hi Thomas, Mahesh

Excellent!

I just put in some ' ' in debugging and it works.

Thanks

Graham