1. Scott Roberts
  2. Valentina Database ADK
  3. Wednesday, July 11 2018, 09:03 PM
  4.  Subscribe via email
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
Attachments (1)
Comment
There are no comments made yet.
Ruslan Zasukhin Accepted Answer
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
Comment
There are no comments made yet.
  1. more than a month ago
  2. Valentina Database ADK
  3. # 1
Ivan Smahin Accepted Answer
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
Comment
There are no comments made yet.
  1. more than a month ago
  2. Valentina Database ADK
  3. # 2
Scott Roberts Accepted Answer
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.
Comment
There are no comments made yet.
  1. more than a month ago
  2. Valentina Database ADK
  3. # 3
  • Page :
  • 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