select statement conditions

[Former Member]

Hi,

I want to read the data from aufk and jest table .

while selecting data i have some exclude functions .

Exclusions :

jest-stat = 'I0045' jest-inact = ' '.

jest-stat = '0012' jest-inact = ' '.

jest-stat = '0016' jest-inact = ' '.

I wrote the below code.

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 = 'E'.

wa_stat-option = 'EQ'.

wa_stat-low = 'I0045'.

APPEND wa_stat TO r_stat.

CLEAR wa_stat.

wa_stat-sign = 'E'.

wa_stat-option = 'EQ'.

wa_stat-low = '0012'.

APPEND wa_stat TO r_stat.

CLEAR wa_stat.

wa_stat-sign = 'E'.

wa_stat-option = 'EQ'.

wa_stat-low = 'I0016'.

wa_stat-high = 'I0016'.

APPEND wa_stat TO r_stat.

CLEAR wa_stat.

wa_inact-sign = 'E'.

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,

objnr type aufk-objnr,

stat type jest-stat,

inact type jest-inact,

end of ty_aufk.

data: gt_aufk type table of ty_aufk,

wa_aufk type ty_aufk.

SELECT a_{aufnr a}objnr b_{stat b}inact into table gt_aufk FROM aufk AS a JOIN jest AS b ON a_{objnr = b}objnr

WHERE a~werks = '0001'

AND a~auart EQ 'YB02'

AND b~stat IN r_stat

AND b~inact IN r_inact .

In Jest table contains data like

objnr stat inact

OR000000600000 I0001 X

OR000000600000 I0002

OR000000600000 I0016

iam getting only first record .

How can i moify my select conditions.

Regards,

Suresh

[Former Member]

Hi,

change like this,

SELECT a_{aufnr a}objnr b_{stat b}inact into table gt_aufk FROM aufk AS a JOIN jest AS b ON a_{objnr = b}objnr

WHERE a~werks = '0001'

AND a~auart EQ 'YB02'

AND b~stat IN r_stat

AND b~inact = ' '.

Thnaks,

Krishna.

[Former Member]

Hi,

Try using For All entries.

[Former Member]

Hi,

change like this,

SELECT a_{aufnr a}objnr b_{stat b}inact into table gt_aufk FROM aufk AS a JOIN jest AS b ON a_{objnr = b}objnr

WHERE a~werks = '0001'

AND a~auart EQ 'YB02'

AND b~stat IN r_stat

AND b~inact = ' '.

Thnaks,

Krishna.

[Former Member]

select from the header table then go for for all entries and select from other table with necesary conditions,....

[Former Member]

Hi,

Say r_stat is the internal table containing the entries which you want to exclude. Then use the condition in where

NOT IN r_stat.

Regards,

Murthy.

[Former Member]

u can use into corresponding fields of table <internal table> in select query

SELECT aufk_{aufnr aufk}objnr jest_{stat jest}inact

into corresponding fields of table gt_aufk

FROM aufk inner JOIN jest

ON aufk_{objnr = jest}objnr

WHERE aufk~werks = '0001'

AND aufk~auart EQ 'YB02'

AND jest~stat IN r_stat

AND jest~inact IN r_inact .

Edited by: krupa jani on Nov 25, 2008 8:22 AM