topic Re: SQL Query not working while only input charg in Application Development and Automation Discussions

SQL Query not working while only input charg

shahad009 — Sun, 05 Jun 2022 04:55:42 GMT

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

Re: SQL Query not working while only input charg

anujawani242683 — Sun, 05 Jun 2022 05:07:50 GMT

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

Re: SQL Query not working while only input charg

anujawani242683 — Sun, 05 Jun 2022 05:22:03 GMT

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

Re: SQL Query not working while only input charg

shahad009 — Sun, 05 Jun 2022 05:35:39 GMT

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.

Re: SQL Query not working while only input charg

Sandra_Rossi — Sun, 05 Jun 2022 09:11:43 GMT

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

Re: SQL Query not working while only input charg

Sandra_Rossi — Sun, 05 Jun 2022 09:14:19 GMT

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.

Re: SQL Query not working while only input charg

shahad009 — Sun, 05 Jun 2022 09:21:56 GMT

Hi Sandra,

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

thanks

Re: SQL Query not working while only input charg

Sandra_Rossi — Sun, 05 Jun 2022 09:40:22 GMT

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.

Re: SQL Query not working while only input charg

Sandra_Rossi — Sun, 05 Jun 2022 09:43:53 GMT

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

Re: SQL Query not working while only input charg

shahad009 — Sun, 05 Jun 2022 09:52:26 GMT

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

Re: SQL Query not working while only input charg

shahad009 — Sun, 05 Jun 2022 10:02:40 GMT

hi sandra,'

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

regards

Re: SQL Query not working while only input charg

Sandra_Rossi — Sun, 05 Jun 2022 10:25:01 GMT

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>

Re: SQL Query not working while only input charg

shahad009 — Mon, 06 Jun 2022 04:56:40 GMT

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

Re: SQL Query not working while only input charg

shahad009 — Mon, 06 Jun 2022 05:57:31 GMT

hi sandra,

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

this line showing an error.

Re: SQL Query not working while only input charg

anujawani242683 — Mon, 06 Jun 2022 06:18:38 GMT

Hi Sandra,

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

Regards,

Anuja Kawadiwale

Re: SQL Query not working while only input charg

Sandra_Rossi — Mon, 06 Jun 2022 07:32:34 GMT

anujawani2426

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

Re: SQL Query not working while only input charg

Sandra_Rossi — Mon, 06 Jun 2022 07:35:35 GMT

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