ABAP QUERY

[adnanmaqbool]

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

[adnanmaqbool]

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

[Former Member]

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

[Former Member]

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

[Former Member]

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

[Former Member]

oh, and dont forget to append after the move.

Warren

[LucianoBentiveg]

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' ).