topic Re: Select statement with wild card entry problem in Application Development and Automation Discussions

Select statement with wild card entry problem

Former Member — Wed, 29 Jul 2009 11:42:24 GMT

Hello Friends,

Am writing a select query on LFA1 to select the vendors matching a particular pattern. I have used the like operation and i face a particular situation here

For eg, Am passing the string concatenated with '%' to the like operation in where.


data: p_lifnr like lfa1-lifnr, 
         v_lifnr(10) type c.

concatenate  p_lifnr '%' into v_lifnr.

select * from LFA1 into itab where lifnr like v_lifnr.

In the above code, if there is a vendor '0000029123' and if i pass '0000029%' , the select will fetch the data. But at the same time if there is a vendor '0002965421' it wont work.

I would like to know any suggestions as to how to select all the records if just pass '29%' which should select both '0000029123' and '0002965421'.

Note: Also am using this select within a function module with p_lifnr as the import parameter. Thus i cannot use ranges here.

Thanks in advance.

Regards,

Vik

Re: Select statement with wild card entry problem

Former Member — Wed, 29 Jul 2009 11:45:41 GMT

try '0%29%'

Re: Select statement with wild card entry problem

former_member222860 — Wed, 29 Jul 2009 11:50:09 GMT

concatenate '%' at both sides

Check this code:

data: p_lifnr like lfa1-lifnr,
         v_lifnr(10) type c.

data: begin of itab occurs 0.
        include structure lfa1.
data:      end of itab.

p_lifnr = '29'.

concatenate  '%' p_lifnr '%' into v_lifnr.

select * from LFA1 into table itab where lifnr like v_lifnr.

loop at itab.
write:/ itab-lifnr.
endloop.

Re: Select statement with wild card entry problem

Former Member — Wed, 29 Jul 2009 11:53:00 GMT

Hi Gary,

By using '0%29%', it will fetch all the records containing '29' anywhere in it. I would like to select only those starting with '29' with zero's leading it depending on the length of the value.

Regards,

Vik

Re: Select statement with wild card entry problem

Former Member — Wed, 29 Jul 2009 11:54:52 GMT

Hi Mahesh,

if i concatenate '%' at both sides, it will fetch all records which contains the pattern. i would like to select only those starting with the entered pattern.

Regards,

Vik

Re: Select statement with wild card entry problem

Former Member — Wed, 29 Jul 2009 11:57:46 GMT

Try this.

tables: lfa1.
data: itab type standard table of lfa1 with header line.
data: p_lifnr like lfa1-lifnr,
         v_lifnr(10) type c.

p_lifnr = '00000034'.

concatenate  p_lifnr '%' into v_lifnr.

select * from LFA1 into table itab where lifnr like v_lifnr.

loop at itab.
write: itab-lifnr.
endloop.

Re: Select statement with wild card entry problem

Former Member — Wed, 29 Jul 2009 12:06:15 GMT

Hi Balu,

If i pass '34' i want it to fetch records for '00034%' as well as '034%'. Any suggestions for this?

Regards,

Vik

Re: Select statement with wild card entry problem

Former Member — Wed, 29 Jul 2009 12:17:04 GMT

Hi Vikred,

I jave a suggestion for you.

Just select all the vendors from vendor master containing '29' within it.

Use loop and inside do as follows:

use a FM : CONVERSION_EXIT_ALPHA_OUTPUT.

It will remove the leading zeroes from the vendor Code.

After that you can check for first two characters of the vendor codes.

If lifnr(2) = '29', then ok other wise delete the records from your internal table.

I think it will work.

Regds,

Anil

Re: Select statement with wild card entry problem

sridhar_meesala — Wed, 29 Jul 2009 12:20:50 GMT

Hi,

I am not sure it works or not, but try this

'_ _ _ _ _24%'.

% for a sequence of any characters

_ for a single character.

Thanks,

Sri.

Re: Select statement with wild card entry problem

Former Member — Wed, 29 Jul 2009 12:32:00 GMT

Hi Anil,

Thank you for your suggestion.


If lifnr(2) = '29', then ok other wise delete the records from your internal table.

But here if i enter '293' as my pattern to search, i cannot use just if lifnr(2). The pattern to search length may vary. Is there any possiblity to use LOOP with '29%' or '293%' in where so that i can move them to a final internal table?

Regards,

Vik

Re: Select statement with wild card entry problem

Former Member — Wed, 29 Jul 2009 12:39:46 GMT

Hi ,

basically what u want is that , u can achieve the functionality like going to LFA1 table and giving input as 34* ,034* ,0034,00034.... for LIFNR ,rt ..!!

For this

You will have to create ranges any how..Since u r saying , you cannot use ranges , so just check these Function modules

Think they might be helpful .

CONVERSION_EXIT_ALPHA_RANGE_I

CONVERSION_EXIT_ALPHA_RANGE_O

Regards ,

Rajesh Kumar

Re: Select statement with wild card entry problem

Former Member — Wed, 29 Jul 2009 12:41:23 GMT

Yes dear ,

rightly saying.

