473,320 Members | 2,071 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

INSERTIN' INTO TEMPORAL TABLE THE RESULT OF A STORE PROCEDURE

i want to insert into a temporal table the result of a store procedure.
on sql server the sentence would look like this (already working)

INSERT INTO #SHIPINFO
exec TESTDTA.S59RSH05 @SCBILLTO, @INID, @ADRSTYPE

i tried to do the same on DB2, meaning

INSERT INTO SESSION.SHIPINFO
CALL TESTDTA.S59RSH05 v_SCBILLTO, v_INID, v_ADRSTYPE

but it doesn't work.i have been trying to do this for a couple of days
but i can't and it kinda urgent :-p
does anybody knows hot to do this?

Jun 7 '06 #1
8 8591
ga****@gmail.com wrote:
i want to insert into a temporal table the result of a store procedure.
on sql server the sentence would look like this (already working)

INSERT INTO #SHIPINFO
exec TESTDTA.S59RSH05 @SCBILLTO, @INID, @ADRSTYPE

i tried to do the same on DB2, meaning

INSERT INTO SESSION.SHIPINFO
CALL TESTDTA.S59RSH05 v_SCBILLTO, v_INID, v_ADRSTYPE

Well, that's illegal SQL. It was two days ago and it still is ;-)
How does your procedure look like? Before trying to do it the hard way
let's see if it could be written as a table function. E.g.:

CREATE FUNCTION FOO(arg INT) RETURNS TABLE(c1 INT, c2 INT)
RETURN SELECT 1, 2 FROM SYSIBM.SYSDUMMY1;

CREATE TABLE T AS (SELECT * FROM TABLE(FOO(1, 1)) AS F) DEFINITION ONLY;
INSERT INTO T SELECT * FROM TABLE(FOO(1, 1)) AS F;

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

IOD Conference
http://www.ibm.com/software/data/ond...ness/conf2006/
Jun 7 '06 #2

Serge Rielau wrote:
ga****@gmail.com wrote:
i want to insert into a temporal table the result of a store procedure.
on sql server the sentence would look like this (already working)

INSERT INTO #SHIPINFO
exec TESTDTA.S59RSH05 @SCBILLTO, @INID, @ADRSTYPE

i tried to do the same on DB2, meaning

INSERT INTO SESSION.SHIPINFO
CALL TESTDTA.S59RSH05 v_SCBILLTO, v_INID, v_ADRSTYPE

Well, that's illegal SQL. It was two days ago and it still is ;-)
How does your procedure look like? Before trying to do it the hard way
let's see if it could be written as a table function. E.g.:

CREATE FUNCTION FOO(arg INT) RETURNS TABLE(c1 INT, c2 INT)
RETURN SELECT 1, 2 FROM SYSIBM.SYSDUMMY1;

CREATE TABLE T AS (SELECT * FROM TABLE(FOO(1, 1)) AS F) DEFINITION ONLY;
INSERT INTO T SELECT * FROM TABLE(FOO(1, 1)) AS F;

Cheers
Serge


Serge, why the two step insert? Why not just
CREATE TABLE T AS (SELECT * FROM TABLE(FOO(1, 1)) AS F) WITH DATA;

Jun 8 '06 #3
JohnO wrote:
Serge, why the two step insert? Why not just
CREATE TABLE T AS (SELECT * FROM TABLE(FOO(1, 1)) AS F) WITH DATA;

I for the life of it can't find WITH DATA in the syntax diagram...
Seriously - what's so bad about separating declaration of the table from
filling it? If DB2 were to support WITH DATA it would save you nothing
but parsing the SELECT twice. A teeny, tiny bit of CPU cost, since the
CREATE TABLE will NOT execute the SELECT. It merely derives the data types.

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

IOD Conference
http://www.ibm.com/software/data/ond...ness/conf2006/
Jun 8 '06 #4

Serge Rielau wrote:
JohnO wrote:
Serge, why the two step insert? Why not just
CREATE TABLE T AS (SELECT * FROM TABLE(FOO(1, 1)) AS F) WITH DATA;

