COUNT(*) problem

[Former Member]

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?

[RaymondGiuseppi]

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

[Former Member]

Hi George,

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

Regards,

Kumar.

[Former Member]

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