topic Re: SELECT Statement question in Application Development and Automation Discussions

SELECT Statement question

Former Member — Thu, 03 Aug 2006 16:17:13 GMT

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

Re: SELECT Statement question

Former Member — Thu, 03 Aug 2006 16:19:20 GMT

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

regards

srikanth

Re: SELECT Statement question

Former Member — Thu, 03 Aug 2006 16:19:22 GMT

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

Re: SELECT Statement question

Former Member — Thu, 03 Aug 2006 16:20:28 GMT

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.

Re: SELECT Statement question

Former Member — Thu, 03 Aug 2006 16:21:05 GMT

SELECT pernr

begda

endda

into table IT_PA0000

FROM pa0000

for all entries in PERNRZ_TAB

WHERE pernr = PERNRZ_TAB-PERNR.

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

Re: SELECT Statement question

Former Member — Thu, 03 Aug 2006 16:23:11 GMT

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

Re: SELECT Statement question

Former Member — Thu, 03 Aug 2006 16:25:35 GMT

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

Re: SELECT Statement question

Former Member — Thu, 03 Aug 2006 16:25:56 GMT

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.

Re: SELECT Statement question

Former Member — Thu, 03 Aug 2006 16:27:21 GMT

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.

Re: SELECT Statement question

Former Member — Thu, 03 Aug 2006 16:29:46 GMT

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.

CHECK NOT pernrz_tab[] IS INITIAL.

SELECT.....

FOR ALL ENTRIES IN pernrz_tab

WHERE ......

Re: SELECT Statement question

Former Member — Thu, 03 Aug 2006 16:34:23 GMT

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.

Re: SELECT Statement question

Former Member — Thu, 03 Aug 2006 16:38:08 GMT

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".

Re: SELECT Statement question

Former Member — Thu, 03 Aug 2006 17:15:52 GMT

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

Thanks once again.

Regards,

bw_newbie

Re: SELECT Statement question

Former Member — Thu, 03 Aug 2006 18:25:25 GMT

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.