SELECT Statement question

[Former Member]

Hi ABAP Gurus,

I have the following code.

DATA: BEGIN OF i_p0000 OCCURS 0,

pernr LIKE pa0000-pernr,

begda LIKE pa0000-begda,

endda LIKE pa0000-endda.

DATA: END OF i_p0000.

DATA: v_date_from TYPE d,

v_date_to TYPE d.

DATA: BEGIN OF PERNRZ_TAB occurs 0,

PERNR(8) TYPE N,

END OF PERNRZ_TAB.

RANGES: l_r_pernr FOR pa0000-pernr, "personnel number

.

.

SELECT PERNRZ1 FROM ZTESTHR1 INTO table PERNRZ_TAB

WHERE BEGDAZ1

BETWEEN v_date_from AND v_date_to.

OPEN CURSOR WITH HOLD s_cursor FOR

SELECT pernr

begda

endda

FROM pa0000

WHERE pernr IN l_r_pernr.

• Fetch records into interface table.

FETCH NEXT CURSOR s_cursor

APPENDING CORRESPONDING FIELDS

OF TABLE i_p0000

PACKAGE SIZE s_s_if-maxsize.

IF sy-subrc <> 0.

CLOSE CURSOR s_cursor.

RAISE no_more_data.

ELSE.

SORT i_p0000 BY pernr.

I would like to retrieve records based on the values in PERNRZ_TAB. When I make the following change I am getting an error.

SELECT pernr

begda

endda

FROM pa0000

WHERE pernr IN PERNRZ_TAB.

Thanks in advance.

Regards,

bw_newbie

[Former Member]

either you should say SELECT SINGLE or use ENDSELECT after SELECT>

regards

srikanth

[Former Member]

SELECT pernr

begda

endda

<b>into table IT_PA0000</b>

FROM pa0000

<b>for all entries in PERNRZ_TAB

WHERE pernr = PERNRZ_TAB-PERNR.</b>

if you want to retrieve records from table for all the entries in any Internal table, you should use FOR ALL ENTRIES addition of SELECT statement.

this fetch may have more than 1 record, so you should give the INTO TABLE addition also.

define IT_PA000 internal table with pernr

begda

endda fields.

Check this.

Regards

srikanth

Message was edited by: Srikanth Kidambi

[Former Member]

hi,

SELECT pernr

begda

endda

FROM pa0000 into table tablename

for all entries in PERNRZ_TAB

WHERE pernr IN PERNRZ_TAB-pernr.

Message was edited by: Ashok Parupalli

[Former Member]

Hi Ashok,

I get the following error "The IN operator with PERNRZ_TAB-PERNR is followed neither by an internal table nor by a value list".

Thanks.

bw_newbie

[Former Member]

yes, you will get the ERROR because PERNRZ_TAB IS not a select-option or a Range.

for SELECT-OPTION or RANGE only you can use IN operator.

if you are passing internal table, you have to use FOR ALL ENTRIES addition .

check my recent post given above.

regards

srikanth

Message was edited by: Srikanth Kidambi

[Former Member]

Hi Srikanth,

I have the following code.

SELECT pernr

begda

endda

into table IT_PA0000

FROM pa0000

FOR ALL ENTRIES IN PERNRZ_TAB

WHERE pernr IN PERNRZ_TAB-PERNR.

Now, when I activate, i get the following message"The INTO clause must be specified with FETCH NEXT CURSOR. It is not allowed with OPEN CURSOR".

[Former Member]

Change the query as

IF PERNRZ_TAB[] IS NOT INITIAL.

SELECT pernr

begda

endda

FROM pa0000 FOR ALL ENTRIES IN PERNRZ_TAB

WHERE pernr IN PERNRZ_TAB-PERNR.

ENDIF.

[Former Member]

You need to use For all entries addition as follows to fulfill UR requirement.

IF PERNRZ_TAB[] IS NOT INITIAL.

SELECT PERNR

BEGDA

ENDDA

FROM PA0000 INTO TABLE <INTERNAL_TABLE> FOR ALL ENTRIES IN PERNRZ_TAB

WHERE pernr = PERNRZ_TAB-PERNR.

ENDIF.

Hope this solves your problem.

If solved then do reward the points.

[Former Member]

hi do like this,

SELECT pernr

begda

endda

FROM pa0000 into table i_p0000

for all entries in PERNRZ_TAB

WHERE pernr IN PERNRZ_TAB-pernr.

[Former Member]

As everyone said before, use 'FOR ALL ENTRIES'. But make sure that PERNRZ_TAB has some entries in it before you do the SELECT. If the table is empty, then it will fetch all of them.

<b>CHECK NOT pernrz_tab[] IS INITIAL.</b>

SELECT.....

FOR ALL ENTRIES IN pernrz_tab

WHERE ......

[Former Member]

Another alternative can be to fill your range from the internal table as follows. In this case you don't have to change the select statement.

LOOP AT pernrz_tab.
  l_r_pernr-option = 'EQ'.
  l_r_pernr-sign   = 'I'.
  l_r_pernr-low    = pernrz_tab-pernr.
  APPEND pernrz_tab.
  CLEAR  pernrz_tab.
ENDLOOP.

SELECT ....
FROM pa0000
WHERE pernr in l_r_pernr.

[Former Member]

Thanks a lot everybody. Srinivas your code solved my problem. I am assigning points to everybody who responded.

Thanks once again.

Regards,

bw_newbie

[Former Member]

Glad that it helped but you have to keep one thing in mind. If there are many entries in your internal table, then the select statement will dump with the IN range clause. Another thing you can do is to sort the range by LOW value and then do a DELETE ADJACENT DUPLICATES. That way you can eliminate the duplicate PERNRs.