Thats why i am telling you to check only first two characters from left side of lifnr within the loop.

Our purpose is that first two characters should be '29', if it is not any '0'.

So within loop when you check for first two characters after removing the leading zeroes, you must have '29'

So when you check itab-lifnr(2) = '29', it will check all the record, does not matter what after that.

You try once and let me know.

Please find a simple example:

tables: lfa1.

data : p_lifnr like lfa1-lifnr value '00029'.

data : q_lifnr like lfa1-lifnr value '00291'.

data : r_lifnr like lfa1-lifnr value '02934'.

CALL FUNCTION 'CONVERSION_EXIT_ALPHA_OUTPUT'

EXPORTING

INPUT = p_lifnr

IMPORTING

OUTPUT = p_lifnr .

CALL FUNCTION 'CONVERSION_EXIT_ALPHA_OUTPUT'

EXPORTING

INPUT = q_lifnr

IMPORTING

OUTPUT = q_lifnr .

CALL FUNCTION 'CONVERSION_EXIT_ALPHA_OUTPUT'

EXPORTING

INPUT = r_lifnr

IMPORTING

OUTPUT = r_lifnr .

write:/ p_lifnr(2).

write:/ q_lifnr(2).

write:/ r_lifnr(2).

Output:

Regds,

Anil

Edited by: Anil Katoch on Jul 29, 2009 2:41 PM

Re: Select statement with wild card entry problem

Former Member — Wed, 29 Jul 2009 12:43:19 GMT

Hi,

u can set the value of '2' dynamically...

something like

length = strlen(lifnr).

if selected_data(length) = lifnr, then pass it, else delete it.

Thanks,

Bikash

Re: Select statement with wild card entry problem

Former Member — Wed, 29 Jul 2009 12:53:11 GMT

Hi Bikash and Anil,

Its working now.

Thanks,

Vik

Re: Select statement with wild card entry problem

Former Member — Wed, 29 Jul 2009 14:24:40 GMT

Note: Also am using this select within a function module with p_lifnr as the import parameter. Thus i cannot use ranges here.

You say that a range table is not an option because this is a function module. This is not a restriction at all. A range table can be set up a then used in your where clause.


ranges: r_lifnr  for lfa1-lifnr.
.....
.....
r_lifnr-sign = 'I'.
r_lifnr-option = 'CP'.
concatenate p_lifnr '*' into r_lifnr-low,
append r_lifnr.

select * from LFA1 into itab where lifnr in r_lifnr.

Or am I missing something?

Edited by: Larry Browning on Jul 29, 2009 9:25 AM

Edited by: Larry Browning on Jul 29, 2009 9:35 AM

Re: Select statement with wild card entry problem

Former Member — Wed, 29 Jul 2009 14:50:07 GMT

Hi Larry,

I was under the impression that ranges are associated with a selection screen like select-options and parameters. So i thought it cannot be used in a function module as a import parameter.

So you mean to say ranges can be used in function modules also?

Regards,

Vik

Re: Select statement with wild card entry problem

Former Member — Wed, 29 Jul 2009 15:31:07 GMT

Hi Vik,

If you are selecting all the data and looping to filter out you are probebly getting more data than you need which costs you on the performance. If you are just using the ranges table with only 29* as suggested above then you wont get all the data. SO what you will have to do is declare a ranges and use all possible values that you want like below:

Data: lr_lifnr like rsranges,

ls_lifnr like line of lr_lifnr.

ls_lifnr-SIGN = 'I'.

ls_lifnr-OPTION = 'CP'.

ls_lifnr-low = '000000029*'.

append ls_lifnr to lr_lifnr.

ls_lifnr-low = '00000029*'.

append ls_lifnr to lr_lifnr.

ls_lifnr-low = '0000029*'.

append ls_lifnr to lr_lifnr.

ls_lifnr-low = '000029*'.

append ls_lifnr to lr_lifnr.

ls_lifnr-low = '00029*'.

append ls_lifnr to lr_lifnr.

ls_lifnr-low = '0029*'.

append ls_lifnr to lr_lifnr.

ls_lifnr-low = '029*'.

append ls_lifnr to lr_lifnr.

ls_lifnr-low = '29*'.

append ls_lifnr to lr_lifnr.

ls_lifnr-OPTION = 'EQ'.

ls_lifnr-low = '0000000029'.

append ls_lifnr to lr_lifnr.

Then use the select statement with the where clause "lifnr IN lr_lifnr."

This will ensure that you get anything that starts with 29. The method of adding can be done in a loop as well. Hope this helps ...I dont know if you still need it.

Best regards,

Kazmi

Re: Select statement with wild card entry problem

Former Member — Wed, 29 Jul 2009 16:11:03 GMT

So you mean to say ranges can be used in function modules also?

Yes, a select-option is nothing more than a range table.

I think that Kazmi is on the right track as well to cover all your cases for leading zeros.

You could easily customize this method to meet your particular needs using looping techniques to shorten the code considerably.

Edited by: Larry Browning on Jul 29, 2009 11:26 AM