topic Re: Select Statement in Application Development and Automation Discussions

Select Statement

Former Member — Thu, 19 Oct 2006 16:07:33 GMT

Hi,

for ex: obj id - OR0000123 is having

I0001, I0002, I0003, I0009, I0005.

If I0009 I0076 is there I dont want to display the obj id.

even if the objid is having other statuses.

how should i write the select statement.

SELECT SINGLE * INTO I_JEST FROM JEST

WHERE OBJNR = I_TMP_CAUFV-OBJNR and STAT....

please help.

Re: Select Statement

Former Member — Thu, 19 Oct 2006 16:19:59 GMT

Select * from jest

into table i_jest

where objnr = 'OR0000123' and

( stat <> 'I0009' OR

stat <> 'I0076' ).

Re: Select Statement

Former Member — Thu, 19 Oct 2006 18:30:35 GMT

if you are going to use select single to check

it's just :

SELECT SINGLE * INTO I_JEST FROM JEST

WHERE OBJNR = I_TMP_CAUFV-OBJNR and STAT in ('I0009','I0076')

if sy-subrc - 0 -> DO NOT DISPLAY this OBJID.

Re: Select Statement

sridhar_k1 — Thu, 19 Oct 2006 20:52:41 GMT

To exclude records like that, use subquery:

select * from jest into  table gt_jest
         for all entries in i_tmp_caufv
         where objnr = i_tmp_caufv-objnr
          and objnr not in ( select objnr from jest where stat in ('I0009', 'I0076')
                                                      and inact = ' ' )
          and inact eq ' '.

Regards

Sridhar

Re: Select Statement

Former Member — Thu, 19 Oct 2006 21:50:49 GMT

Hi Kamlesh,

You can write like:

select * from jest into table itab

where objnr = I_TMP_CAUFV-OBJNR.

if sy-subrc eq 0.

delete itab where stat = 'I0009' or stat = 'I0076'.

endif.

Re: Select Statement

Former Member — Sat, 21 Oct 2006 06:46:22 GMT

hi kamlesh,

select * into i_jest from jest

where objnr = i_tmp_caufv-objnr and

stat in ('I0001', 'I0002', 'I0003',

'I0009', 'I0005') and

stat ne ('I0076')

hope, you get the desired result.

Re: Select Statement

Former Member — Tue, 31 Oct 2006 13:58:06 GMT

Hi,

You the below logic for your requirement.

1. First get all the Orders into an internal table with Statuses I0009, I0076.

clear : i_jest, i_jest[].

if not i_tmp_caufv[] is initial.

select objnr

stat

into table i_jest

from jest

for all entries in i_tmp_caufv

where objnr = i_tmp_caufv-objnr and

( stat = 'I0009' or stat = 'I0076' ) and

inact = ' '.

if sy-subrc = 0.

sort i_jest by objnr stat.

endf.

endif.

2. Now while displaying the report you can use below logic.

loop at i_tmp_caufv.

read table i_jest with key objnr = i_tmp_caufv-objnr

binary search.

if sy-subrc = 0.

continue. " do not display which have I0009,I0076

" statuses.

else.

" display in the output

endif.

endloop.

thanks,

sksingh