exec sql: manage only one value

[former_member254358]

Hi,

I'm using EXEC SQL sentence where the used select sentence only returns one value, for example:

SELECT MYID

FROM MYEXTERNALTABLE

WHERE MYID = '0000000001'.

(MYID is PK)

Is it necessary to use a recorset in the abap code?

How I have to manage this case in abap?

Thanks in advance.

[Former Member]

You have to OPEN CURSOR FOR your SELECT and then within a DO, FETCH NEXT CURSOR.

Like this:

REPORT zscntest NO STANDARD PAGE HEADING LINE-SIZE 255.  " FISKROB9999

TABLES payr.

DATA: wa_src LIKE payr,

       i_src  LIKE TABLE OF payr,

       l_str1(20) TYPE c.

PARAMETERS: l_string(20) TYPE c.

START-OF-SELECTION.

   CONCATENATE '%' l_string '%' INTO l_str1.

   EXEC SQL.

     OPEN C FOR

     SELECT *

     FROM  PAYR

     WHERE UPPER(ZANRE)  LIKE  :L_STR1

     OR    UPPER(ZNME1)  LIKE  :L_STR1

     OR    UPPER(ZNME2)  LIKE  :L_STR1

     OR    UPPER(ZNME3)  LIKE  :L_STR1

     OR    UPPER(ZNME4)  LIKE  :L_STR1

   ENDEXEC.

   DO.

     EXEC SQL.

       FETCH NEXT C INTO :wa_SRC

     ENDEXEC.

     IF sy-subrc <> 0.

       EXIT.

     ENDIF.

     APPEND wa_src TO i_src.

   ENDDO.

   EXEC SQL.

     CLOSE C

   ENDEXEC.

   LOOP AT i_src INTO wa_src.

     WRITE: /001 wa_src-zanre,

                 wa_src-znme1,

                 wa_src-znme2,

                 wa_src-znme3,

                 wa_src-znme4.

   ENDLOOP.

Rob

Message was edited by: Rob Burbank

[former_member254358]

Thanks for your help.

In my case the select only returns one field, for example:

select myid from......

I have just create a structure with only one field of the same type that which is

returned in the select but I get an error:

    begin of wa,

    matnr1 type matnr,

    end of wa.

Regards.

[Former Member]

Hi David,

I think you need to use cursor here.

open connection.

Data cur type cursor.

exec sql.

open cur for

SELECT MYID FROM MYEXTERNALTABLE WHERE MYID = '0000000001

endexec.

exec sql.

fetch next cur for :wa

endexec.

append wa to itab.

exec sql.

close cur

endeexec.

close connection.

This will give you all records in itab. Hope it works

Regards,

Pranav.

[former_member254358]

Thanks for your reply,

I have defined an structure with only one field of the same type than MYID and in the fetch sentence I get an error:

    begin of wa,

    matnr1 type matnr,

    end of wa.

Regards.

[former_member254358]

Now I'm getting a dump:

   Error 1007 has occurred on the current database connection "ORCX".

   Database error text: con=2(192.168.7.203:1521/ORCX): ORA-01007: variable not in

    select list

   Triggering SQL statement: "FETCH NEXT "

Any suggestion?

Thanks a again.

[former_member254358]

Another detail is that my select will only return 0 or 1 records not more.

Could be this the problem?

[Former Member]

Your work area has to have the same structure as the table from which you are Selecting.

I tested the code before posting. It works. Try it as is first.

Rob

[former_member186741]

Use Rob's test program exactly as he has posted it just to see how the concept works. Then you can change it slightly to suit your exact requirements.

[Former Member]

Hi,

If you select query doesn't retrieve anything then it will give dump for fetch next.

In your select query you are fetching MYID and putting it in matnr type variable. Your wa should be of type myid or fetch matnr in select. Also if you are giving condition like select myid where myid = '......' it will fetch only one record as myid is a primary key.

Regards,

Pranav,

[former_member254358]

Sorry,

there is something I'm doing wrong.

How would you define wa?  In my case, the select sentence only returns one variable.

Is this correct?

    types:

    begin of wa,

    MYID(10) type c,

    end of wa.

The varible returned by the select is of the same type.

Thanks.

[Former Member]

No - you have to define it the same as your external table.

It might help you to press F1 on EXEC SQL, OPEN CURSOR and FETCH NEXT CURSOR.

Rob

Message was edited by: Rob Burbank

[VenkatRamesh_V]

Hi David,

Try,

EXEC SQL PERFORMING subroutine_name.

    SELECT

    RECORD_ID

    , POST_DATE

    , GL_CODE

    , BANK_ID

    , BANK_NAME

    , ROI

    , SANC_LIMIT

    , AVAIL_DC_IND

    , AVALD_LIMIT

    , BAL_DC_IND

    , BALANCE

    , PRO_CEN_CODE

    , PRO_CEN_DESC

    INTO

       :wa_str-RECORD_ID

     , :wa_str-POST_DATE

     , :wa_str-GL_CODE

     , :wa_str-BANK_ID

     , :wa_str-BANK_NAME

     , :wa_str-ROI

     , :wa_str-SANC_LIMIT

     , :wa_str-AVAIL_DC_IND

     , :wa_str-AVALD_LIMIT

     , :wa_str-BAL_DC_IND

     , :wa_str-BALANCE

     , :wa_str-PRO_CEN_CODE

     , :wa_str-PRO_CEN_DESC

    FROM

    SFL_SAP_HPCCBALANCE

    where POST_DATE  = :date

    and GL_CODE      = :gl

    and BANK_ID      = :bankid

    and PRO_CEN_CODE = :pc_code

  ENDEXEC.

FORM subroutine_name.

  IF NOT  wa_str IS INITIAL.

    flag_validate = abap_true.

    IF ( ( lv_ex_sanc  NE wa_str-SANC_LIMIT   ) OR

         ( lv_ex_roi   NE wa_str-ROI          ) OR

         ( lv_ex_avlt  NE wa_str-AVALD_LIMIT  ) OR

         ( lv_ex_bal   NE wa_str-BALANCE      ) ).

      MOVE-CORRESPONDING wa_final to wa_upd.

      wa_upd-record_id = wa_str-RECORD_ID.

      wa_upd-POST_DATE = wa_str-POST_DATE.

      APPEND wa_upd to it_upd.

      CLEAR wa_upd.

    ENDIF.

  ENDIF.

ENDFORM.

Values will be updated in structure FORM subroutine_name.

Hope it helpful,

Regards,

Venkat