Skip to Content
author's profile photo Former Member
Former Member

SELECT Statement question

Posted on Aug 03, 2006 at 04:17 PM 14 Views

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

ABAP Development
Add comment
10|10000 characters needed characters exceeded

6 Answers

  • Best Answer
    author's profile photo Srinivas Adavi
    Srinivas Adavi
    Posted on Aug 03, 2006 at 04:34 PM

    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.

    Add comment
    10|10000 characters needed characters exceeded

    • Srinivas Adavi Former Member
      Aug 03, 2006 at 06:25 PM

      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.

  • author's profile photo Former Member
    Former Member
    Posted on Aug 03, 2006 at 04:20 PM

    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.

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member
      Aug 03, 2006 at 04:27 PM

      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.

  • author's profile photo Former Member
    Former Member
    Posted on Aug 03, 2006 at 04:19 PM

    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

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member Former Member
      Aug 03, 2006 at 04:38 PM

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

  • author's profile photo Former Member
    Former Member
    Posted on Aug 03, 2006 at 04:25 PM

    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.

    Add comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Aug 03, 2006 at 04:19 PM

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

    regards

    srikanth

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member
      Aug 03, 2006 at 04:21 PM

      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

  • author's profile photo Srinivas Adavi
    Srinivas Adavi
    Posted on Aug 03, 2006 at 04:29 PM

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

    Add comment
    10|10000 characters needed characters exceeded