Unabled to drop temp table
Thanks..
I have a Store Procedure that is calling from a Select Statement , this Sp..
create a temp table and then drop this temp table.. when i execute mi select
example
Select sp(table1.field1) var1 from table1;
this error message appear..
SQL Error (-675) : Illegal SQL statement in SPL routine.
this error appear when try to drop the table f inside the sp.
Anybody knows what happens? My Ids version is 11.5 , with version 7.3 it's
working fine..
Thanks.. Sorry for my english.. i'm from Mexico..
create procedure sp(t like table1.field1) returning int
select cve_1 from table2 where table2.field2 = t into temp f;
return (select cve_1 from f);
drop table f;
end procedure ;
Hi, Try changing your logic in SP.
You will have to replace following two lines with
return (select cve_1 from f); drop table f; ==================
let a = select cve_1 from f; (assuming it's one record only)
drop table f;
return a;
> To: ids@iiug.org> From: sperales@multiasis.com.mx> Subject: Unabled to drop
temp table [14084]> Date: Fri, 21 Nov 2008 15:06:36 -0500> > Thanks.. > > I
have a Store Procedure that is calling from a Select Statement , this Sp.. >
create a temp table and then drop this temp table.. when i execute mi select >
> example > > Select sp(table1.field1) var1 from table1; > > this error
message appear.. > > SQL Error (-675) : Illegal SQL statement in SPL routine.
> > this error appear when try to drop the table f inside the sp. > Anybody
knows what happens? My Ids version is 11.5 , with version 7.3 it's > working
fine.. > > Thanks.. Sorry for my english.. i'm from Mexico.. > > create
procedure sp(t like table1.field1) returning int > select cve_1 from table2
where table2.field2 = t into temp f; > > return (select cve_1 from f); > >
drop table f; > > end procedure ; > > >*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum. >
_________________________________________________________________
Access your email online and on the go with Windows Live Hotmail.
http://windowslive.com/Explore/Hotmail?ocid=TXT_TAGLM_WL_hotmail_acq_access_1120
08
>
>
> Thanks.. Sorry for my english.. i'm from Mexico..
>
No hay problema, además para eso está iiug-esp, ahí también hay gente muy
pila... así que sigo en español
Lo que comentas está muy raro sobre todo si funcionaba en 7.3 no veo por que
no deba funcionar en 11.5.
Lo que me parece extraño es que hagas un return y luego hagas el drop; tal
vez si haces un select into en una variable, luego borras la tabla temporal
y finalmente haces el return de la variable supongo que debería funcionar.
Si de todas formas te sigue saliendo el error puedes tratar de capturar el
error dentro del stored procedure con un "ON EXCEPTION" que tenga "WITH
RESUME" (http://publib.boulder.ibm.com/infocenter/idshelp/v115/index.jsp) y
en el bloque de la excepción borras la temporal... Pero la verdad no me
parece que esta sea la forma de hacerlo...
Trata así a ver como te va.
J.
2008/11/21 SANTIAGO PERALES <sperales@multiasis.com.mx>
> Thanks..
>
> I have a Store Procedure that is calling from a Select Statement , this
> Sp..
> create a temp table and then drop this temp table.. when i execute mi
> select
>
> example
>
> Select sp(table1.field1) var1 from table1;
>
> this error message appear..
>
> SQL Error (-675) : Illegal SQL statement in SPL routine.
>
> this error appear when try to drop the table f inside the sp.
> Anybody knows what happens? My Ids version is 11.5 , with version 7.3 it's
> working fine..
>
> Thanks.. Sorry for my english.. i'm from Mexico..
>
> create procedure sp(t like table1.field1) returning int
> select cve_1 from table2 where table2.field2 = t into temp f;>
> return (select cve_1 from f);
>
> drop table f;>
> end procedure ;
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
You are NOT permitted to make any data modifications within a stored
procedure that is called from within a SELECT statement. Note the error
description for SQLCODE -675
Art
$ finderr -675-675 Illegal SQL statement in SPL routine.
An SQL statement that is not allowed in an SPL routine was executed.
This error occurs when a routine is called from an SQL data
manipulation statement.
Example of error:
CREATE PROCEDURE testproc (arg INT, id INT) RETURNING INT;
UPDATE tab SET col = arg WHERE key = id; -- error
RETURN id;
END PROCEDURE;
SELECT col FROM tab WHERE testproc(tab.col, tab.key) = 10;
Do not use statements such as the preceding UPDATE statement in SPL
routines.
On Fri, Nov 21, 2008 at 3:06 PM, SANTIAGO PERALES <sperales@multiasis.com.mx
> wrote:
> Thanks..
>
> I have a Store Procedure that is calling from a Select Statement , this
> Sp..
> create a temp table and then drop this temp table.. when i execute mi
> select
>
> example
>
> Select sp(table1.field1) var1 from table1;
>
> this error message appear..
>
> SQL Error (-675) : Illegal SQL statement in SPL routine.
>
> this error appear when try to drop the table f inside the sp.
> Anybody knows what happens? My Ids version is 11.5 , with version 7.3 it's
> working fine..
>
> Thanks.. Sorry for my english.. i'm from Mexico..
>
> create procedure sp(t like table1.field1) returning int
> select cve_1 from table2 where table2.field2 = t into temp f;>
> return (select cve_1 from f);
>
> drop table f;>
> end procedure ;
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--
Art S. Kagel
Oninit (www.oninit.com)
IIUG Board of Directors (art@iiug.org)
Disclaimer: Please keep in mind that my own opinions are my own opinions and
do not reflect on my employer, Oninit, the IIUG, nor any other organization
with which I am associated either explicitly or implicitly. Neither do
those opinions reflect those of other individuals affiliated with any entity
with which I am affiliated nor those of the entities themselves.