Check condition on remote table fields

[Former Member]

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

[Former Member]

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.

[Former Member]

Hi,

write this within exec.....endexec

Regards,

Siddarth

[Former Member]

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

[Former Member]

no this is not a correct syntax....

use the if condition outside exec...endexec.

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

Regards,

Siddarth

[Former Member]

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

[Former Member]

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

[Former Member]

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

[former_member222860]

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

[Former Member]

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

[former_member222860]

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.

[Former Member]

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

[former_member222860]

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.

[Former Member]

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

[Former Member]

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

[Former Member]

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

[Former Member]

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

[former_member222860]

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.

[Former Member]

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

[former_member222860]

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.

[Former Member]

Hello Mahesh,

I put it like this but again syntax check gives me error

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 PERFORMING app_tab.
    SELECT * FROM KUPCI INTO :ITAB_ORA
ENDEXEC.
FORM app_tab.
  APPEND ITAB_ORA.
ENDFORM.

[former_member222860]

where's the ENDCASE,,, this Form..EndForm should come after EndCase.

Pl. check again

[Former Member]

I put this after endcase and endmodule

.....................
ENDCASE.

ENDMODULE.                 " USER_COMMAND_0100  INPUT

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

FORM app_tab.
  APPEND ITAB_ORA.
ENDFORM.

It says

+Statement is not accessible. +

Can u give me your email adress if you could check my code?

Thanks a lot

Nihad

[former_member222860]

Here's the sequence to follow:

* Select statement for fetching the data

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


*Here your Condition Check

ENDCASE.

* And others
 
ENDMODULE.                 " USER_COMMAND_0100  INPUT
 

* Here comes this finally 
 
FORM app_tab.
  APPEND ITAB_ORA.
ENDFORM.

And hope this time, you can resolve

[Former Member]

Thanks, now final shape is this

* Data Declarations
tables:
types:
data:

* open db connection
EXEC SQL.
  CONNECT TO 'ORADB'
ENDEXEC.

* Select statement for fetching the data
EXEC SQL PERFORMING app_tab.
    SELECT * FROM KUPCI INTO :ITAB_ORA
ENDEXEC.
CALL SCREEN 100.

*&---------------------------------------------------------------------*
*&      Module  USER_COMMAND_0100  INPUT
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
MODULE USER_COMMAND_0100 INPUT.

CASE SY-UCOMM.

* button on screen
when 'PROVJERA' .
............
case wk_answer.
* if answer is confirmed
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.

*Here your Condition Check
exec sql.
Read itab_ora where itab_ora-pozivni = wa_itab-pozivni and
itab_ora-ms = wa_itab-telefon.

if sy-subrc <> 0.

* update oracle table
update KUPCI using wa_itab.
endexec.
* update sap table
INSERT INTO ZBT_BONUS VALUES WA_ITAB.
COMMIT WORK.

endcase.  " end of case wk_answer.
ENDCASE. " end of CASE SY-UCOMM.
* And others
EXEC SQL.
  DISCONNECT :'ORADB'
ENDEXEC.
ENDMODULE.                 " USER_COMMAND_0100  INPUT

* Here comes this finally 
 FORM app_tab.
  APPEND ITAB_ORA.
ENDFORM.

I concluded like this. When testing, I do not get oracle table updated :(((

Nihad

[former_member222860]

In the Exec Sql .. Endexec, READ itab should not be used.

Check in the debug-mode , whether itab_ora is populating with values.

Also close the if..endif

* open db connection
EXEC SQL.
  CONNECT TO 'ORADB'
ENDEXEC.

* Select statement for fetching the data
EXEC SQL PERFORMING app_tab.
  SELECT * FROM KUPCI INTO :ITAB_ORA
ENDEXEC.
CALL SCREEN 100.

*&---------------------------------------------------------------------*
*&      Module  USER_COMMAND_0100  INPUT
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
MODULE USER_COMMAND_0100 INPUT.

  CASE SY-UCOMM.

* button on screen
    when 'PROVJERA' .
      ............
      case wk_answer.
* if answer is confirmed
        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.

*Here your Condition Check
          Read itab_ora where itab_ora-pozivni = wa_itab-pozivni and
          itab_ora-ms = wa_itab-telefon.

          if sy-subrc  0.

* update oracle table

            exec sql.                        " Observe the Change here
              update KUPCI using :wa_itab.
            endexec.

* update sap table
            INSERT INTO ZBT_BONUS VALUES WA_ITAB.
            COMMIT WORK.

        endcase.  " end of case wk_answer.

    ENDCASE. " end of CASE SY-UCOMM.

* And others
    EXEC SQL.
      DISCONNECT :'ORADB'
    ENDEXEC.

  ENDMODULE.                 " USER_COMMAND_0100  INPUT

* Here comes this finally
FORM app_tab.
  APPEND ITAB_ORA.
ENDFORM.                    "app_tab

Mahesh

[Former Member]

+Unable to interpret "ITAB_ORA". Possible causes of error: Incorrect +

+spelling or comma error. +

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.

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

I got this error, data is not populated in itab_ora from KUPCI... I think there is a mess with this

somewhere... and also my wa_itab is not the same structure like KUPCI...so maybe it is better to do INSERT directly field by field into KUPCI values wa_itab?

I am concerned about this condition and I feel myself very annoying on this topic....

Thanks for help.

Nihad