topic DELETE in SQL NATIVO in Application Development and Automation Discussions

DELETE in SQL NATIVO

Former Member — Fri, 03 Mar 2006 16:53:33 GMT

Hello ...

I am working with a table create in BD. ORACLE and y have the next code ...for to read.

EXEC SQL PERFORMING loop_output.

SELECT t$comp,

t$date,

t$year,

t$prdo,

t$ttyp,

t$lino,

t$spac,

t$leac,

t$typ1,

t$dim1,

t$typ2,

t$dim2,

t$typ3,

t$dim3,

t$typ4,

t$dim4,

t$typ5,

t$dim5,

t$refr,

t$dbcr,

t$amnt,

t$ccur,

t$spa2,

t$rate,

t$amth,

t$spa3,

t$bpid,

t$isup,

t$cvat,

t$vamt,

t$tipo,

t$actu,

t$tip2,

T$REFCNTD,

T$REFCNTU

INTO :wa

FROM baan.ttfgld915100@DB_BAAN

ENDEXEC.

and the next code for to write ...

exec sql.

INSERT INTO sapdev.tfgld915100

(COMP,TDATE,YEAR,PRDO,TTYP,LINO,SPAC,

LEAC,TYP1,DIM1,TYP2,DIM2,TYP3,DIM3,

TYP4,DIM4,TYP5,DIM5,REFR,DBCR,

AMNT,CCUR,SPA2,RATE,AMTH,SPA3,BPID,

ISUP,CVAT,VAMT,TIPO,ACTU,TIP2,REFCNTD,

REFCNTU)

VALUES

(:wa_comp,:wa_date,:wa_year,:wa_prdo,

:wa_ttyp,:wa_lino,

:wa_spac,:wa_leac,:wa_typ1,:wa_dim1,

:wa_typ2,:wa_dim2,

:wa_typ3,:wa_dim3,:wa_typ4,:wa_dim4,:wa_t yp5,:wa_dim5,

:wa_refr,:wa_dbcr,:wa_amnt,:wa_ccur,

:wa_spa2,:wa_rate,:wa_amth,:wa_spa3,

:wa_bpid,:wa_isup,

:wa_cvat,:wa_vamt,:wa_tipo,:wa_actu,

:wa_tip2,:wa_refcntd,

:wa_refcntu)

endexec.

EXEC SQL. COMMIT WORK;

endexec.

and i am utility the next code for to delete ...

exec sql.

DELETE FROM

baan.ttfgld915100@DB_BAAN

where comp = 400;

endexec.

EXEC SQL. COMMIT WORK;

endexec.

Re: DELETE in SQL NATIVO

Former Member — Fri, 03 Mar 2006 16:57:01 GMT

You excuse, my question is which is the codigo to erase???

Greetings

Re: DELETE in SQL NATIVO

Former Member — Fri, 03 Mar 2006 17:02:40 GMT

Hi Ramiro,

Can you be more clear as to what your query is??

Re: DELETE in SQL NATIVO

Former Member — Fri, 03 Mar 2006 17:20:46 GMT

Hi Ramiro

the Delete from <db.tablename) will delete the contents of the table

is that you want?

regards

kishore

Re: DELETE in SQL NATIVO

Former Member — Fri, 03 Mar 2006 17:28:34 GMT

hi ..kishore thanks ...

i need an query for to DELETE one row.

the SELECT and the INSERT is working perefect ..

both the delete don´t work ...

i was put the next code ..

exec sql.

DELETE FROM

sapdev.tfgld915100

baan.ttfgld915100@DB_BAAN

where comp = 400;

endexec.

EXEC SQL. COMMIT WORK;

endexec.

But, it sends me the next message..

ORA-00911: invalid character

my dude is ..i can to user DELETE .. for to erase one row in this TABLE ????

Re: DELETE in SQL NATIVO

Former Member — Fri, 03 Mar 2006 17:39:13 GMT

hI RAMIRO

YOU CAN USE

VAR = 400.

EXEC SQL.

DELETE SAPDEV.TFGLD915100 WHERE COMP = :VAR;

ENDEXEC.

EXEC SQL. COMMIT WORK;

endexec.

REGARDS

KISHORE

Re: DELETE in SQL NATIVO

Former Member — Fri, 03 Mar 2006 18:40:12 GMT

hi ..kishore ...

i dis a small change .. the var is enclosed in parentesis ..

wa_comp = 400.

exec sql.

DELETE FROM

sapdev.tfgld915100

where comp = (:WA_COMP);

endexec.

baan.ttfgld915100@DB_BAAN

EXEC SQL. COMMIT WORK;

endexec.

both ...it continue send me the message ..

"ORA-00911: invalid character"

Greetings.

Re: DELETE in SQL NATIVO

Former Member — Fri, 03 Mar 2006 19:51:29 GMT

Try leaving off the semi-colons:


wa_comp = 400.

exec sql.
  DELETE FROM
  sapdev.tfgld915100
  where comp = (:WA_COMP)
endexec.

* baan.ttfgld915100@DB_BAAN

EXEC SQL.
  COMMIT WORK
endexec.

Re: DELETE in SQL NATIVO

Former Member — Fri, 03 Mar 2006 21:42:50 GMT

hi, Rob

not, is the same error ....

i have my dude if this command it permitedd in SQL NATIVO...

Greetings

Re: DELETE in SQL NATIVO

Former Member — Fri, 03 Mar 2006 22:17:59 GMT

I think you can actually do more in native SQL. Try:


wa_comp = 400.

exec sql.
  DELETE FROM  sapdev.tfgld915100
  where comp = :WA_COMP
endexec.

* baan.ttfgld915100@DB_BAAN

EXEC SQL.  
  COMMIT WORK
endexec.

(removing the parenthesis).

Rob

Re: DELETE in SQL NATIVO

Former Member — Fri, 03 Mar 2006 22:29:41 GMT

hi Rob ..

with this code i can`t to active the program send me the next error ...

Field "WA_COMP;" is unknown. It is neither in one of the specified.

Greetings ..

Re: DELETE in SQL NATIVO

Former Member — Fri, 03 Mar 2006 23:17:17 GMT

This compiled without a syntax error in my system:


REPORT ztest.

DATA: wa_comp(3).

wa_comp = 400.

EXEC SQL.
  DELETE FROM  sapdev.tfgld915100
  where comp = :WA_COMP
ENDEXEC.

* baan.ttfgld915100@DB_BAAN

EXEC SQL.
  COMMIT WORK
ENDEXEC.

Rob

Re: DELETE in SQL NATIVO

Former Member — Fri, 03 Mar 2006 23:38:02 GMT

My Friend Rob ...

we have succeeded ... My error was the ; at end of HERE..

But ... it´s o.k.

thanks ... very much

i believe that this day was of beneficial .. bye

Re: DELETE in SQL NATIVO

Former Member — Fri, 03 Mar 2006 23:45:54 GMT

Glad to help - don't forget to close the thread.

Rob