Clarification in Select syntax

[manoj_kumar12]

Hi Friends,

I have a requirement to write a selection query based on vendor name(LFA1-NAME1). In this select statement i need to use range of values to fetch records  like below

But if i use selection statement as below the selection is not accurate it miss out some records which starts with A* or B* or C*.

select * from lfa1 into table it_lfa1 where name1 in LFA1-NAME1.

If you have any solutions pls let me know.

Thanks!

Regards,

Manojkumar Nagarajan

[adrian_mejido]

Hi Manoj,

Try with this select:

SELECT name1
   FROM lfa1
   INTO CORRESPONDING FIELDS OF TABLE lt_lfa1
   WHERE name1 BETWEEN 'A%' AND 'C%'.

Best regards

[manoj_kumar12]

Hi Adrian Mejido,

Thanks for your comments......As per your code it randomly misses some records.

Let us say...If there are 10 records in LFA1 table which starts with A*(UPPERCASE) and 3 records with B*(UPPERCASE) and 6 records with C*(UPPERCASE)...so to get an accurate result we need 19 records in the output......but by the syntax you had provided it only picks 15 records approximately and the remaining 4 records misses randomly.

Thanks!

[ThomasZloch]

Patterns don't work correctly when used You cannot use patterns in from-to ranges, try filling three single values A*, B*, C* instead.

Thomas

[manoj_kumar12]

Thanks Thomas for your prompt reply.

[arivazhagan_sivasamy]

Hi,

Write a code like below..

SELECT name1
   FROM lfa1
   INTO CORRESPONDING FIELDS OF TABLE lt_lfa1
   WHERE name1 like 'A%' or 'C%'.

Arivazhagan S

[adrian_mejido]

I think that your code is wrong because he couldn't get "NAME1" which begin by "B".

Best Regards

[manoj_kumar12]

Agree with Adrián Mejido..

[hiriyappa_myageri]

u just use like in where clause with % symbol.

[manoj_kumar12]

Hi Hiriyappa Myageri ,

I need the selection condition for range of values.

Thanks!

[hiriyappa_myageri]

Hi manoj,

tables:lfa1.

data:BEGIN OF it_lfa1 OCCURS 0,

      name1 type lfa1-name1,

   END OF it_lfa1.

select-OPTIONS name for lfa1-name1.

SELECT name1

    FROM lfa1

    INTO CORRESPONDING FIELDS OF TABLE it_lfa1

    WHERE name1 in name.

You can give multiple values in select option it fetch those values.

like A*,C*,S*.

Regards,

Hiriyappa.

[manoj_kumar12]

Yes Hiriyappa Myageri,

You are right but it will not exactly fetch all records which actually need to be fetched....Its is ...

Thanks!

[Former Member]

Agree with Thomas, Do not use pattern in range.

or if user wants to do so, you can write your code in report to modify select option.

[manoj_kumar12]

Thanks,,,,Shyam Sunder Goyal

[Former Member]

select * from lfa1 into table it_lfa1 where name1 CP LFA1-NAME1.

Also dont forget to translate A* in upper as well as in lower case.

BR,

Prakash

[manoj_kumar12]

Hi Prakash Jha,

Your code gives me syntax error. Can you give some example code using CP without syntax error and also I need to use the range value for name1 (i.e) name1 IN LFA1-NAME1.

Thanks!

[Former Member]

Hi,

Try this code.

REPLACE ALL OCCURRENCES OF '*'  IN i_lifnr WITH '%'.   "i_lifnr is my input variable

SELECT lifnr

              name1

              INTO TABLE lt_vendor_lfa1

              FROM lfa1

              WHERE lifnr LIKE i_lifnr .

Regards

[manoj_kumar12]

Hello Amaranatha Madhaba,

I'm not using input variable(PARAMETERS)....I need to use Range(SELECT-OPTIONS).....Your code will fetch only single variable.

Thanks!

[dibyajeeban_jena]

Hi Manoj .

Here is the code... its working fine ..

TABLES : LFA1 .
DATA   :  IT_LFA1 TYPE TABLE OF LFA1,
           WA_LFA1  TYPE LFA1 .
DATA   : PA_STRING TYPE STRING .

SELECTION-SCREEN: BEGIN OF BLOCK B1.
  SELECT-OPTIONS : SO_NAME   FOR LFA1-NAME1 .
SELECTION-SCREEN : END OF BLOCK B1 .


LOOP AT SO_NAME .
REPLACE ALL OCCURRENCES OF '*'  IN SO_NAME-LOW  WITH '%'.
CONCATENATE '''' SO_NAME-LOW '''' INTO SO_NAME-LOW .
MODIFY SO_NAME .
ENDLOOP .

LOOP AT SO_NAME  .
  IF PA_STRING IS INITIAL .
  CONCATENATE   'NAME1 LIKE' SO_NAME-LOW  PA_STRING INTO PA_STRING SEPARATED BY ' '.
  ELSE .
  CONCATENATE   PA_STRING 'OR NAME1 LIKE'  SO_NAME-LOW  INTO PA_STRING SEPARATED BY ' '.
  ENDIF .
ENDLOOP .

SELECT * FROM LFA1 INTO TABLE IT_LFA1 WHERE (PA_STRING) .


LOOP AT IT_LFA1 INTO WA_LFA1.


