topic Re: Query producing Wrong Output in Application Development and Automation Discussions

Query producing Wrong Output

shahad009 — Sun, 05 Jun 2022 10:27:46 GMT

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.

Re: Query producing Wrong Output

Sandra_Rossi — Sun, 05 Jun 2022 10:33:12 GMT

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

Re: Query producing Wrong Output

venkateswaran_k — Sun, 05 Jun 2022 12:35:02 GMT

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

Re: Query producing Wrong Output

shahad009 — Mon, 06 Jun 2022 04:23:13 GMT

Thanks Sandra I understand the fact.

Re: Query producing Wrong Output

shahad009 — Mon, 06 Jun 2022 05:46:59 GMT

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

Re: Query producing Wrong Output

venkateswaran_k — Mon, 06 Jun 2022 05:53:35 GMT

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

Re: Query producing Wrong Output

shahad009 — Mon, 06 Jun 2022 06:02:38 GMT

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 .

Re: Query producing Wrong Output

venkateswaran_k — Mon, 06 Jun 2022 06:05:56 GMT

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

Re: Query producing Wrong Output

shahad009 — Mon, 06 Jun 2022 06:08:20 GMT

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.

Re: Query producing Wrong Output

shahad009 — Mon, 06 Jun 2022 06:27:49 GMT

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.

Re: Query producing Wrong Output

venkateswaran_k — Mon, 06 Jun 2022 06:32:23 GMT

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

Re: Query producing Wrong Output

shahad009 — Mon, 06 Jun 2022 06:37:12 GMT

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.

Re: Query producing Wrong Output

venkateswaran_k — Mon, 06 Jun 2022 06:39:36 GMT

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

Re: Query producing Wrong Output

shahad009 — Mon, 06 Jun 2022 06:45:33 GMT

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

Re: Query producing Wrong Output

venkateswaran_k — Mon, 06 Jun 2022 09:40:30 GMT

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?

Re: Query producing Wrong Output

venkateswaran_k — Mon, 06 Jun 2022 18:18:15 GMT

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.

Re: Query producing Wrong Output

shahad009 — Tue, 07 Jun 2022 03:53:40 GMT

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.

Re: Query producing Wrong Output

venkateswaran_k — Tue, 07 Jun 2022 05:11:04 GMT

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