on 05-02-2006 3:13 PM
I have a small query where I have to retrieve vendor details not including the vendors starting with '9'.
I have written the below query for that.
select b~lifnr " Vendor number
into corresponding fields of table gi_output_vendors
from lfa1
where lifnr not like '9%'.
But the output I am getting contains vendor numbers '0000950000' where I wanted to eliminate these type of numbers also. i.e, I don't want to consider leading zeroes.
So, the below code I have written for that.
The problem is with if condition or delete statement 'like' logical operator is not being allowed.
loop at gi_output_vendors into wa_output_vendors.
shift wa_output_vendors-lifnr left deleting leading '0'.
if wa_output_vendors-lifnr like '9%' " didn't work delete gi_output_vendors from wa_output_vendors where
lifnr like '9%'. "didn't work
endif.
endloop.
When I use 'like' with 'if' condition or 'delete' statement, I am getting error saying that 'Like operator is not allowed'.
How could I deal with this situation.
Thanks in advance.
Vishnu Priya
Hi Vishnu,
Vendor number i think is of char type.
Had it been numeric this problem might have not arise.
Convert the number to numeric type n convert it back to string..
this removes the leading zeros..
Now compare using offset..
eg:
wa_output_vendors-lifnr+0(1) <> '9'.
Regards,
Tanveer.
Please mark helpful answers..
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
If you can guarantee the 9 will always be in the same place then it would be better to use offset logic or something like 'lifnr NOT LIKE '00009'' - incidentally, I believe the % wildcard only replaces one character so '9%' will be looking for a 2 char string containing a 9 followed by ONE other character. Using '9' will look for a 9 followed by any number of other characters. Since lifnr is a 10 char field (according to your example) '9%' will always fail. I would suggest using the Data Browser (SE11) selection screens to try out some of the possibilities and see what works.
Hope that's of some help!
Andy
By the way, I wouldn't recommend using '9' becasue this will look for a 9 <b>anywhere</b> in the lifnr field i.e. it could exclude a perfectly valid number just because it ends in a 9!
Message was edited by: Andrew Wright
Sorry, ignore me, in SQL you should use % and not * for multiple characters. However, the same applies if you can guarantee the position of the 9.
Message was edited by: Andrew Wright
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi Priya,
When we query on a table, it will take internal format of field if a field conversion exists for that field. Lifnr has conversion exists.
CONVERSION_EXIT_ALPHA_INPUT -> Conversion exit ALPHA, external->internal
CONVERSION_EXIT_ALPHA_OUTPUT -> Conversion exit ALPHA, internal->external
So it is giving proper results for your select query.
Lifnr internally starts with 0's so try the following code which can provide solution at SELECT statement level only.
select b~lifnr " Vendor number
into corresponding fields of table gi_output_vendors
from lfa1
where lifnr not like '0%9%'.
Thanks,
Vinay
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi Priya,
You can use this code :
Tables : lfa1.
data : begin of gi_output_vendors occurs 0.
include structure lfa1.
data : end of gi_output_vendors.
select lifnr " Vendor number
into corresponding fields of table gi_output_vendors
from lfa1
where lifnr not like '%9%'.
Loop at gi_output_vendors.
write : / gi_output_vendors-lifnr.
endloop.
Regards,
Kunal.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
try CONVERSION_EXIT_ALPHA_OUTPUT which removes leading zeroes
loop at itab.
CALL FUNCTION 'CONVERSION_EXIT_ALPHA_OUTPUT'
EXPORTING
INPUT = itab-fld
IMPORTING
OTPUT = itab-fld
end.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi,
You could use that:
loop at gi_output_vendors into wa_output_vendors.
shift wa_output_vendors-lifnr left deleting leading '0'.
if wa_output_vendors(1) eq '9'.
delete gi_output_vendors from wa_output_vendors.
endif.
endloop.
Regards,
Mireia
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
81 | |
24 | |
11 | |
9 | |
7 | |
5 | |
5 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.