topic Re: Check condition on remote table fields in Application Development and Automation Discussions

Check condition on remote table fields

Former Member — Thu, 09 Apr 2009 05:38:12 GMT

Hi guys,

and good morning all

Oracle table is defined:

CREATE TABLE KUPCI

(

POZIVNI VARCHAR2(3 BYTE),

MSISDN VARCHAR2(6 BYTE),

)

I need to check some field on oracle table. I want to do this as part of my abap include code:

IF itab-dat = <oracle_table_field1> or itab-tel = <oracle_table_field2>.

ACTION 1.

else.

ACTION 2.

how to do this?

thanks,

nihad

Edited by: nihad omerbegovic on Apr 9, 2009 7:45 AM

Re: Check condition on remote table fields

Former Member — Thu, 09 Apr 2009 05:51:25 GMT

In sap do you have oracle data base ?

If yes use native sql command to use oracle table otherwise you can interact directly with oracle database.

Re: Check condition on remote table fields

Former Member — Thu, 09 Apr 2009 05:55:25 GMT

Hi,

write this within exec.....endexec

Regards,

Siddarth

Re: Check condition on remote table fields

former_member222860 — Thu, 09 Apr 2009 06:05:16 GMT

Get the values of Oracle table into an itab,, then validate your condition with those itab values

Check this code

DATA: BEGIN OF itab_ora OCCURS 0,
        poz(3) type c,
        msi(6) type c,
      END OF itab_ora.  

EXEC SQL PERFORMING app_tab.
  SELECT * FROM KUPCI INTO :ITAB_ORA
ENDEXEC.

FORM app_tab.
  APPEND ITAB_ORA.
ENDFORM.

loop at itab_ora.
  if 
  -- condition
  else.
    -- condition
  endif.
 endloop.

Re: Check condition on remote table fields

Former Member — Thu, 09 Apr 2009 06:05:19 GMT

yes, I use native sql. exec...endexec.

but how I write syntax for oracle table field?

connect to 'dbcon_name'

exec sql.

IF itab-dat = pozivni@kupci or itab-tel = msisdn@kupci.

Action1.

endexec.

but is this syntax correct?

thanks,

Nihad

Re: Check condition on remote table fields

Former Member — Thu, 09 Apr 2009 06:33:07 GMT

thanks, this is nice idea...

So can I write like this?

exec sql.

........

loop at itab_ora.

IF itab-poz = itab_ora-poz or itab-tel = itab_ora-msi.

INSERT INTO KUPCI (....)VALUES (:itab-poz, :itab-tel.....).

endif.

endloop.

endexec.

Nihad

Re: Check condition on remote table fields

Former Member — Thu, 09 Apr 2009 06:35:20 GMT

no this is not a correct syntax....

use the if condition outside exec...endexec.

if <condition>.
   exec.
   ...
  endexec.
endif.

Regards,

Siddarth

Re: Check condition on remote table fields

Former Member — Thu, 09 Apr 2009 06:37:09 GMT

OK, but after condition I need to insert record into oracle table.

so,

IF condition1

.....

else.

......

exec sql.

insert into kupci (.,...) values (itab values)

endexec.

Nihad

Re: Check condition on remote table fields

former_member222860 — Thu, 09 Apr 2009 06:40:16 GMT

you can try like this:

loop at itab_ora.
  IF itab-poz = itab_ora-poz or itab-tel = itab_ora-msi.

    exec sql.
      INSERT INTO KUPCI (....)VALUES (:itab-poz, :itab-tel.....).
    endexec.

  endif.
endloop.

Re: Check condition on remote table fields

Former Member — Thu, 09 Apr 2009 06:42:58 GMT

use it this way...

exec sql.
........  " if this is an sql query
endexec.
loop at itab_ora.
IF itab-poz = itab_ora-poz or itab-tel = itab_ora-msi.
exec sql.
INSERT INTO KUPCI (....)VALUES (:itab-poz, :itab-tel.....).
endexec.
else.
"if you have some other query.
exec sql.
"sql query goes here
endexec.

endif.
endloop.

Regards,

Siddarth

Re: Check condition on remote table fields

Former Member — Thu, 09 Apr 2009 10:52:25 GMT

Hi,

when I debug program I got warning message "Statement is not accessible".

step by step check shows that I have endless loop at this line .

select * from zbt_bonus into table itab.

EXEC SQL PERFORMING app_tab.

SELECT * FROM KUPCI INTO :ITAB_ORA

ENDEXEC.

FORM app_tab. " this is where I got in enless loop

APPEND ITAB_ORA.**

ENDFORM.

have u idea what is wrong?

thanks,

Nihad

Re: Check condition on remote table fields

former_member222860 — Thu, 09 Apr 2009 11:13:09 GMT

Filter the selection criteria as per your requirement

EXEC SQL PERFORMING app_tab.
    SELECT POZIVNI, MSISDN 
      FROM KUPCI 
      INTO :ITAB_ORA
     WHERE POZIVNI = :P_POZ   " if any where-clause
ENDEXEC.


FORM app_tab. 
  APPEND ITAB_ORA.
ENDFORM.

Mahesh

Re: Check condition on remote table fields

Former Member — Thu, 09 Apr 2009 11:22:44 GMT

Thanks, when new record is added into itab I must check all records in oracle table,

specificly two fields: pozivni and ms.

so, isn't this good:

SELECT * FROM KUPCI INTO :ITAB_ORA "consider all records from table KUPCI

