topic Re: ABAP QUERY in Application Development and Automation Discussions

ABAP QUERY

adnanmaqbool — Tue, 12 Dec 2006 17:07:54 GMT

Respected Guys

I want to access a table containing Employee Education Profile. I want to write a query by which I can see only those employees which dont have MBA degree. My problem is that in the table a employee can have more than one enteries becuse he can have degrees other than MBA e.g

emp degree

1 MBA

1 BA

1 BSC

2 CA

If i will write query like this then I want emp 2 only because emp 2 dont have MBA degree.

e.g

Select emp degree

from emptab

INTO ITAB

where degree NE 'MBA;.

But of course above brings both emp 1 and 2. and I want only emp 2 becuase it actuallly emp 2 who dont have MBA degree.

Can please anybody correct the above query.

Thnx

Re: ABAP QUERY

Former Member — Tue, 12 Dec 2006 17:17:49 GMT

Hello,

First select all the employees irrespective of degree to itab1.

Then select all the record who degree is MBA to itab2.

LOOP at itab1.

read table itab2 with key emp = itab1-emp.

if sy-subrc ne 0.

move to final table which contains emp without MBA degree.

endif.

endloop.

If useful reward.

Vasanth

Re: ABAP QUERY

Former Member — Tue, 12 Dec 2006 17:18:23 GMT

You can do it in two steps. First select all of the employees that <b>have</b> an MBA into a range table. Then select all employees, excluding those in the range table.

Rob

Re: ABAP QUERY

Former Member — Tue, 12 Dec 2006 17:24:37 GMT

Try something like this:

DATA: I_EMPTAB TYPE TABLE OF EMPTAB WITH HEADER LINE.

DATA: V_FLAG TYPE I.

SELECT * FROM EMPTAB INTO TABLE I_EMPTAB.

DELETE ADJACENT DUPLICATE ENTRIES FROM I_EMPTAB COMPARING EMP.

V_FLAG = 0.

LOOP AT I_EMPTAB.

LOOP AT EMPTAB WHERE EMP = I_EMPTAB-EMP.

IF EMPTAB-DEGREE = 'MBA'.

V_FLAG = '1'.

ENDIF.

ENDLOOP.

IF V_FLAG NE '1'.

MOVE-CORRESPONDING EMPTAB TO ITAB.

ENDIF.

ENDLOOP.

Warren

Re: ABAP QUERY

Former Member — Tue, 12 Dec 2006 17:25:20 GMT

oh, and dont forget to append after the move.

Warren

Re: ABAP QUERY

LucianoBentiveg — Tue, 12 Dec 2006 17:27:59 GMT

Try this:


  SELECT * AS f FROM emptab INTO itab
      WHERE degree NE 'MBA'
        AND NOT EXISTS ( SELECT * FROM emptab
                          WHERE emp EQ f~emp
                            AND degree EQ 'MBA' ).