Query producing Wrong Output

[shahad009]

Hi experts,

I have written a query but its not producing right output... to be more precise quantity field condition is not checked.

SELECT

a~werks,
a~lgort,
a~matnr,
a~charg,
a~ersda,
a~clabs,
a~cinsm,
a~cspem,
b~maktx,
c~meins,
concat( a~matnr, a~charg ) AS merge
FROM mchb AS a
INNER JOIN makt AS b
ON a~matnr = b~matnr
INNER JOIN mara AS c
ON a~matnr = c~matnr
WHERE a~lgort = @p_store OR
a~werks = @p_plant AND
a~matnr IN @s_matnr AND
a~charg IN @s_batch AND
( a~clabs > 0 OR a~cinsm > 0 OR a~cspem > 0 )
INTO TABLE @DATA(it_mchb).

My selection screen looks like

SELECTION-SCREEN BEGIN OF BLOCK b WITH FRAME TITLE txt-001.


PARAMETERS : p_plant TYPE t001w-werks MATCHCODE OBJECT zpl ,
p_store TYPE mchb-lgort OBLIGATORY MATCHCODE OBJECT zsl.


SELECT-OPTIONS : s_matnr FOR mara-matnr ,
s_batch FOR mska-charg MATCHCODE OBJECT zbt.


PARAMETERS : r1 AS CHECKBOX.

SELECTION-SCREEN END OF BLOCK b.

[Sandra_Rossi]

I already told you in your previous question that it's wrong due to the AND/OR priority, but your query anyway is wrong. Parentheses:

WHERE ( a~lgort = @p_store OR
        a~werks = @p_plant )
   AND ...

But it's wrong, because if the table MCHB is like this:

        LGORT  WERKS
Line 1  abc    123
Line 2  abc    456
Line 3  def    123

it won't work in case 1:

         P_STORE  P_PLANT  Expected result  Actual result      Status
Case 1   abc      123      Line 1           Lines 1, 2 and 3   KO
Case 2   abc               Lines 1 and 2    Lines 1 and 2      OK

[venkateswaran_k]

Hi

As Sandra said,

You need to clarify in following Points

1. The Storage location name - (say ABC ) - is exist in all Plants and if you give only storage location as input then

- the results will contain materials with quantity from that storage locations from all the plants

2. If you give Input Plant only - then

- the results will contain materials with quantity from all storage locations of that plant

3. If you give input both plant and storage location

- then the result will be correct as it is very specific

So If you say case 1 and 2 are okay to you then modify the SQL where clause with OR operation inside paranthesis

WHERE ( a~lgort = @p_store OR
        a~werks = @p_plant )

Or If you say case 1 and 2 are not okay then make both plant and stoage loacation as mandatory and put AND operator in SQL query (paramethesis is not required)

WHERE   a~lgort = @p_store AND
        a~werks = @p_plant 

Kindly update with your clarification with business and/or Functional consultant

Regards,

Venkat

[shahad009]

hi venkateswaran.k,

Case 1 and 3 will be ok. not 2 as plant is not a mandatory field. But ur proposed technique is producing wrong output too.

when I am giving both plant and storage location as input. Its not filtering the storage location. here is the query.

The report has only one mandatory field that is storage location so only if user gives storage location the report should run.

if applies plant material batch the data will be filtered else wil not.

SELECT
a~werks,
a~lgort,
a~matnr,
a~charg,
a~ersda,
a~clabs,
a~cinsm,
a~cspem,
b~maktx,
c~meins,
concat( a~matnr, a~charg ) AS merge
FROM mchb AS a
INNER JOIN makt AS b
ON a~matnr = b~matnr
INNER JOIN mara AS c
ON a~matnr = c~matnr
WHERE ( a~lgort = @p_store OR a~werks = @p_plant ) AND
a~matnr IN @s_matnr AND
a~charg IN @s_batch AND
( a~clabs > 0 OR a~cinsm > 0 OR a~cspem > 0 )
INTO TABLE @DATA(it_mchb).

[venkateswaran_k]

hmm okay. If the user requirement is that, then okay. I just mention this you to clarify with business with possible cases.

[shahad009]

so can i achieve it using a single query?

or i have to write two quries. for example if p_plant is initial the OR

or P_plant is not initial then AND .

[venkateswaran_k]

Yes, the query you now pasted (single query with OR operator within paranthesis) should work for you.

[shahad009]

but its not working when i am giving both plant and storage location. its pulling data of all storage location.

