Create a session, a database and a script. Execute the script. All is well. Execute the script again. Get the error below. The only way round the problem I have found so far is to free all three objects and start again, which carries the overhead of a session login and database re-open. Any ideas? The script and the procedure it invokes are below.

----------------------------------------------------------
Error #400 An error occurred with the  routine at line 6 and column 7 (An error occurred
with the statement at line 15 and column 19 (The temporary table CleanInfo already exists in the schema Default))
----------------------------------------------------------

SCRIPT
BEGIN
   Declare crsr Cursor With Return for stmt;
   
   Call CleanUnitStatusReport2({On Date});
   Prepare stmt From 'Select * From CleanInfo Where (CleanStatusId = 1 or {Include All})';
   Open crsr;
END

-------------------------

CREATE PROCEDURE "CleanUnitStatusReport" (
  StartDate Date,
  EndDate Date
)
BEGIN
Declare crsr Cursor With Return for stmt;
Declare InfoCursor Cursor For InfoStmt;
Declare SheetChangeDays integer;

PREPARE InfoStmt FROM 'SELECT * FROM Information.Tables WHERE Name=''CleanInfo''';
OPEN InfoCursor;
IF (ROWCOUNT(InfoCursor) > 0) THEN
EXECUTE IMMEDIATE 'DROP TABLE "CleanInfo"';
END IF;

CLOSE InfoCursor;

Execute Immediate 'Create Temporary Table CleanInfo(
d Date,
RoomId Integer,
RoomTypeId Integer,
RoomNumber varchar(255),
RoomTypeName varchar(255),
CleanStatusId Integer,
ArrivalResNumber Integer,
DepartureResNumber Integer,
BlockedStatus varchar(50),
InRoomResNumber Integer,
IsCheckedIn boolean,
ArrivalTime Integer,
MaidInfo varchar(40),
LastName varchar(255),
FirstName varchar(255),
Company varchar(255),
NumAdult Integer,
NumChildren Integer,
CotCribHCCount Integer,
ArrivalDateOfInRoomRes Date,
DepartureDateOfInRoomRes Date,
Task varchar(255)
)';

Execute Immediate 'Create Index ciresnum on CleanInfo (InRoomResNumber Asc)';

Execute Immediate
'Insert Into CleanInfo(RoomId, RoomTypeId, RoomNumber, RoomTypeName, CleanStatusId, d, ArrivalResNumber, DepartureResNumber, InRoomResNumber)
Select cfgRoom.ID RoomId,
cfgRoom.RoomTypeId,
cfgRoom.RoomNumber,
(Select Name from cfgRoomType Where ID = cfgRoom.RoomTypeId) as RoomType,
cfgRoom.CleanStatusId,
Dates.d,
(Select ResNumber from tblGuestDates gDates inner join tblGuestRoomRates groomnum on gDates.ResNumber = groomnum.ResNumber
Where Arrival = Dates.d and groomnum.RoomId = cfgRoom.ID
and Cancelled Is Null Range 1 to 1) as ArrivalResNumber,
(Select ResNumber from tblGuestDates gDates inner join tblGuestRoomRates groomnum on gDates.ResNumber = groomnum.ResNumber
Where Departure = Dates.d and groomnum.RoomId = cfgRoom.ID
and Cancelled Is Null Range 1 to 1) as DepartureResNumber,
--       (Select BlockType from tblroomblock Where StartDate <= Dates.d and EndDate >= Dates.d and tblroomblock.RoomId = cfgRoom.Id) as blocked,
(Select ResNumber from tblGuestDates gDates inner join tblGuestRoomRates groomnum on gDates.ResNumber = groomnum.ResNumber
Where Arrival <= Dates.d and Departure > Dates.D and groomnum.RoomId = cfgRoom.ID
and Cancelled Is Null Range 1 to 1) as InRoomResNumber
from cfgRoom, Dates
Where d >= ? and d <= ?' using StartDate, EndDate;

EXECUTE IMMEDIATE 'Update CleanInfo
Set (ArrivalTime,
IsCheckedIn,
MaidInfo,
NumAdult,
NumChildren,
CotCribHCCount) =
(Select ArrivalTimeId,
IsCheckedIn,
MaidInfo,
Adult,
Children,
Coalesce("Cot",0) + Coalesce("Crib", 0) + Coalesce("Highchair", 0)
From tblGuestGeneral Where ResNumber = InRoomResNumber)';

EXECUTE IMMEDIATE 'Update CleanInfo
Set
(LastName,
FirstName,
Company) =
(Select LastName,
FirstName,
Company
from tblNames Where ResNumber=InRoomResNumber and NameType=''GST'')';

EXECUTE IMMEDIATE 'Update CleanInfo
Set
(ArrivalDateOfInRoomRes,
DepartureDateOfInRoomRes) =
(Select Arrival,
Departure
from tblGuestDates Where ResNumber=InRoomResNumber)';

-- BLOCK all of the rooms that might have blocks on them
Execute Immediate
'Update CleanInfo
Set ArrivalResNumber=-1
Where RoomId in (Select RoomId From tblRoomBlock Where d between StartDate and EndDate)
and ArrivalResNumber Is Null';

-- BLOCK all of the rooms from yesterday that might have blocks on them
Execute Immediate
'Update CleanInfo
Set DepartureResNumber=-1
Where RoomId in (Select RoomId From tblRoomBlock Where (d - Interval ''1'' Day) between StartDate and EndDate)
and DepartureResNumber Is Null';



-- === Calculate the various 'tasks'

-- Rooms that are blocked on both days.
Execute Immediate
'Update CleanInfo
Set Task = ''Maintenance''
Where ArrivalResNumber = -1 and (DepartureResNumber = -1 or DepartureResNumber Is Null)
';

