problem with select options in select query

[Former Member]

Hi Gurus ,

I have a problem with this select query .

When I have select options and input parameters other than Matnr is intial . It is not returning any values into the internal table. And I am not sure what the problem is .

Please help

select  matnr HERKL   into corresponding fields of table lt_marc from marc
                                                    where matnr in S_matnr
                                                    and  Beskz  = p_beskz
                                                    and  ekgrp  = P_ekgrp
                                                    and  lgpro  in s_lgpro
                                                    and  dispo  in S_dispo
                                                    and werks   = P_werks.

Many Thanks,

Bhararhi

[bishwajit_das]

Please validate the inputs ( in select-options & parameters ) whether the respective values are available in MARC table.

thanks,

Bishwajit.

[Former Member]

so check the format of matnr and your input!

[former_member386202]

Hi,

Convert material first using conversion routine CONVERSION_EXIT_MATN1_INPUT.

Regards,

Prashant Patil

[vinoth_aruldass]

hi,

check the length of the selection variable and it is properly assigned with the correct data type..

use the conversion_exit_matn1_input.

hope it helps,

Vinoth

[former_member183073]

Hi Bharathi,

I have tried using the same select statement with parameters and select-options and passing value only to the matnr, the select statement is not fetching any data.

then i used select-option for all the input fields instead of parameter with additions

                NO INTERVALS

                NO-EXTENSION

select-options: matnr for marc-matnr,

                      beskz for marc-beskz no intervals no-extension.

select  matnr herkl into corresponding fields of table lt_marc from marc
                                                    where matnr in s_matnr
                                                    and  beskz  in s_beskz

didn't understand the reason for this behavious but select-option is working.

[Former Member]

Hi Bharathi,

Check in the respective table whether that particular combination which you entered in Selection Screen is existing or not.

For eg: Values which you entered for  fields BESKZ, EKGRP,LGPRO,DISPO and WERKS are existing in the given MATNR range or not.

Sometimes taking too many parameters also lead to this type of problems

[RaymondGiuseppi]

Can you check yourself or at least post the definition of select-options (S_matnr, s_lgpro, S_dispo) and parameters (p_beskz, P_ekgrp, P_werks), did you use correct ddic reference (LIKE/TYPE) ?

Regards,

Raymond

[Former Member]

Hi Bharathi,

As Venkat suggested please check with the MARC table whether is there any entries existed in the table for that material number with Beskz ,ekgrp ,lgpro,dispo ,werks are intial.

As there is no entries existed in the table with this combination It is not returning any values into the internal table

Thanks,

Srilatha.

[Former Member]

Hi bharathi,

Your all condition are linked with  logical expression "AND"  .

SELECT ... WHERE <cond 1> AND <cond 2> ...

This condition is true if < cond1 > and < cond2 > are true.

SELECT ... WHERE <cond 1> OR <cond 2> ...

This condition is true if one or both of < cond1 > and < cond2 > are true.

" select  matnr HERKL   into corresponding fields of table lt_marc from marc

                                                    where matnr in S_matnr

                                                    and  Beskz  = p_beskz

                                                    and  ekgrp  = P_ekgrp

                                                    and  lgpro  in s_lgpro

                                                    and  dispo  in S_dispo

                                                    and werks   = P_werks.  "

Regard's

Smruti

[Former Member]

Hi ,

Pls Check in the respective table whether that particular combination which you entered in Selection Screen is existing or not.

please check with the MARC table whether there are any entries existed in the table for that material number with Beskz ,ekgrp ,lgpro,dispo ,werks are initial.

   If there are entries existing you shud get the values and also check with the declarations for type/like..

Thanks.

UK.

[Former Member]

Based on My above Post .

Example :

As above Screen Shot “BWTTY” and “XCHAR” fields  are  blank but EKGRP is '001' .

Suppose my SQL query as below …

Case # 1.

Pass  these value in CASE # 1.

S_MATNR = ‘000000000000001727’.

P_BWTTY =  BLANK VALUE .

P_XCHAR = BLANK VALUE .

SELECT  MATNR  HERKL

INTO CORRESPONDING FIELDS OF TABLE

LT_MARC FROM MARC

WHERE MATNR IN S_MATNR             “ Condition TRUE

AND BWTTY EQ P_BWTTY,                “ Condition TRUE

AND XCHAR  EQ P_ XCHAR .              “ Condition TRUE

Then my SY-subrc EQ ‘0’ .  

mean  my three Condition are true  above case so Result is TRUE and I got value in Internal table  .

Case # 2.

Pass  these value in CASE # 2.

S_MATNR = ‘000000000000001727’.

P_BWTTY =  BLANK VALUE .

P_XCHAR = BLANK VALUE .

P_EKGRP = BLANK VALUE .       “  but in Table I have a value against this Material Number

SELECT  MATNR  HERKL

INTO CORRESPONDING FIELDS OF TABLE

LT_MARC FROM MARC

WHERE MATNR IN S_MATNR                         “ Condition TRUE

AND BWTTY EQ P_BWTTY,                          “ Condition TRUE

AND XCHAR  EQ P_ XCHAR ,                      “ Condition TRUE

AND  EKGRP EQ P_EKGRP .                      “ Condition False

Then my SY-subrc EQ ‘4’ .  

mean  only three Condition are true  and one condition is false  above case , so Result must be false Reason of  logical expression “AND”  then I didn't get any value in Internal table .

Regard's

Smruti

[Former Member]

Hi Bharati,

The best way would be to compare externally and programmatically. Pass the same selection screen parameters' values into the table MARC and check if you are getting entries.

Also make sure the order of fields in the WHERE condition of the SELECT is the SAME as the order of these fields in the table MARC.

[RaymondGiuseppi]

Also make sure the order of fields in the WHERE condition of the SELECT is the SAME as the order of these fields in the table MARC.

Well why do you suggest that, not confident with the SQL optimizer ?

Regards,

Raymond

[Former Member]

Hi,

Did you check the record in marc which has the same matnr, beskz, ekgrp, lgpro, dispo and werks which you have entered??

Your query has 6 input parameters. Do it 1 by 1 and you will get the bug.

ex- first run the query

select  matnr HERKL   into corresponding fields of table lt_marc from marc

                                                    where matnr in S_matnr.

then

select  matnr HERKL   into corresponding fields of table lt_marc from marc

                                                    where matnr in S_matnr

                                                    and  Beskz  = p_beskz.

and so on.

Please do let us know if you are still facing the issue.

Regards

Purnand Dhingra

[Former Member]

Hi Bharati,

As explained by  purnand dhingra, run the select query with where clause one by one, this will help you to narrow down the records, the where and clause is sometimes tricky if not used properly,  run the select statement by appending the where clause one by one, and one thing more if you have used NO-EXTENSION NO INTERVALS for SELECT-OPTIONS validate the user input using the following reference.

AT SELECTION-SCREEN ON S_BZIRK.
   IF S_BZIRK-LOW IS NOT INITIAL.
     SELECT SINGLE * FROM T171 WHERE BZIRK = S_BZIRK-LOW.
     IF SY-SUBRC <> 0.
       MESSAGE E009(ZSD_EMS) WITH S_BZIRK-LOW.
     ENDIF.
   ENDIF.