topic Re: Clarification in Select syntax in Application Development and Automation Discussions

Clarification in Select syntax

manoj_kumar12 — Wed, 26 Feb 2014 09:41:27 GMT

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

Re: Clarification in Select syntax

adrian_mejido — Wed, 26 Feb 2014 09:47:09 GMT

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

Re: Clarification in Select syntax

ThomasZloch — Wed, 26 Feb 2014 09:48:43 GMT

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

Re: Clarification in Select syntax

arivazhagan_sivasamy — Wed, 26 Feb 2014 09:51:54 GMT

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

Re: Clarification in Select syntax

adrian_mejido — Wed, 26 Feb 2014 09:56:14 GMT

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

Best Regards

Re: Clarification in Select syntax

hiriyappa_myageri — Wed, 26 Feb 2014 10:19:42 GMT

u just use like in where clause with % symbol.

Re: Clarification in Select syntax

Former Member — Wed, 26 Feb 2014 11:06:47 GMT

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.

Re: Clarification in Select syntax

Former Member — Wed, 26 Feb 2014 11:20:16 GMT

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

Re: Clarification in Select syntax

manoj_kumar12 — Thu, 27 Feb 2014 02:23:44 GMT

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!

Re: Clarification in Select syntax

manoj_kumar12 — Thu, 27 Feb 2014 02:24:37 GMT

Thanks Thomas for your prompt reply.

Re: Clarification in Select syntax

manoj_kumar12 — Thu, 27 Feb 2014 02:25:28 GMT

Agree with Adrián Mejido..

Re: Clarification in Select syntax

manoj_kumar12 — Thu, 27 Feb 2014 02:26:23 GMT

Hi Hiriyappa Myageri ,

I need the selection condition for range of values.

Thanks!

Re: Clarification in Select syntax

manoj_kumar12 — Thu, 27 Feb 2014 02:26:51 GMT

Thanks,,,,Shyam Sunder Goyal

Re: Clarification in Select syntax

manoj_kumar12 — Thu, 27 Feb 2014 02:29:27 GMT

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!

Re: Clarification in Select syntax

Former Member — Thu, 27 Feb 2014 03:45:38 GMT

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

Re: Clarification in Select syntax

dibyajeeban_jena — Thu, 27 Feb 2014 05:07:40 GMT

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

Re: Clarification in Select syntax

mayur_priyan — Thu, 27 Feb 2014 05:11:30 GMT

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

Re: Clarification in Select syntax

Former Member — Thu, 27 Feb 2014 05:23:57 GMT

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.

Re: Clarification in Select syntax

manoj_kumar12 — Thu, 27 Feb 2014 08:53:40 GMT

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!

Re: Clarification in Select syntax

Former Member — Thu, 27 Feb 2014 09:11:45 GMT

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