the reason being its an or operator between p_plant and p_store.

so i am thinking that i will use an if condition if p_plant is initial the p_store or p_plant

if p_plant is not initial p_store and p_plant.

[shahad009]

hi venkat,

I did this. Is it ok now?

IF p_plant IS INITIAL.

SELECT
a~werks,
a~lgort,
a~matnr,
a~charg,
a~ersda,
a~clabs,
a~cinsm,
a~cspem,
b~maktx,
c~meins,
concat( a~matnr, a~charg ) AS merge
FROM mchb AS a
INNER JOIN makt AS b
ON a~matnr = b~matnr
INNER JOIN mara AS c
ON a~matnr = c~matnr
WHERE ( a~lgort = @p_store OR a~werks = @p_plant ) AND
a~matnr IN @s_matnr AND
a~charg IN @s_batch AND
( a~clabs > 0 OR a~cinsm > 0 OR a~cspem > 0 )
INTO TABLE @DATA(it_mchb).

ELSE.
SELECT
a~werks,
a~lgort,
a~matnr,
a~charg,
a~ersda,
a~clabs,
a~cinsm,
a~cspem,
b~maktx,
c~meins,
concat( a~matnr, a~charg ) AS merge
FROM mchb AS a
INNER JOIN makt AS b
ON a~matnr = b~matnr
INNER JOIN mara AS c
ON a~matnr = c~matnr
WHERE a~lgort = @p_store AND a~werks = @p_plant AND
a~matnr IN @s_matnr AND
a~charg IN @s_batch AND
( a~clabs > 0 OR a~cinsm > 0 OR a~cspem > 0 )
INTO TABLE @DATA(it_mchb2).

ENDIF.

[venkateswaran_k]

IF p_plant IS INITIAL.- case then you need not specify OR a~werks = @p_plant... - you can remove that.

otherwise looks okay to me..

However, still that single query will also work for you..

It is upto you to choose either ..

[shahad009]

but that single query is not working. as i said it producing wrong output when i am giving both plant and storage in initilal screen.

My scenerio is only p_store is mandatory so only putting p_store the report should run. Also both giving p_plant and p_store report should run properly.

Hope u get my point.

[venkateswaran_k]

Oh okay,, then this If condition based sql should work

[shahad009]

Yes, but its more code .. i wanted it in an optimized way.

[venkateswaran_k]

hmm okay. As per your requirement putting in if statement is the only option I believe.

However, can you give screenshot of example - when you say the output of single query is wrong. Please give example - if we put in single query - what is the expected result and what is actually coming - so that we can narrow down where the issue is?

[venkateswaran_k]

Hi shahad009

If you want to optimize the code, then do as follows : Instead of Parameters, you change the Plant and Storage location to Select-option

SELECT-OPTIONS : 
s_lgort for mchb-lgort obligatory matchcode object zsl,
s_plant for t001w-werks matchcode object zpl

and change the query as follows (single query )

SELECT
a~werks,
a~lgort,
a~matnr,
a~charg,
a~ersda,
a~clabs,
a~cinsm,
a~cspem,
b~maktx,
c~meins,
concat( a~matnr, a~charg ) AS merge
FROM mchb AS a
INNER JOIN makt AS b
ON a~matnr = b~matnr
INNER JOIN mara AS c
ON a~matnr = c~matnr
WHERE 
a~lgort IN @s_store AND    <==== changed here
a~werks IN @s_plant AND    <==== changed here
a~matnr IN @s_matnr AND
a~charg IN @s_batch AND
( a~clabs > 0 OR a~cinsm > 0 OR a~cspem > 0 )
INTO TABLE @DATA(it_mchb).

Let me know your input.

[shahad009]

Hi venkateswaran.k,

Here i am explaining you why the single query was not working.

Note : p_store in the only mandatory field in the report. so now when i used the single query its an or operation between p_store p_plant. Right?

So when the user is giving both plant and storage location to run the report it is actually pulling all the storage location data 8100 7100.... as its( p_plnat or p_plant) in the query but if the user is only giving p_store to run the report the output is ok .

Hope u understand.

Thanks.

[venkateswaran_k]

Yes, Got it.

In that case you have to put IF statement for the Query as you mentioned earlier.. If that works, you can close this thread. Writing two queries based on If condition is okay .. We cannot optimize the code further..

Regards,Venkat

[shahad009]

Thanks Sandra I understand the fact.