topic Re: problem with select options in select query in Application Development and Automation Discussions

problem with select options in select query

Former Member — Wed, 27 Feb 2013 19:53:44 GMT

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

Re: problem with select options in select query

bishwajit_das — Thu, 28 Feb 2013 08:25:29 GMT

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

thanks,

Bishwajit.

Re: problem with select options in select query

Former Member — Thu, 28 Feb 2013 08:30:12 GMT

so check the format of matnr and your input!

Re: problem with select options in select query

former_member386202 — Thu, 28 Feb 2013 08:52:02 GMT

Hi,

Convert material first using conversion routine CONVERSION_EXIT_MATN1_INPUT.

Regards,

Prashant Patil

Re: problem with select options in select query

vinoth_aruldass — Thu, 28 Feb 2013 09:45:16 GMT

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

Re: problem with select options in select query

former_member183073 — Thu, 28 Feb 2013 09:45:29 GMT

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.

Re: problem with select options in select query

Former Member — Thu, 28 Feb 2013 10:07:24 GMT

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

Re: problem with select options in select query

RaymondGiuseppi — Thu, 28 Feb 2013 10:15:02 GMT

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

Re: problem with select options in select query

Former Member — Thu, 28 Feb 2013 10:42:29 GMT

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.

Re: problem with select options in select query

Former Member — Thu, 28 Feb 2013 11:08:03 GMT

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

Re: problem with select options in select query

Former Member — Thu, 28 Feb 2013 11:09:07 GMT

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.

Re: problem with select options in select query

Former Member — Thu, 28 Feb 2013 11:31:47 GMT

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

Re: problem with select options in select query

Former Member — Thu, 28 Feb 2013 11:49:21 GMT

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.

Re: problem with select options in select query

RaymondGiuseppi — Thu, 28 Feb 2013 13:11:22 GMT

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

Re: problem with select options in select query

Former Member — Thu, 28 Feb 2013 13:26:17 GMT

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

Re: problem with select options in select query

Former Member — Fri, 01 Mar 2013 06:54:51 GMT

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.