I for the life of it can't find WITH DATA in the syntax diagram...
Seriously - what's so bad about separating declaration of the table from
filling it? If DB2 were to support WITH DATA it would save you nothing
but parsing the SELECT twice. A teeny, tiny bit of CPU cost, since the
CREATE TABLE will NOT execute the SELECT. It merely derives the data types.

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

IOD Conference
http://www.ibm.com/software/data/ond...ness/conf2006/


Hi Serge, can't tell if you are just kidding me but... "WITH DATA" is
in the Syntax diagram for CREATE TABLE in my SQL manual "DB2 UDB for
iSeries SQL Reference V5R3" on page 568.

I *really* like succinct code, and *hate* seeing 2 lines of code when
one will do. For a start, it is less code to maintain; if the table
name is changed, you only have to change it in one place. If the
parameters change, they only change in one place etc. Most of all, it
is just more elegant - to my eye anyway.

Cheers,
JohnO

Jun 8 '06 #5
JohnO wrote:
Serge Rielau wrote:
JohnO wrote:
Serge, why the two step insert? Why not just
CREATE TABLE T AS (SELECT * FROM TABLE(FOO(1, 1)) AS F) WITH DATA;

I for the life of it can't find WITH DATA in the syntax diagram...
Seriously - what's so bad about separating declaration of the table from
filling it? If DB2 were to support WITH DATA it would save you nothing
but parsing the SELECT twice. A teeny, tiny bit of CPU cost, since the
CREATE TABLE will NOT execute the SELECT. It merely derives the data types.

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

IOD Conference
http://www.ibm.com/software/data/ond...ness/conf2006/


Hi Serge, can't tell if you are just kidding me but... "WITH DATA" is
in the Syntax diagram for CREATE TABLE in my SQL manual "DB2 UDB for
iSeries SQL Reference V5R3" on page 568.

I *really* like succinct code, and *hate* seeing 2 lines of code when
one will do. For a start, it is less code to maintain; if the table
name is changed, you only have to change it in one place. If the
parameters change, they only change in one place etc. Most of all, it
is just more elegant - to my eye anyway.

I agree on elegance. Chances that the OP is using DB2 for iSeries are
rather slim though. I didn't know that iSeries has stepped up to WITH
DATA. Good to know :-)

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

IOD Conference
http://www.ibm.com/software/data/ond...ness/conf2006/
Jun 8 '06 #6

Serge Rielau wrote:
JohnO wrote:
Serge Rielau wrote:
JohnO wrote:
Serge, why the two step insert? Why not just
CREATE TABLE T AS (SELECT * FROM TABLE(FOO(1, 1)) AS F) WITH DATA;
I for the life of it can't find WITH DATA in the syntax diagram...
Seriously - what's so bad about separating declaration of the table from
filling it? If DB2 were to support WITH DATA it would save you nothing
but parsing the SELECT twice. A teeny, tiny bit of CPU cost, since the
CREATE TABLE will NOT execute the SELECT. It merely derives the data types.

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

IOD Conference
http://www.ibm.com/software/data/ond...ness/conf2006/


Hi Serge, can't tell if you are just kidding me but... "WITH DATA" is
in the Syntax diagram for CREATE TABLE in my SQL manual "DB2 UDB for
iSeries SQL Reference V5R3" on page 568.

I *really* like succinct code, and *hate* seeing 2 lines of code when
one will do. For a start, it is less code to maintain; if the table
name is changed, you only have to change it in one place. If the
parameters change, they only change in one place etc. Most of all, it
is just more elegant - to my eye anyway.

I agree on elegance. Chances that the OP is using DB2 for iSeries are
rather slim though. I didn't know that iSeries has stepped up to WITH
DATA. Good to know :-)

Cheers
Serge


Indeed. I just happened to notice it appeared after we upgraded to V5R3!

Jun 8 '06 #7
the thing is. i have this store procedure (X) wich returns some data.

i have a new store procedure (Y) in wich i need to fill a tepmoral
table with the result of the first procedure (X)

i found something like this

DECLARE result1 RESULT_SET_LOCATOR VARYING;
CALL targetProcedure();
ASSOCIATE RESULT SET LOCATORS(result1, result2, result3)
WITH PROCEDURE targetProcedure;

ALLOCATE rsCur CURSOR FOR RESULT SET result1;

FETCH rsCur INTO ...

