topic Re: Select statement problem in Application Development and Automation Discussions

Select statement problem

Former Member — Fri, 11 Sep 2009 10:24:29 GMT

Hello Experts,

I have made a select statement in which where condition has 0R , In this I have 4 statsu numbers but it only takes 1 instead of all this 4 status, how can I get this.

select
      aufnr
      auart
      objnr
      WERKS
     INTO CORRESPONDING FIELDS OF ITAB
          from aufk where aufnr IN s_aufnr
                    and werks in S_IWERK
                    and auart in s_auart.

select SINGLE STAT
              INACT
                  from JEST into CORRESPONDING FIELDS OF itab
                                     WHERE objnr = ITAB-objnr
                                     AND  STAT = 'I0009'
                                     OR   STAT = 'I0340'
                                     OR   STAT = 'I0045'
                                     OR   STAT = 'I0321'.      " overhere I want all these status numbers

IF SY-SUBRC EQ 0.
APPEND ITAB.
ELSE.
select SINGLE STAT
                 from JEST into CORRESPONDING FIELDS OF itab
                                     WHERE objnr = ITAB-objnr
                                     AND  STAT = 'I0002'.
MODIFY ITAB.
ENDIF.
ENDSELECT.

Thanks and Regrds,

Nikhil.

Re: Select statement problem

MarcinPciak — Fri, 11 Sep 2009 10:27:25 GMT

Try this


from JEST into CORRESPONDING FIELDS OF itab
                                     WHERE objnr = ITAB-objnr
                                     AND  ( STAT = 'I0009'
                                     OR   STAT = 'I0340'
                                     OR   STAT = 'I0045'
                                     OR   STAT = 'I0321' ).

or better from performance point of view


from JEST into CORRESPONDING FIELDS OF itab
                                     WHERE ( objnr = ITAB-objnr AND  STAT = 'I0009' )
                                            OR ( objnr = ITAB-objnr AND   STAT = 'I0340' )
                                            OR  ( objnr = ITAB-objnr AND  STAT = 'I0045' )
                                             OR ( objnr = ITAB-objnr AND   STAT = 'I0321' ).

Regards

Marcin

Re: Select statement problem

Former Member — Fri, 11 Sep 2009 10:27:36 GMT

Hi,

Use this instead:


select
      aufnr
      auart
      objnr
      WERKS
     INTO CORRESPONDING FIELDS OF ITAB
          from aufk where aufnr IN s_aufnr
                    and werks in S_IWERK
                    and auart in s_auart.
 
select SINGLE STAT
              INACT
                  from JEST into CORRESPONDING FIELDS OF itab
                                     WHERE objnr = ITAB-objnr
                                     AND  STAT in ( 'I0009', 'I0340', 'I0045', 'I0321').

It is like giving range of values.

BR,

Advait

Re: Select statement problem

Former Member — Fri, 11 Sep 2009 10:28:09 GMT

Hi,,

Try this..

select SINGLE STAT

INACT

from JEST into CORRESPONDING FIELDS OF itab

WHERE objnr = ITAB-objnr

AND ( STAT = 'I0009'

OR STAT = 'I0340'

OR STAT = 'I0045'

OR STAT = 'I0321'). " overhere I want all these status numbers

Add brackets as shown..

Regards

Ansari

Re: Select statement problem

Former Member — Fri, 11 Sep 2009 10:34:12 GMT

Hi Nikhil,

Try this.

select SINGLE STAT

INACT

from JEST into CORRESPONDING FIELDS OF itab

WHERE objnr = ITAB-objnr

AND STAT in ('I0009', 'I0340', 'I0045', 'I0321').

Hope this will help you.

Regards,

Vijay

Re: Select statement problem

Former Member — Fri, 11 Sep 2009 10:37:00 GMT

All the or condition should be inside abracket.

Example

from JEST into CORRESPONDING FIELDS OF itab

WHERE objnr = ITAB-objnr

AND ( STAT = 'I0009'

OR STAT = 'I0340'

OR STAT = 'I0045'

OR STAT = 'I0321' ).

You can also append the values of stat into a range table and use that range table in your query.

ranges: r_stat for jest-stat.

r_stat-sign = 'I'.

r_stat-option = 'EQ'.

r_stat-low = 'I0340'

append r_stat.

r_stat-sign = 'I'.

r_stat-option = 'EQ'.

r_stat-low = 'I0045'

append r_stat.

from JEST into CORRESPONDING FIELDS OF itab

WHERE objnr = ITAB-objnr

AND stat in r_stat.

Re: Select statement problem

ThomasZloch — Fri, 11 Sep 2009 10:45:54 GMT

Drop the SINGLE and use INTO TABLE.

Thomas

Re: Select statement problem

Former Member — Fri, 11 Sep 2009 10:50:58 GMT

Hi Thomas,

Stupid mistake which I did not see no need of single since I need multiple entries.

Thanks,

Nikhil.