topic Re: exec sql: manage only one value in Application Development and Automation Discussions

exec sql: manage only one value

former_member254358 — Wed, 03 Jun 2015 13:49:47 GMT

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.

Re: exec sql: manage only one value

Former Member — Wed, 03 Jun 2015 13:58:57 GMT

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

Re: exec sql: manage only one value

Former Member — Thu, 04 Jun 2015 09:58:17 GMT

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.

Re: exec sql: manage only one value

former_member254358 — Thu, 04 Jun 2015 13:10:45 GMT

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.

Re: exec sql: manage only one value

former_member254358 — Thu, 04 Jun 2015 13:12:48 GMT

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.

Re: exec sql: manage only one value

former_member254358 — Thu, 04 Jun 2015 14:05:50 GMT

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.

Re: exec sql: manage only one value

former_member254358 — Thu, 04 Jun 2015 14:32:10 GMT

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

Could be this the problem?

Re: exec sql: manage only one value

Former Member — Thu, 04 Jun 2015 16:23:26 GMT

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

Re: exec sql: manage only one value

former_member186741 — Fri, 05 Jun 2015 00:06:18 GMT

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.

Re: exec sql: manage only one value

Former Member — Fri, 05 Jun 2015 05:42:10 GMT

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,

Re: exec sql: manage only one value

former_member254358 — Fri, 05 Jun 2015 13:39:17 GMT

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.

Re: exec sql: manage only one value

Former Member — Fri, 05 Jun 2015 15:30:52 GMT

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

Re: exec sql: manage only one value

VenkatRamesh_V — Fri, 05 Jun 2015 16:26:43 GMT

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.

ENDFORM.

Values will be updated in structure FORM subroutine_name.

Hope it helpful,

Regards,

Venkat