topic Re: Select Statement in Application Development and Automation Discussions

Select Statement

Former Member — Thu, 19 Oct 2006 15:48:34 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:04:40 GMT

I didn't get your query exactly. But assuming 10001, 10002, 10003 ...... are objnr

here is your select statement:

SELECT SINGLE * INTO I_JEST

FROM JEST

WHERE OBJNR NOT IN ('10009','10076').

Let me know if your are not clear...

Regards,

Vinod.

Re: Select Statement

Former Member — Thu, 19 Oct 2006 16:06:58 GMT

Hope below code can give you some lead:

if not i_tmp_caufv[] is initial.  -> Assuming i_tmp_caufv is an internal table
   select objnr estat ...
          into table itab
          for all entries in i_tmp_caufv
          from jest 
          where objnr = i_tmp_caufv-objnr.
   loop at itab into wa_itab where estat = 'I0009'
                             or    estat = 'I0076'.
        delete itab where objnr = wa_itab-objnr.
   endloop.
endif.

Kind Regards

Eswar

Re: Select Statement

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

Hi,

U can select all the necessary data then filter out .

Pl find eg below. if it helps pl reward.

eg.

FORM f1200_read_status_and_filter.

sort i_viqmelst by qmnum objnr ascending.

LOOP AT i_viqmelst.

REFRESH ws_stat.

CALL FUNCTION 'STATUS_READ'

EXPORTING

objnr = i_viqmelst-objnr

only_active = 'X'

TABLES

status = ws_stat.

*Delete RSLV notification

READ TABLE ws_stat WITH KEY stat = 'E0005' . "RSLV

IF sy-subrc = 0.

DELETE i_viqmelst where qmnum = i_viqmelst-qmnum.

ENDIF.

*Delete INCP notification

READ TABLE ws_stat WITH KEY stat = 'E0003'. "INCP

IF sy-subrc = 0.

DELETE i_viqmelst where qmnum = i_viqmelst-qmnum.

ENDIF.

ENDLOOP .

Re: Select Statement

Former Member — Thu, 19 Oct 2006 16:15:48 GMT

vinod,

Every objnr has several different status

like i0002, i0009 i0003 i0076.

i dont need the objnr where there is i0009 and i0076.

if there is an obj id with the i0009 and i00076 then dont display it.the same status may have I0002 and I0003.

did u get my point.

Re: Select Statement

RichHeilman — Thu, 19 Oct 2006 16:22:10 GMT

Not sure about the requirement, but if you want to select a row only when STAT is either I0009 or I0076, then you can do this.

SELECT SINGLE * INTO I_JEST FROM JEST
          WHERE OBJNR = I_TMP_CAUFV-OBJNR
            and <b>STAT in ('I0009', 'I0076').</b>

Regards,

Rich Heilman

Re: Select Statement

Former Member — Thu, 19 Oct 2006 16:28:13 GMT

Rich,

I dont want the objnr no with the following status. these objnr will be having many more statuses. but when it encounter I0009 and I00076.

tht objnr shd not be displayed.

even if it there with different statuses.

please help.

Kamlesh

Re: Select Statement

Former Member — Thu, 19 Oct 2006 16:35:46 GMT

Taking Rich's Code as an example:

SELECT SINGLE * INTO I_JEST FROM JEST
          WHERE OBJNR = I_TMP_CAUFV-OBJNR
            and STAT in ('I0009', 'I0076').
IF <b>sy-subrc ne 0.</b>
*** Display details
ENDIF.

How about above method???

Kind Regards

Eswar

Re: Select Statement

Former Member — Thu, 19 Oct 2006 21:45:21 GMT

Is this what you are looking for?

DATA: s_jest TYPE jest.

SELECT SINGLE * INTO s_jest FROM jest
          WHERE objnr = your_object_number
            AND stat <b>NOT IN</b> ('I0009', 'I0076').

Re: Select Statement

Former Member — Fri, 20 Oct 2006 08:38:00 GMT

Here we go !!

loop at the internal table where obj no = 'I0009' or

obj no = 'I0076 '.

if sy-subrc = 0 .

delete all those rec for the internal table.

endif.

end loop.

pl let me know if it still not working.

cheers.

Re: Select Statement

Former Member — Fri, 20 Oct 2006 08:42:14 GMT

Kamlesh,

It is not possible in a single step but you can use the below logic. As I understand from the code you are looping at i_tmp_caufv and performing the particular select.

loop at i_tmp_caufv.

refresh i_tmp.

clear i_tmp.

SELECT objnr INTO table I_tmp FROM JEST

WHERE OBJNR = I_TMP_CAUFV-OBJNR

and STAT in ('I0009', 'I0076').

describe table i_tmp lines lcnt.

if lcnt = 0.

SELECT objnr stat into i_jest from jest

WHERE OBJNR = I_TMP_CAUFV-OBJNR.

endif.

Endloop.

The above should be able to give you the required result.

Message was edited by: Anurag Bankley

Re: Select Statement

Former Member — Fri, 20 Oct 2006 09:34:13 GMT

Hi Kamlesh,

SELECT SINGLE * INTO I_JEST FROM JEST

WHERE OBJNR = I_TMP_CAUFV-OBJNR.

sort i_jest by objnr stat2.

loop at i_jest.

if i_jest-stat2 = 'I0009' or

i_jest-stat2 = 'I0076'.

v_objnr = i_jest-objnr.

delete i_jest where objnr = v_objnr.

endif.

-Anu.

endloop.

Message was edited by: Anupama Reddy

Re: Select Statement

Former Member — Fri, 20 Oct 2006 09:41:52 GMT

Hi Kamlesh,

try this...

declare another internal table of same structure of i_jest.

SELECT SINGLE * INTO I_JEST FROM JEST

WHERE OBJNR = I_TMP_CAUFV-OBJNR.

loop at i_jset.

if objid = 'I0009' or 'I0076'

continue.

else.

move-corresponding i_jest to int_jest.

append int_jest.

clear int_jest.

endif.

endloop.

finally display int_jest.

Madhavi.