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

COUNT(*) problem

Former Member — Sun, 18 Mar 2007 09:12:38 GMT

Hello,

I have the following select statement:

SELECT COUNT( * ) FROM marc

JOIN mard ON marc_{matnr = mard}matnr

JOIN mara ON marc_{matnr = mara}matnr

JOIN eina ON mara_{matnr = eina}matnr

JOIN lfa1 ON eina_{lifnr = lfa1}lifnr

JOIN wtaddi ON marc_{matnr = wtaddi}matnr

INTO COUNT_PROMO_ZERO

WHERE marc~werks = p_plant

AND mard~labst = 0

AND eina~loekz <> 'X'

AND lfa1~sperq NOT IN ('01','02','03')

AND lfa1~sperm <> 'X'

AND mara~lvorm <>'X'

AND mara~mstae NOT IN ('01','02','03')

AND marc~matnr = p_matnr.

COUNT_PROMO_ZERO is a type i variable.

My problem is that if I run this statement for a material(matnr) with field mard~labst > 0, the count_promo_zero is 1. Why?

Re: COUNT(*) problem

RaymondGiuseppi — Sun, 18 Mar 2007 10:02:28 GMT

In your join between MARC and MARD you forget fields WERKS. Your request make a mix of MARC and MARD with different WERKS.

So i think there is a record with a WERKS <> p_plant and labst <> 0

ADD: marc_{werks = mard}werks in the JOIN MARD.

(Check all your JOIN, to get the whole set of key)

Regards

Re: COUNT(*) problem

Former Member — Mon, 19 Mar 2007 06:00:05 GMT

Hi George,

As per raymond, check your keys declaration and where clause. Do u have the parameter for matnr?

Regards,

Kumar.

Re: COUNT(*) problem

Former Member — Mon, 19 Mar 2007 06:16:26 GMT

sorry i cant get your actual query count(*) returns the how many lines are read by the select query ..for your case i think it only fetch one record so it is giing 1 to you.

regards

shiba dutta