but when i try to run the code it says

"oken VARYING was not valid. Valid tokens: NO CURSOR SCROLL. Cause . .
.. . . : A syntax error was detected at token VARYING. Token VARYING
is not a valid token. A partial list of valid tokens is NO CURSOR
SCROLL. This list assumes that the statement is correct up to the
token. The error may be earlier in the statement, but the syntax of
the statement appears to be valid up to this point. Recovery . . . :
Do one or more of the following and try the request again: -- Verify
the SQL statement in the area of the token VARYING. Correct the
statement. The error could be a missing comma or quotation mark, it
could be a misspelled word, or it could be related to the order of
clauses. -- If the error token is <END-OF-STATEMENT>, correct the SQL
statement because it does not end with a valid clause."
now it looks something like this
CREATE PROCEDURE SCDV810.S42ROD01

(

)

DYNAMIC RESULT SETS 4

LANGUAGE SQL

MODIFIES SQL DATA

BEGIN

DECLARE procresult RESULT_SET_LOCATOR VARYING;
-- TEMPORAL TABLE FOR THE SHIPTO'S

DECLARE GLOBAL TEMPORARY TABLE SESSION.SHIPINFO

(

ShipToNumber DECIMAL,

CCType DECIMAL,

ALPHNAME CHAR(100),

MLNAM CHAR(100),

ADDL1 CHAR(100),

ADDL2 CHAR(100),

ADDL3 CHAR(100),

ADDL4 CHAR(100),

CITY CHAR(100),

STATE CHAR(100),

ZIPCD CHAR(100),

CNTRY CHAR(100),

SHICTCTID DECIMAL,

FIRSTNAME CHAR(100),

MDLNAME CHAR(100),

LSTNAME CHAR(100),

NKNAME CHAR(100),

ADRSFLAG CHAR(100),

CNYNAME CHAR(100),

PHNNUMBER CHAR(100)

) WITH REPLACE ON COMMIT PRESERVE ROWS NOT LOGGED;


CALL TESTDTA.S59RSH05();
ASSOCIATE LOCATORS (loc) WITH PROCEDURE TESTDTA.S59RSH05;
ALLOCATE temp_cursor CURSOR FOR RESULT SET loc;

ETC ETC ETC
END;

but it gave me the error that y showed you before..

THANKS

Jun 8 '06 #8
Which platform and version of DB2 are you on?

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

IOD Conference
http://www.ibm.com/software/data/ond...ness/conf2006/
Jun 8 '06 #9

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: per | last post by:
im not very good at sql but need to query the database to use in my programming script. if the database is just like this id name parent_id 1 A ...
3
by: aaapaul | last post by:
Hallo ! I have a Table with a column "ordernumber" ordernumber A12 A45 A77 A88
7
by: Rick Caborn | last post by:
Does anyone know of a way to execute sql code from a dynamically built text field? Before beginning, let me state that I know this db architecture is built solely for frustration and I hope to...
11
by: randi_clausen | last post by:
Using SQL against a DB2 table the 'with' key word is used to dynamically create a temporary table with an SQL statement that is retained for the duration of that SQL statement. What is the...
2
by: Patrick Olurotimi Ige | last post by:
When i run the code below with stored proc :- I get only the first table results :-"templates" even if i fill the dataset with another table for example category,pages etc.. Any ideas? ...
6
by: Peter Neumaier | last post by:
Hi, I am trying to select some data through a stored procedure and would like to store the result in a local access table. Is that possible? Can somebody provide an example? Thanks&regards!...
16
by: pukivruki | last post by:
hi, I wish to create a temporary table who's name is dynamic based on the argument. ALTER PROCEDURE . @PID1 VARCHAR(50), @PID2 VARCHAR(50), @TICKET VARCHAR(20)
9
by: shanevanle | last post by:
I have two tables that are pretty big. I need about 10 rows in the left table and the right table is filtered to 5 rows as well. It seems when I join the tables in the FROM clause, I have to...
0
by: David | last post by:
On Wed, Jun 18, 2008 at 11:16 AM, M.-A. Lemburg <mal@egenix.comwrote: Thanks for your reply. How do you maintain foreign key references with this approach? eg, you have these 4 tables: ...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.