"then check if itab-poz and itab-tel already exists in itab_ora-pozivni and itab_ora-ms

Nihad

Edited by: nihad omerbegovic on Apr 9, 2009 1:23 PM

Re: Check condition on remote table fields

Former Member — Thu, 09 Apr 2009 11:26:15 GMT

Hi Nihab,

if the table is is in remote system( I mean another R/3 sytem) we can get the table and field details

through Function module



RFC_READ_TABLE.

if you pass the tabel name it will return the available fields in the table...

Regards,

Prabhudas

Re: Check condition on remote table fields

Former Member — Thu, 09 Apr 2009 11:34:55 GMT

No, this table is on another oracle database.

What Mahesh suggests is very fine, but I just need to put under loop

and check if my itab-poz and itab-tel fields already exist in remote table.

Thanks,

Nihad

Re: Check condition on remote table fields

Former Member — Tue, 14 Apr 2009 10:40:19 GMT

Hello, will this program update oracle table KUPCI?

I want sap table zbt_kupci and oracle KUPCI to be updated on click of button PROVJERA.

Can u check this code please?

REPORT ZTEST2_SCREEN .
TABLES: ZBT_BONUS.

TYPES : BEGIN OF TY_ITAB,
KLIJENT TYPE mandt,
POZIVNI(3) TYPE C,
TELEFON(6) TYPE C,
IME(25) TYPE C,
PREZIME(35) TYPE C,
NOVI(1) TYPE C,
DATUM TYPE dats,
KORISNIK(12) TYPE C,
END OF TY_ITAB.

data: begin of itab_ora occurs 0,
      ID TYPE NUM,
      SIF(13),
      POZIVNI(3),
      MS(6),
      SER(20),
      ID2(13),
      IME(25),
      PREZIME(35),
      NAZ(160),
      AD(160),
      DAT(9),
      NOV(1),
      end of itab_ora.  "same structure like ora table
DATA : WA_ITAB TYPE TY_ITAB.
data : wk_answer.
data: begin of itab occurs 0,
        man(3) type c,
        poz(3) type c,
        tel(6) type c,
        name(25) type c,
        surname(35) type c,
        novi(1) type c,
        dat type dats,
        usnam(12) type c,
     end of itab.

CALL SCREEN 100.

*&---------------------------------------------------------------------*
*&      Module  USER_COMMAND_0100  INPUT
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*

MODULE USER_COMMAND_0100 INPUT.

CASE SY-UCOMM.

when 'PROVJERA' .

call function 'POPUP_TO_CONFIRM_STEP'
exporting
defaultoption = 'Y'
textline1 = 'Da li zelite snimiti podatke?'(004)
textline2 = 'Sigurni?'(005)
titel = 'PROVJERA PODATAKA'(006)
start_column = 25
start_row = 6
cancel_display = 'X'
importing
answer = wk_answer.

case wk_answer.
when 'J'.
wa_itab-KLIJENT = ZBT_BONUS-MANDT.
wa_itab-pozivni = ZBT_BONUS-POZIVNI.
wa_itab-telefon = ZBT_BONUS-TELEFON.
wa_itab-ime = ZBT_BONUS-IME.
wa_itab-prezime = ZBT_BONUS-PREZIME.
wa_itab-novi = ZBT_BONUS-NOVI_KORISNIK.
wa_itab-datum = ZBT_BONUS-DATUM.
wa_itab-korisnik = SY-UNAME.

EXEC SQL.
  CONNECT TO 'ORADB'
ENDEXEC.

EXEC SQL.
*PERFORMING app_tab.
  SELECT * FROM KUPCI INTO :ITAB_ORA
ENDEXEC.
  APPEND ITAB_ORA.

exec sql.
Read itab_ora where itab_ora-pozivni = wa_itab-pozivni and
itab_ora-ms = wa_itab-telefon.
if sy-subrc <> 0.
update KUPCI using wa_itab.
endexec.

INSERT INTO ZBT_BONUS VALUES WA_ITAB.
COMMIT WORK.

Thanks,

Nihad

Re: Check condition on remote table fields

Former Member — Tue, 14 Apr 2009 12:06:28 GMT

I don't know how to take all data from oracle table kupci into itab_ora.

Is this code right please check from the previous post..

When I debugg I got some sql error....Is abap logic ok?

thanks,

Nihad

Re: Check condition on remote table fields

former_member222860 — Tue, 14 Apr 2009 12:56:13 GMT

Hi,

In order to get all the records into itab_ora

you 've to use like this :

EXEC SQL PERFORMING app_tab.
    SELECT * FROM KUPCI INTO :ITAB_ORA
ENDEXEC.
 
 
FORM app_tab. 
  APPEND ITAB_ORA.
ENDFORM.

Re: Check condition on remote table fields

Former Member — Tue, 14 Apr 2009 13:41:05 GMT

It gives me error

*Incorrect nesting: Before the statement "FORM", the structure *

*introduced by "CASE" must be concluded by "ENDCASE". *

I put this code before case btu again the same....

Also, In CASE I fill wa_itab with data from inuput/output fields....

nihad

Edited by: nihad omerbegovic on Apr 14, 2009 3:41 PM

Re: Check condition on remote table fields

former_member222860 — Wed, 15 Apr 2009 04:01:26 GMT

Hey Nihad,

keep this code at the extreme bottom of the code like all your Case statements etc.

FORM app_tab. 
  APPEND ITAB_ORA.
ENDFORM.