select... for all entries in...

[Former Member]

Hi,

Can anybody help me to sort out this issue. This select should work. I m filling an itab with distinct pernr. in temp it should give <b>all the records for which pernr should not be in itab.</b>

report ZTEST4.

data : begin of ITAB occurs 0,

PERNR like PA0001-PERNR,

ename like pa0001-ename,

end of ITAB.

data : temp like itab occurs 0 with header line.

clear : ITAB, TEMP.

refresh : ITAB,TEMP.

select distinct PERNR into table ITAB from PA0001 where PERNR le 10.

select PERNR ENAME into table TEMP from PA0001

for all entries in ITAB where pernr not in itab-pernr.

sort TEMP by PERNR.

break-point.

[athavanraja]

Sample code from ABAP Key word documentation.

SELECT * FROM sflight INTO wa_sflight 
    FOR ALL ENTRIES IN ftab 
    WHERE CARRID = ftab-carrid AND 
          CONNID = ftab-connid AND 
          fldate = '20010228'. 
  free = wa_sflight-seatsocc - wa_sflight-seatsmax. 
  WRITE: / wa_sflight-carrid, wa_sflight-connid, free. 
ENDSELECT. 

Regards

Raja

[TMNielsen]

Hello

Please be carefull with "for all entries in"

Before you execute the select command you should make sure that the table ITAB is not empty.

If the table is empty this will work as empty select-options - this means you will get all records from PA0001 but you probably want to select no records.

Best regards

Thomas Madsen Nielsen

[ChristianFi]

the suggestion with for all entries where pernr <> itab-pernr would not work because this check will be performed for each line individually (as opposed to the IN operator)

But I do not see why you could not read the internal table in one single select.

report ZTEST4.

data : begin of ITAB occurs 0,
PERNR like PA0001-PERNR,
ename like pa0001-ename,
end of ITAB.


select PERNR ENAME into table TEMP from PA0001
where pernr le 10.

Christian

[Former Member]

I agree with you Christian. He probably needs to include the DISTINCT keyword in your suggestion. Also, it appears that he wants the ones that are NOT le 10 so I think your code should read:

where pernr gt 10

Would you agree?

If this does not work out, then build a RANGE and put the PERNR that you do not want in the RANGE with an exclusive test, for example:

SIGN = 'E'

OPTION = 'EQ'

LOW = '# to be excluded'

HIGH = space

then do a WHERE clause with PERNR IN MY_RANGE.

Let us know how it goes.

[ChristianFi]

Hi Charles.

you are right, I missed that point with gt.

I thought the distinct part would be necessary to avoid duplicate read with the FAE-Statement.

As for the range this would work if pernr does not exceed a certain amount of values (sql statement too large) . If necessary a subquery would be an option (in this case more effective than the FAE - Stement)

Christian

[former_member214131]

Hello,

One more point has to be kept in mind to get consistant records while using the 'for all enties' is that all the key fields are to be fetched from the table.

I would prefer to read especially the SAP Online Doc everytime when I use the 'for all entries statement' as this has tricky variants.

I hope this helps.

Regards, Murugesh AS

[Former Member]

Hi Sagar,

If i understand it well, you exclude all PERNR LE 10.

But first things first.

You cannot use the FOR ALL ENTRIES in your case (it doesn't allow the operator "NOT IN").

Your code should be:

REPORT ztest4.

TYPES: BEGIN OF ty_itab,

pernr TYPE persno,

ename TYPE emnam,

END OF ty_itab.

DATA : itab TYPE ty_itab occurs 0,

wtab TYPE ty_itab.

CLEAR: itab,

wtab.

SELECT pernr ename

INTO TABLE itab

FROM pa0001

WHERE pernr LE 10

ORDER BY pernr.

IF sy-subrc EQ 0.

DELETE ADJACENT DUPLICATES FROM itab COMPARING pernr.

ENDIF.

Regards,

Rob.