1. Scott Roberts
  2. Valentina Database ADK
  3. Mittwoch, Juli 11 2018, 09:03 PM
  4.  Abonnieren via E-Mail
Is it possible to use temporary tables in stored procedures/functions? When I try to execute the following, I receive Kernel error 0x70503. Table "house_rooms" not found.

I have attached a sample database to which I'm attempting to add the procedure.


create or replace procedure test_ram_table
(
)
begin
create ram table house_rooms as
select
rooms.*
from
rooms
join
houses on lnk_houses_rooms
where
houses.RecID = 1;

select
*
from
house_rooms;

drop table house_rooms;
end
Anhänge
Kommentar
There are no comments made yet.
Ruslan Zasukhin Akzeptierte Antwort
Hi Scott,

sorry for the delay, Ivan should return from small vacation and I expect today (Sat) will answer to both your questions.

Meanwhile, let me point this WIKI page about Valentina SQL
https://goo.gl/Uqeh4R

Look at section Stored Procedures, EXECUTE statement,
maybe this syntax will help execute CREATE TABLE cmd
Kommentar
There are no comments made yet.
Ivan Smahin Akzeptierte Antwort
Technically, you can create tables in the SP, but it is useless because you can not use them there.
All mentioned tables in the SP body must be created before SP.

For your case (I suppose it is about some really hard query, so creating tmp table could be solution):
- you can use a VIEW

CREATE OR REPLACE VIEW v1 AS select
rooms.*
from
rooms
join
houses on lnk_houses_rooms
where
houses.RecID = 1;


create or replace procedure test_ram_table
(
)
begin
select
*
from
v1;
end


- use a CURSOR:

create or replace procedure test_ram_table
(
)
begin
DECLARE a String(20);
DECLARE b Long;
...
DECLARE cur1 CURSOR FOR
select
rooms.*
from
rooms
join
houses on lnk_houses_rooms
where
houses.RecID = 1;

OPEN cur1;

BEGIN
FETCH FIRST cur1 INTO b, a;
LOOP
...
FETCH NEXT cur1 INTO b, a;
END LOOP
EXCEPTION WHEN ERR_CURSOR_WRONG_POSITION THEN
LEAVE

CLOSE cur1;
end
Kommentar
There are no comments made yet.
Scott Roberts Akzeptierte Antwort
Thanks for your response. I'm not able to use a view because the RecID in my original statement is a parameter. I used a hard-coded value for my example.

I was attempting to create a temporary table in order to simplify a query that included a subquery. It was also a situation where I had planned to use the temporary table in more than one stored procedure but then I found that I could not return a table from a function.

I have figured out another way to accomplish what I need to do.

Thanks for your help.
Kommentar
There are no comments made yet.
  • Seite :
  • 1


There are no replies made for this post yet.
However, you are not allowed to reply to this post.

Categories

Announcements & News
  1. 0 subcategories
Valentina Studio
  1. 2 subcategories
Valentina Server
  1. 4 subcategories
Valentina Database ADK
  1. 0 subcategories
Valentina Reports ADK
  1. 0 subcategories
Other Discussions
  1. 2 subcategories
BETA Testing
  1. 0 subcategories
Education & Research
  1. 0 subcategories