topic Re: Select only numbers from database in Application Development and Automation Discussions

Select only numbers from database

Former Member — Fri, 28 Aug 2009 09:38:55 GMT

Hi All,

I need to retrieve numerical data's from the database. Data base table has both numerical and text values. now I want to retrieve only numerical data's.

Can any one help me on this,

Thanks and helps will be appreciated.

Re: Select only numbers from database

Former Member — Fri, 28 Aug 2009 09:45:01 GMT

Hi,

Check if this works



select * 
from dbtable
into itab
where NOT field1 like '% A% B% C% D% E% F% G% H% I% J% K% L% M% N% O% P% Q% R% S% T% U% V% W% X% Y% Z %'.

Regards,

Vikranth

Re: Select only numbers from database

Former Member — Fri, 28 Aug 2009 09:45:26 GMT

Hi,

You requirement is not clear at all. Kindly mention the table and field to be retrived.

Sajimon Chandran

Re: Select only numbers from database

former_member611006 — Fri, 28 Aug 2009 09:46:39 GMT

Hi,

try this:

SELECT ... WHERE field_name CO '0123456789'.

Hope it helps.

Regards,

David

Re: Select only numbers from database

Former Member — Fri, 28 Aug 2009 09:47:32 GMT

Hello

Try this scenario (example for MARA-MATNR):


* declare RANGES for value
RANGES: R_MATNR for MARA-MATNR.
* fill RANGES by numeric values
R_MATNR-SIGN = 'I'.
R_MATNR-OPTION = 'BT'.
R_MATNR-LOW = '000000000000000000'.
R_MATNR-HIGH = '999999999999999999'.
APPEND R_MATNR.
* select data
SELECT * FROM MARA INTO TABLE ITAB WHERE MATNR IN R_MATNR.

Re: Select only numbers from database

Former Member — Fri, 28 Aug 2009 09:51:47 GMT

Hi Experts,

Thanks for your valuable replies.

Table is T591S and field name is SUBTY.

I Used CO operator. But it throws error like "CO" is not a valid comparison operator. .

Thanks.

Re: Select only numbers from database

Former Member — Fri, 28 Aug 2009 09:52:39 GMT

hello

Re: Select only numbers from database

Former Member — Fri, 28 Aug 2009 09:56:40 GMT

Hi,

You cannot use CO CA CP operators in where conditions of select statement directly.

Try the solution given by Dzed. It should work

Regards,

Vik

Re: Select only numbers from database

Former Member — Fri, 28 Aug 2009 09:59:00 GMT

Hello

Working code:


data: itab like T591S occurs 0.
RANGES: R_SUBTY for T591S-SUBTY.
R_SUBTY-SIGN = 'I'.
R_SUBTY-OPTION = 'BT'.
R_SUBTY-LOW = '0'.
R_SUBTY-HIGH = '9999'.
APPEND R_SUBTY.
SELECT * FROM T591S INTO TABLE ITAB WHERE SUBTY IN R_SUBTY.

Re: Select only numbers from database

Former Member — Fri, 28 Aug 2009 10:02:04 GMT

Hi Dzed Maroz,

Thanks for the Effort. But i need to write this code with in the method of class.

In ABAP Objects we cannot use Ranges addition. Anyway thanks for your effort.

Thanks.

Re: Select only numbers from database

former_member611006 — Fri, 28 Aug 2009 10:04:28 GMT

Hi,

sorry for the mistake.

You can select all your data and then use the CO operator into your LOOP statement (i.e. with a CHECK )

Regards,

David

Re: Select only numbers from database

ThomasZloch — Fri, 28 Aug 2009 10:05:16 GMT

But you can use

DATA: R_SUBTY TYPE RANGE OF T591S-SUBTY.

and also use a separate work area.

Thomas

Re: Select only numbers from database

Former Member — Fri, 28 Aug 2009 10:11:08 GMT

Hi Vikranth,

I tried your code.

select *

from dbtable

into itab

where NOT field1 like '% A% B% C% D% E% F% G% H% I% J% K% L% M% N% O% P% Q% R% S% T% U% V% W% X% Y% Z %'.

But it shows error like 'Like operator should be no mor than 8 characters long'..

Thanks.

Re: Select only numbers from database

sridhar_meesala — Fri, 28 Aug 2009 10:26:12 GMT

Hi,

Try to split the where and see. Some thing like this.

select *
from ztable
into TABLE itab
where NOT ztable like '% A% B%' AND NOT ztable like 'C% D% E' AND NOT ztable like '% F% G%' AND NOT ztable like 'H% I% J'
  AND NOT ztable like '% K% L' AND NOT ztable like '% M% N' AND NOT ztable like '% O% P' AND NOT ztable like '% Q% R'
  AND NOT ztable like '% S% T' AND NOT ztable like '% U% V' AND NOT ztable like '% W% X' AND NOT ztable like '% Y% Z %'.

Thanks,

Sri.

Re: Select only numbers from database

former_member611006 — Fri, 28 Aug 2009 10:28:02 GMT

Hi,

did you try like this ?

SELECT * FROM database INTO t_inter WHERE field1 = crit1.

LOOP AT t_inter into w_intern WHERE field1 CO '0123456789'.
....
ENDLOOP.

LOOP AT t_inter into w_intern.
CHECK w_intern-field1 CO '0123456789'
....
ENDLOOP.

Regards,

David

Re: Select only numbers from database

Former Member — Fri, 28 Aug 2009 10:29:12 GMT

Hi Nandini,

Try like this


data: begin of itab occurs 0,
      f1 like T591S-SUBTY,
      end of itab.


select subty
into table itab
from t591s
where NOT subty like '%A%B%C%' and
      NOT subty like '%F%G%H%'  and
      NOT subty like '%K%L%M%' and
      NOT subty like '%P%Q%R%' and
      NOT subty like '%U%V%W%'  and
      NOT subty like '%E%J%O%' and
      NOT subty like '%y%Z&'.

I must have missed 1 or 2 alphabets. Include them and check. It works

Regards,

Vik

Re: Select only numbers from database

Former Member — Fri, 28 Aug 2009 10:30:27 GMT

Hello

As say Thomas you can declare:


DATA: R_SUBTY TYPE RANGE OF T591S-SUBTY.
*" instead of
RANGES: R_SUBTY for T591S-SUBTY.

It will be work.

P.s. In the future immediately elaborate that you are working with classes.

Re: Select only numbers from database

Former Member — Fri, 28 Aug 2009 10:33:08 GMT

Hi Nandini,

the following code works according to ur requirement.......


types : begin of type_itab,
          subty type t591s-subty,
        end of type_itab.

data: w_subty type t591s-subty.
data: itab type table of type_itab with header line.
data: w_idx like sy-tabix.

select distinct subty
from t591s
into table itab.

  if sy-subrc eq 0.
    loop at itab.
    w_idx = sy-tabix.
      if itab ca sy-abcde.
      delete itab index w_idx.
      endif.
    endloop.
    clear itab.
  endif.

Regards,

Mdi.Deeba