Hi,

I'm attempting to run a large SQL statement from DBISAM in EDB. This SQL
statement has numerous Memory tables with select into's...

I've managed to get through and do what I believe is the correct SQL,
but when the code actually runs in EDBManager, I get an error saying
that the table already exists, even though I've put in code to test, and
drop the table if it does indeed exist.

Here's the start of my Script:

----------------------------------------------------
SCRIPT
BEGIN

DECLARE TmpCursor CURSOR WITH RETURN FOR Stmt;
DECLARE InfoCursor SENSITIVE CURSOR FOR InfoStmt;
PREPARE InfoStmt FROM 'SELECT * FROM Information.Tables WHERE Name=?';


/* Drop the table if it exists */
OPEN InfoCursor USING 'TmpClaimActives';
IF (ROWCOUNT(InfoCursor) > 0) THEN
 EXECUTE IMMEDIATE 'DROP TABLE TmpClaimActives';
 END IF;


/* Select active records to work with */
EXECUTE IMMEDIATE 'CREATE TEMPORARY TABLE "TmpClaimActives" AS (
Select ac.item, ac.TYPE, ac.ID , ac.AppID, AC.CourseID, AC.EndDate,
AC.CourseItem, AC.TraineeshipStartDate, ac.CourseState, AC.EmployerID,
AC.RTOID, AC.ActiveState, AC.Progression, AC.TrainingEndDate,
AC.TrainingStartDate, AC.CourseState
From ApprenticeCourse AC
inner join Apprentice A on (A.ID = AC.AppID)
where (A.Active)) WITH DATA';
----------------------------------------------------

It breaks when attempting to create the temporary table with the error:

ElevateDB Error #400 An error occurred with the statement at line 17 and
column 19 (The temporary table TmpClaimActives already exists in the
schema Default)

I believe it already exists, as I've run this statement before, but
shouldn't the script above it (that I stole from someone else's post on
this newsgroup drop the table.

I notice that when I do a line break on the IF Statement, it skips the
next line (the drop statement), so I'm assuming at that point it doesn't
believe the table exists.

Thanks & Regards

Adam.

Adam


I've just simulated the statement to find the temporary table in EDBManager and it returns no rows even though the table is there.

For temporary tables you need to use Information.TemporaryTables not Information.Tables.

Alternatively you could use the exception handling approach

BEGIN
 EXECUTE IMMEDIATE 'DROP TABLE TmpClaimActives';
EXCEPTION
/* I don't know what to do now */
END;

Roy Lambert [Team Elevate]

Hi Roy,
> I've just simulated the statement to find the temporary table in
EDBManager and it returns no rows even though the table is there.
>
> For temporary tables you need to use Information.TemporaryTables not Information.Tables.
>
> Alternatively you could use the exception handling approach
>
> BEGIN
>    EXECUTE IMMEDIATE 'DROP TABLE TmpClaimActives';
> EXCEPTION
> /* I don't know what to do now */
> END;

Thanks very much for pointing out the error of my ways. I like the idea
of trapping the exception - much easier to follow for me. (But probably
more 'lazy' scripting... but hey, if it works

Cheers

Adam.

Adam H,

I would seriously suggest that you look at EDB's VIEWS.

I had DBISAM applications which used the methods you describe. In moving to EDB I tried to use this way of working until I realised that VIEWS could replace them.

A view gives everything that DBISAM's temp-table would give, but doesn't need to be "managed" through "select into" type statements.

Have a look at them.

Adam

>I would seriously suggest that you look at EDB's VIEWS.
>
>I had DBISAM applications which used the methods you describe. In moving to EDB I tried to use this way of working until I realised that VIEWS could replace them.
>
>A view gives everything that DBISAM's temp-table would give, but doesn't need to be "managed" through "select into" type statements.
>
>Have a look at them.

I thoroughly agree with you for static selections but not for parameterised ones or where the view would return (I assume) an insensitive result set.

My systems are "unfortunate enough to have very little need for static views

Roy