SQL Query not working while only input charg

[shahad009]

Hi experts,

I wrote a simple query

SELECT
werks,
lgort,
matnr,
charg,
ersda,
clabs,
cinsm,
cspem,
concat( matnr, charg ) AS merge
FROM mchb
WHERE lgort = @p_store AND
werks = @p_plant AND
matnr IN @s_matnr AND
charg IN @s_batch AND
( clabs > 0 OR cinsm > 0 OR cspem > 0 )

INTO TABLE @DATA(it_mchb).

But its not fetching any data while I am only giving storage location in the selection screen. If i give plant and storage location the query is working fine. Can anyone tell me why this is happening?

regards

[anujawani242683]

Hi,

Please try below query.

SELECT
werks,
lgort,
matnr,
charg,
ersda,
clabs,
cinsm,
cspem,
concat( matnr, charg ) AS merge
FROM mchb
WHERE lgort = @p_store OR
werks = @p_plant AND
matnr IN @s_matnr AND
charg IN @s_batch AND
( clabs > 0 OR cinsm > 0 OR cspem > 0 )
INTO TABLE @DATA(it_mchb).

Regards,

Anuja Kawadiwale

[anujawani242683]

Hi Shahad,

You have written the AND condition in where clause.

Previously it was with AND condition. If you want plant and storage location both needs to be given then only give AND condition between them. And if you want either one of them if entered then also query should work then use OR between them.

Previously code was like

Where lgort = @p_store AND werks = @p_plant

[shahad009]

Hi Anuja,

Understood. Storage location is the only mandatory field in my report so when i put value only in storage location and the rest of the fields were empty AND was not working . Hence Or worked.

Thanks.

[Sandra_Rossi]

Wrong solution, it won't work if the user enters incompatible P_STORE and P_PLANT values.

Instead you should use range tables (LGORT IN RANGE_LGORT). See my answer.

[Sandra_Rossi]

shahad009

First reason it's wrong is because the current solution means:

WHERE lgort = @p_store
   OR ( werks = @p_plant AND
        matnr IN @s_matnr AND
        charg IN @s_batch AND
        ( clabs > 0 OR cinsm > 0 OR cspem > 0 ) )
INTO TABLE @DATA(it_mchb).

But even if you fix the parentheses:

WHERE ( lgort = @p_store OR werks = @p_plant ) AND
        matnr IN @s_matnr AND
        charg IN @s_batch AND
        ( clabs > 0 OR cinsm > 0 OR cspem > 0 ) )
INTO TABLE @DATA(it_mchb).

the result will be wrong, for instance we have table MCHB like this:

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

selected data will be wrong depending on user input:

         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

EDIT: considering that P_STORE is mandatory.

[shahad009]

Hi sandra.rossi ,

I have used parenthesis like this and its working fine for me.

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

[Sandra_Rossi]

shahad009 I was considering that the test case 1 was not as expected but it's up to you of course.

[Sandra_Rossi]

EDIT: taking into account that P_STORE is mandatory (only P_PLANT is optional).

As said previously, the condition with WERKS is wrong.

Prefer using range like below, if you have WERKS IN RANGE and the range is empty, it's like having no condition on WERKS :

DATA: range_werks LIKE RANGE OF p_plant.

range_werks = COND #( WHEN p_plant IS NOT INITIAL 
                      THEN VALUE #( ( sign = 'I' option = 'EQ' low = p_plant ) ) ).

SELECT werks, lgort, matnr, charg, ersda, clabs, cinsm, cspem,
       concat( matnr, charg ) AS merge
    FROM mchb
    WHERE lgort = @p_store AND
          werks IN @range_werks AND
          matnr IN @s_matnr AND
          charg IN @s_batch AND
          ( clabs > 0 OR cinsm > 0 OR cspem > 0 )
INTO TABLE @DATA(it_mchb).

[shahad009]

Hi Sandra,

According to my requirement these two has to be parameters not select-options.

thanks

[Sandra_Rossi]

P_STORE and P_WERKS are "PARAMETERS", not "SELECT-OPTIONS". Don't confuse Select-Options and Range Tables.

With the solution I proposed, the result will be correct, for instance we have table MCHB like this:

        LGORT  WERKS
Line 1  abc    123
Line 2  abc    456

selected data will be correct whatever user input:

         P_STORE  P_PLANT  Produced WHERE with range tables    Expected result  Actual result  Status
Case 1   abc      123      lgort = 'abc' and werks = '123'     Line 1           Line 1         OK
Case 2   abc               lgort = 'abc'                       Lines 1 and 2    Lines 1 and 2  OK
Case 3            123      werks = '123'                       Line 1           Line 1         OK

[shahad009]

Hi sandra.rossi,

Can You pls correct me with the 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 = @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).

[shahad009]

hi sandra,'

lgort can;t be empty.. its a mandatory field.

regards

[Sandra_Rossi]

Answer simplified to consider that P_STORE is mandatory.

For instance we have table MCHB like this:

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

selected data will be as below:

         P_STORE  P_PLANT  Produced WHERE with range tables    Expected result   Actual result    Status
Case 1   abc      123      lgort = 'abc' and werks = '123'     Line 1            Line 1           OK
Case 2   abc               lgort = 'abc'                       Lines 1 and 2     Lines 1 and 2    OK<br>

[shahad009]

hi sandra,

range_werks = COND #( WHEN p_plant IS NOT INITIAL THEN ( sign = 'I' option = 'EQ' low = p_plant ) ).

this line showing an error.

[anujawani242683]

Hi Sandra,

For me also this code is showing error. Not working.

Regards,

Anuja Kawadiwale

[Sandra_Rossi]

anujawani2426

Thanks for notifying. Sorry, I do the same kind of "typo" all the time. ABAP fixed.