Hello,
I have 3 questions about temporary tables:
(The dbproc they are related to is at the end of this mail.)
1) updatebility
In a dbproc I have 3 subsequent queries:
the first query creates a temp table and populates it with data from
two named_select_statements create just before
the second part is a loop that takes data from multiple rows,
concatenates them into a variable and then updates a column in the temp
table with that variable
the third and last query selects everything from the temp table.
=>RESULT, the returned rows are not updated.
I tried to add a `commit;` after the update statement but then the
dbproc would not compile.
Based on this text (http://www.sapdb.org/sap_db_features.htm)
`Temporary tables that will be destroyed if the application ends the
session. These tables can be created within a stored procedure and
selected from outside this procedure. They are updateable, although
there is no implicit update of the rows that created the temporary
table.`
I thought is was possible to update temp tables, but something needs a
few words explanation:
=> `they are updateable, although there is no implicit update ...`
What does that mean ?
2) For the same dbproc. In any isolation level I run the dbproc, the
first time it returns a result, the second time I gives an error that
the temp table already exists.
I thought the temporary tables where destroyed automatically. So I added
a `drop table temp.boreqselect` at the end of the dbproc, but that
crashed my sqlstudio instantly (it compiles without a problem though)
So what is the correct coding way. Delete explicitly or let the database
take care, but why is it still there then after the execution?
3) Something which is not entirely clear:
Are temporary tables unique to a session. In other words, if the dbproc
is executed multiple times a the same time, will each have its own
instance? I'm not entirely sure anymore after reading this:
`These tables can be created within a stored procedure and selected from
outside this procedure` This would suggest they are not.
What is the solution then. Create unique tablenames with STAMP?
I use maxdb 7.5 on SuSE linux 9
And sqlstudio 7.5 on mickeysoft windows2000.
The relevant part of the dbproc:
====start=======
TRY
CREATE TABLE TEMP.BOREQSELECT AS SELECT A.REQUESTID as REQUESTID,
A.OFFICEID, A.EMPLOYEEID, A.TIMESTAMP, A.TYPE, A.CLIENTNR, A.TITLE,
A.FIRSTNAME, A.LASTNAME, A.BIRTHDATE, A.STREET, A.STREETNO, A.ZIP,
A.CITY, B.RISKOBJECT, '' AS GUARANTEETXT
FROM DELTA.PHYSDATA A, DELTA.RISKOBJECTDATA B
WHERE A.REQUESTID=B.REQUESTID;
CATCH
IF $rc <> 0 THEN STOP ($rc, 'unexpected error in create
temp.boreqselect');
TRY
SELECT DISTINCT(A.REQUESTID) AS REQUESTID
FROM DELTA.T_REQUEST A, DELTA.T_REQUEST B, COMMON.T_OFFICE B1,
COMMON.T_EMPLOYEE B2, DELTA.T_REQUEST C
WHERE B.REQUESTID=A.REQUESTID
AND B1.OFFICEID=A.VALUE
AND B2.EMPLOYEEID=B.VALUE
AND C.REQUESTID=A.REQUESTID
AND A.KEY = 'OFFICEID'
AND A.VALUE = :officeid
AND B.KEY = 'EMPLOYEEID'
AND B.VALUE = :employeeid
AND B1.KEY = 'DESCRIPTION'
AND B2.KEY = 'NAME'
AND C.KEY = 'REQUEST_TYPE'
AND C.VALUE= :reqtype;
WHILE $rc<>0 DO BEGIN
FETCH INTO :requestid;
SELECT C.TXT
FROM DELTA.L_RQTE3GUARANTEE A, COMMON.T_E3GUARANTEE B,
COMMON.T_MESSAGELNG C
WHERE A.IDE3=B.IDE3
AND B.SEQMSGTEXT = C.SEQMSG
AND A.REQUESTID = :requestid
AND C.SEQLNG = :language;
WHILE $rc<>0 DO BEGIN
FETCH INTO :guarantee;
set guaranteetxt = guaranteetxt & guarantee;
END;
UPDATE TEMP.BOREQSELECT SET GUARANTEETXT = :guaranteetxt WHERE
REQUESTID = :requestid;
//commit;
END;
CATCH
IF $rc <> 100 THEN STOP ($rc, 'unexpected error guaranteetxt
composition');
TRY
DECLARE :$cursor CURSOR FOR SELECT * FROM TEMP.BOREQSELECT;
CATCH
IF $rc <> 0 THEN STOP ($rc, 'unexpected error in final select from
boreqselect');
// drop table temp.boreqselect;
===stop===
Regards,
Filip Sergeys
--
*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*
* System Engineer, Verzekeringen NV *
* www.verzekeringen.be *
* Oostkaai 23 B-2170 Merksem *
* 03/6416673 - 0477/340942 *
*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*
--
MaxDB Discussion Mailing List
For list archives: http://lists.mysql.com/maxdb
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
