select statement logic

[Former Member]

Hi,

Database table contains data like

objnr status

60000 1

60000 2

60000 3

60001 1

60001 2

i want to read the objnr if status is not equal to 3( i don't need objnr 60000 because status is 3)

my internal table contains data only 60001.

How can i write select statement.

Regards,

Suresh.

[Former Member]

Hi,

Try like this..

Select objnr from <table> into table itab where status <> '3'.

Regards,

Kalp...

[Former Member]

Hi,

Try this.

Select objnr from tablename into lt_itab where status NE '3'.

Regards,

N M Poojari.

[Former Member]

Hi,

Try this way to get solution

Select <data> from <table-name> where status NE '3'.

Hope this helps you out.

Thanks & Regards

[Former Member]

Hi,

sorry Try like this..

Select objnr from <table> into table itab where status NE '3'.

Regards,

Kalp...

[Former Member]

Select objnr status from <table_name> into <internal_tab>

where status NE 3.

Thanks,

Raj.

Edited by: Rajat Chaturvedi on Nov 25, 2008 3:47 PM

[VikasB]

Hi Suresh,

Define Range for status field as follows.

Data: r_stat type range of <status>,

r_stat-sign = 'I'.

r_stat-option = 'NE'.

r_stat-low = '3'.

append r_stat.

Now select the objnr from database table using this range.

select objnr

status

from <table>

into table <i_tab>

where status in r_stat.

Regards,

Vikas.

[JozsefSzikszai]

I am afraid this can only be done in two steps:

1.
SELECT DISTINCT objnr
FROM ...
INTO TABLE itab1
WHERE status EQ '3'. " All objnr are selected., which have status 3 (!)

2.
SELECT DISTINCT objnr
FROM ...
INTO TABLE itab2
FOR ALL ENTRIES IN itab1
WHERE objnr NE itab1-objnr. "All objnr are selected which don't have status 3

Another solution would be to use somenested select:

SELECT objnr
FROM ...
INTO wa
WHERE status NE 3.
IF sy-subrc EQ 0.
SELECT COUNT( * ) 
FROM ... 
WHERE objnr EQ wa-objnr
AND status EQ '3'. "here it is checked if objnr with status 3 exists
IF sy-subrc NE 0. " if no ==> append itab
APPEND wa TO itab.
ENDIF.
ENDIF.
ENDSELECT.

(I don't know which one would be better performancve wise, probably the first)

[Former Member]

Hi Eric,

Iam using below code but both internal table contains same data.

where is my code wrong?

DATA : r_stat TYPE RANGE OF jest-stat,

r_inact TYPE RANGE OF jest-inact,

wa_stat LIKE LINE OF r_stat,

wa_inact LIKE LINE OF r_inact.

CLEAR: wa_stat,

wa_inact.

REFRESH: r_stat,

r_inact.

wa_stat-sign = 'I'.

wa_stat-option = 'EQ'.

wa_stat-low = 'I0045'.

APPEND wa_stat TO r_stat.

CLEAR wa_stat.

wa_stat-sign = 'I'.

wa_stat-option = 'EQ'.

wa_stat-low = '0012'.

APPEND wa_stat TO r_stat.

CLEAR wa_stat.

wa_stat-sign = 'I'.

wa_stat-option = 'EQ'.

wa_stat-low = 'I0016'.

wa_stat-high = 'I0016'.

APPEND wa_stat TO r_stat.

CLEAR wa_stat.

wa_inact-sign = 'I'.

wa_inact-option = 'EQ'.

wa_inact-low = ' '.

APPEND wa_inact TO r_inact.

CLEAR wa_inact.

TYPES: BEGIN OF ty_aufk,

aufnr TYPE aufk-aufnr,

END OF ty_aufk.

DATA: gt_aufk TYPE TABLE OF ty_aufk,

gt_aufk1 TYPE TABLE OF ty_aufk,

wa_aufk TYPE ty_aufk.

SELECT distinct a_{aufnr INTO TABLE gt_aufk FROM aufk AS a JOIN jest AS b ON a}objnr = b~objnr

WHERE a~werks EQ '0001'

AND a~auart EQ 'YB02'

AND b~stat IN r_stat

AND b~inact IN r_inact .

sort gt_aufk.

if not gt_aufk is initial.

select distinct aufnr from aufk into table gt_aufk1 for all entries in gt_aufk

WHERE aufnr ne gt_aufk-aufnr.

endif.

Regards,

Suresh

[JozsefSzikszai]

sorry, my first idea was wrong (FOR ALL ENTRIES and NOT EQUAL in WHERE don't like each other...). Pls. rewrite the code the following way:

DATA : range TYPE RANGE OF objnr.

1.
SELECT DISTINCT objnr AS low
FROM ...
INTO TABLE range
WHERE status EQ '3'. " All objnr are selected., which have status 3 (!) into a range

1./a 
LOOP AT range.
range-sign = 'E'.
range-option = 'EQ'.
MODIFY range.
ENDLOOP.

2.
SELECT DISTINCT objnr
FROM ...
INTO TABLE itab2
WHERE objnr IN rannge "All objnr are selected which don't have status 3

Now the values (which we don't need later) are selected into a range. The range is modified (in step 1/a), so exclusions happen and finally the range is used to exclude the values in the final selection

[SuhaSaha]

Hello Suresh,

You have to create two more internal tables.

IT1 contains data from the DB table.

IT2 contains all OBJNR for which STATUS = 3

IT3 contains all OBJNR for which STATUS NE 3.

SELECT F1 F2 F3 FROM TAB
INTO TABLE IT1
WHERE OBJNR IN S_OBJNR. "DONOT use STATUS here, else you'll get wrong data

LOOP AT IT1 INTO WA.
  IF WA-STATUS = '3'.
    APPEND WA TO IT2.
    CLEAR WA.  
  ENDIF.
ENDLOOP.

SORT IT2 BY OBJNR.

LOOP AT IT1 INTO WA.
  READ TABLE IT2 WITH KEY OBJNR = WA-OBJNR BINARY SEARCH.
  IF SY-SUBRC NE 0.
    APPEND WA TO IT3.
    CLEAR WA.
  ENDIF.
ENDLOOP.

Hope this is clear.

BR,

Suhas