WRITE : / WA_LFA1-LIFNR , WA_LFA1-NAME1 .

ENDLOOP .

follow the above code and do ur selection accordingly .cheers..

input

Output

Regards

DJ

[manoj_kumar12]

Hi D J,

Thanks......You are giving your input value as A*, B* and C* so it fetches correctly.......But I need select-option range (means...need to give value as A* to C*). In this case this code fails.

Thanks Again!

[dibyajeeban_jena]

Hi Manoj .

Got the solution .

I tried as below  ,, its coming according to requirement.....

TABLES : LFA1 .
DATA   :  IT_LFA1 TYPE TABLE OF LFA1,
            WA_LFA1  TYPE LFA1 .
DATA   : PA_STRING TYPE STRING ,
          WA_CAR  TYPE CHAR1 ..

SELECTION-SCREEN: BEGIN OF BLOCK B1.
   SELECT-OPTIONS : SO_NAME   FOR LFA1-NAME1 .
SELECTION-SCREEN : END OF BLOCK B1 .

SO_NAME-SIGN = 'I'.
SO_NAME-OPTION = 'CP' .
SO_NAME-LOW  = SO_NAME-HIGH .
CLEAR SO_NAME-HIGH .
APPEND SO_NAME .

SELECT * FROM LFA1 INTO TABLE IT_LFA1 WHERE NAME1 IN SO_NAME ."(PA_STRING) .

SORT IT_LFA1 BY NAME1 ASCENDING .

LOOP AT IT_LFA1 INTO WA_LFA1.

WRITE : / WA_LFA1-LIFNR , WA_LFA1-NAME1 .

ENDLOOP .

Selection screen input as below - for selecting all records with name starting with  A  , B, & C .

Upper case & Lower case are different .(for the names starting with a,b ,c , .............u have  to  enter a*........c* )

Output

Regards

DJ

[mayur_priyan]

Hi,

As what Thomas mentioned even I don't think it is possible to fetch the patterns in from-to range (select-option).

Try using the SELECT SINGLE VALUES option on the Selection screen.

Eg: REPORT ZTEST_GUV_123.

TABLES: T009.

SELECT-OPTIONS: p_PERIV for T009-periv.

data: it_tbl TYPE STANDARD TABLE OF T009,

       wa_tbl TYPE T009.

SELECT * FROM t009 INTO TABLE it_tbl

   WHERE periv IN p_periv.

   LOOP AT it_tbl INTO wa_tbl.

    write: wa_tbl-periv.

   ENDLOOP.

Regards,

Mayur Priyan. S

[manoj_kumar12]

Thanks!

[Former Member]

Try this, it works.

REPLACE ALL OCCURRENCES OF '*' IN name1 WITH '%'. " Or use A% and B% instead of inputing                                                                                            " A* and B*

.

START-OF-SELECTION.

SELECT * from lfa1 into TABLE it_lfa1 where 

   name1 like pname-low OR                                                 

    name1 pname-high or                                                        " pname is select option

    name1 in pname.

[Former Member]

Declare Range table

For ex: r_name1 type range of name1_gp,

           wa_name1 like line of name1_gp.

wa_name1-option = 'CP'

wa_name1-sign = 'I'.

wa_name1-low = 'A*'.

append wa_name1 to r_name1.

Similarly for B*, C* also add to r_name1.

After that

SELECT * from lfa1 into TABLE it_lfa1 where  name1 in r_name1. 

you will get exact records. 

If user enter A* to C* in Select-options of selection then you can loop select option parameter and populate to range table

[manoj_kumar12]

Thanks!

[Former Member]

If your issue resolved.  Please update as Correct answer.  Thanks

[dibyajeeban_jena]

Hi Manoj .

Got the solution .

I tried as below  ,, its coming according to requirement.....

TABLES : LFA1 .
DATA   :  IT_LFA1 TYPE TABLE OF LFA1,
            WA_LFA1  TYPE LFA1 .
DATA   : PA_STRING TYPE STRING ,
          WA_CAR  TYPE CHAR1 ..

SELECTION-SCREEN: BEGIN OF BLOCK B1.
   SELECT-OPTIONS : SO_NAME   FOR LFA1-NAME1 .
SELECTION-SCREEN : END OF BLOCK B1 .

SO_NAME-SIGN = 'I'.
SO_NAME-OPTION = 'CP' .
SO_NAME-LOW  = SO_NAME-HIGH .
CLEAR SO_NAME-HIGH .
APPEND SO_NAME .

SELECT * FROM LFA1 INTO TABLE IT_LFA1 WHERE NAME1 IN SO_NAME ."(PA_STRING) .

SORT IT_LFA1 BY NAME1 ASCENDING .

LOOP AT IT_LFA1 INTO WA_LFA1.

WRITE : / WA_LFA1-LIFNR , WA_LFA1-NAME1 .

ENDLOOP .

Selection screen input as below - for selecting all records with name starting with  A  , B, & C .

Upper case & Lower case are different .(for the names starting with a,b ,c , .............u have  to  enter a*........c* )

Output

Regards

DJ

[Former Member]

Hi

The select option automatically generates an internal table with fields type low high etc.

So you can use select statement with that internal tables or can use for all entries to fetch all records based on the select option values.

Warm Regards

Suneesh