-- Rooms that were blocked yesterday, not today, but no one arriving
Execute Immediate
'Update CleanInfo
Set Task = ''Maint Clean''
Where (ArrivalResNumber is Null) and (DepartureResNumber = -1)
';

-- Nobody is coming or going, nobody is in the room at all, and it's clean.
Execute Immediate
'Update CleanInfo
Set Task=(Select Value from tblsystemsettings Where Item=''VacantAndClean'' and Section=''housekeeping.status'')
Where Task Is Null and arrivalResNumber is Null and departureResNumber is null and InRoomResNumber is null and CleanStatusID = 0';

-- Nobody is coming or going, nobody is in the room at all, and it's dirty.
Execute Immediate
'Update CleanInfo
Set Task=(Select Value from tblsystemsettings Where Item=''Dirty'' and Section=''housekeeping.status'')
Where Task Is Null and arrivalResNumber is Null and departureResNumber is null and InRoomResNumber is null and CleanStatusID = 1';

-- Nobody is coming or going, nobody is in the room at all, and it's inspected.
Execute Immediate
'Update CleanInfo                 
Set Task=(Select Value from tblsystemsettings Where Item=''VacantAndInspected'' and Section=''housekeeping.status'')
Where Task Is Null and arrivalResNumber is Null and departureResNumber is null and InRoomResNumber is null and CleanStatusID = 2';

-- Someone is in the room
-- We either need to change the sheets - or makeup the room...

-- a) See if we need to change the sheets daily for a specific room type.
Execute Immediate
'Update CleanInfo
Set Task=(Select Value from tblsystemsettings Where Item=''Sheets'' and Section=''housekeeping.status'')
Where Task Is Null and arrivalResNumber is Null and departureResNumber is Null and InRoomResNumber is not null
and RoomTypeId in (Select ID from cfgroomtype Where ChangeSheetsDaily)';

-- b) See if we have some sheets to change based on ChangeSheets setting
-- First, get the setting
PREPARE InfoStmt FROM    'Select Cast(Value as Integer) IntValue from tblsystemsettings Where Item=''SheetChangeDays'' and Section=''housekeeping.general''';
OPEN InfoCursor;
IF (ROWCOUNT(InfoCursor) > 0) THEN
Fetch First From InfoCursor('IntValue') Into SheetChangeDays ;
END IF;
CLOSE InfoCursor;

if (SheetChangeDays <> 0) Then
--      Execute Immediate
--      'Update CleanInfo Set Task=Cast(? + '''' as varchar(255)) Where Task Is Null' Using SheetChangeDays;
Execute Immediate
'Update CleanInfo
Set Task=(Select Value from tblsystemsettings Where Item=''Sheets'' and Section=''housekeeping.status'')
Where Task Is Null and arrivalResNumber is Null and departureResNumber is Null and InRoomResNumber is not null
and (d - ArrivalDateOfInRoomRes) mod ' +  Cast(SheetChangeDays as varchar(10)) + ' = 0 ';
end if;


-- z) All of the rest are then makeup
Execute Immediate
'Update CleanInfo
Set Task=(Select Value from tblsystemsettings Where Item=''makeup'' and Section=''housekeeping.status'')
Where Task Is Null and arrivalResNumber is Null and departureResNumber is Null and InRoomResNumber is not null';

-- Somebody is leaving, but no one arriving.
Execute Immediate
'Update CleanInfo
Set Task= (Select Value from tblsystemsettings Where Item=''Complete'' and Section=''housekeeping.status'')
Where Task Is Null and arrivalResNumber is null and departureResNumber is not null';

-- Someone is arriving into an unoccupied room, and it's dirty
Execute Immediate
'Update CleanInfo
Set Task=(Select Value from tblsystemsettings Where Item=''Setup'' and Section=''housekeeping.status'') + '' - Dirty''
Where Task Is Null and DepartureResNumber Is Null and ArrivalResNumber Is Not Null and CleanStatusID = 1';

-- Someone is arriving into an unoccupied room, and it's clean
Execute Immediate
'Update CleanInfo
Set Task=(Select Value from tblsystemsettings Where Item=''Setup'' and Section=''housekeeping.status'')
Where Task Is Null and DepartureResNumber Is Null and ArrivalResNumber Is Not Null and CleanStatusID = 0';

-- Someone is departing and someone new is coming in.
Execute Immediate
'Update CleanInfo
Set Task=(Select Value from tblsystemsettings Where Item=''ChangeOver'' and Section=''housekeeping.status'')
Where Task Is Null and arrivalResNumber <> departureResNumber';

Prepare stmt From 'Select * From CleanInfo';
Open crsr;
--   Execute stmt;

END

VERSION 1.00

<< Create a session, a database and a script. Execute the script. All is
well. Execute the script again. Get the error below. The only way round the
problem I have found so far is to free all three objects and start again,
which carries the overhead of a session login and database re-open. Any
ideas? The script and the procedure it invokes are below. >>

What you want to change is the indicated line:

CREATE PROCEDURE "CleanUnitStatusReport" (
  StartDate Date,
  EndDate Date
)
BEGIN
Declare crsr Cursor With Return for stmt;
Declare InfoCursor Cursor For InfoStmt;
Declare SheetChangeDays integer;

PREPARE InfoStmt FROM 'SELECT * FROM Information.TemporaryTables WHERE
Name=''CleanInfo''';  <<<<<<<<<<<<<<<<<<<< Here
OPEN InfoCursor;
IF (ROWCOUNT(InfoCursor) > 0) THEN
EXECUTE IMMEDIATE 'DROP TABLE "CleanInfo"';
END IF;

If you have any other questions, please let me know.

Tim Young
Elevate Software
www.elevatesoft.com