473,545 Members | 2,688 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

What's incorrect with this function?

I tried create this function with DB2 Express and received the error
message:

-104
Thiago.FUNCTION 1: 9: [IBM][CLI Driver][DB2/NT] SQL0104N An inexpected
token "TABLE SESSION.TESTE(C 1 INTEGER) ON CO" was found following
"ARE GLOBAL TEMPORARY". Expected tokens may include: "<space>". LINE
NUMBER=9. SQLSTATE=42601
CREATE FUNCTION THIAGO.FUNCTION 1( )
RETURNS TABLE (C1 INTEGER)
------------------------------------------------------------------------
-- SQL UDF (Table)
------------------------------------------------------------------------
MODIFIES SQL DATA
BEGIN ATOMIC
DECLARE GLOBAL TEMPORARY TABLE SESSION.TESTE(C 1 INTEGER)
ON COMMIT DELETE ROWS WITH REPLACE NOT LOGGED;

insert into SESSION.TESTE select count(*) from SYSCAT.FUNCTION S
RETURN SELECT C1 from SESSION.TESTE
END

Is some incorrect?

Thanks

Mar 14 '06 #1
11 2844
Get rid of SESSION qualifier in the temp table declaration, it's only
required when referencing a temp table.

-Eugene

Mar 14 '06 #2
th******@gmail. com wrote:
I tried create this function with DB2 Express and received the error
message:

-104
Thiago.FUNCTION 1: 9: [IBM][CLI Driver][DB2/NT] SQL0104N An inexpected
token "TABLE SESSION.TESTE(C 1 INTEGER) ON CO" was found following
"ARE GLOBAL TEMPORARY". Expected tokens may include: "<space>". LINE
NUMBER=9. SQLSTATE=42601
CREATE FUNCTION THIAGO.FUNCTION 1( )
RETURNS TABLE (C1 INTEGER)
------------------------------------------------------------------------
-- SQL UDF (Table)
------------------------------------------------------------------------
MODIFIES SQL DATA
BEGIN ATOMIC
DECLARE GLOBAL TEMPORARY TABLE SESSION.TESTE(C 1 INTEGER)
ON COMMIT DELETE ROWS WITH REPLACE NOT LOGGED;

insert into SESSION.TESTE select count(*) from SYSCAT.FUNCTION S
RETURN SELECT C1 from SESSION.TESTE
END

Is some incorrect?

SQL Functions use "inline SQL PL" which is the subset of SQL PL
supported by "compound statement(dynam ic)". DDL of any shape is NOT
supported. "inline" means that the function is expanded into the query
like a view. Imagine trying to do DDL in a view :-)

In general to extend the capabilities of SQL functions use the CALL
statement in the function and place all the heavy lifting into the
called procedure.

Note though that you can't catch a resultset from the call.

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
Mar 14 '06 #3
I'm migrating a Application from the Firebird Database to DB2 and I
need of some resource for migrating complex Stored Procedures that
transform values from tables in database and create a RecordSet return.

With SQL Functions in Java/C this is possible?

Mar 14 '06 #4
th******@gmail. com wrote:
I'm migrating a Application from the Firebird Database to DB2 and I
need of some resource for migrating complex Stored Procedures that
transform values from tables in database and create a RecordSet return.

With SQL Functions in Java/C this is possible?

DB2 supports SQL procedures as well as C-Procedures.
If you had procedures before that may be the easiest.
Table functions (C/JAVA and to some extend SQL) are also available.

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
Mar 14 '06 #5
th******@gmail. com wrote:
CREATE FUNCTION THIAGO.FUNCTION 1( )
RETURNS TABLE (C1 INTEGER)
------------------------------------------------------------------------
-- SQL UDF (Table)
------------------------------------------------------------------------
MODIFIES SQL DATA
BEGIN ATOMIC
DECLARE GLOBAL TEMPORARY TABLE SESSION.TESTE(C 1 INTEGER)
ON COMMIT DELETE ROWS WITH REPLACE NOT LOGGED;

insert into SESSION.TESTE select count(*) from SYSCAT.FUNCTION S
RETURN SELECT C1 from SESSION.TESTE
END


Serge told you the "why" this is not working. Here is a simple way to
convert the above into something working:

CREATE FUNCTION THIAGO.FUNCTION 1( )
RETURNS TABLE (C1 INTEGER)
------------------------------------------------------------------------
-- SQL UDF (Table)
------------------------------------------------------------------------
RETURN select count(*) from SYSCAT.FUNCTION S
Depending on your actual logic, I'm pretty confident that you will be able
to solve most of these things with non-procedural SQL.

--
Knut Stolze
DB2 Information Integration Development
IBM Germany
Mar 15 '06 #6
Stolze,

The real procedure I need build is more complex. Envolve many
computations and verifications. I sent an example of my necessity.

I see this sample in the ibm public help:

CREATE FUNCTION RANK(N INTEGER)
RETURNS TABLE(
POSITION INTEGER,
EMPNO CHAR(6),
FIRSTNME CHAR(20),
LASTNAME CHAR(20),
SALARY DECIMAL(13,2)
)
LANGUAGE SQL
DISALLOW PARALLEL
MODIFIES SQL DATA
NOT FENCED
BEGIN
DECLARE LAST_SALARY DEC(13,2) DEFAULT 0;
DECLARE I INTEGER DEFAULT 1;
DECLARE STMT VARCHAR(255);
DECLARE TABLE_ALREADY_E XISTS CONDITION FOR '42710'; 1
DECLARE CONTINUE HANDLER FOR TABLE_ALREADY_E XISTS 2
DELETE FROM SESSION.RETURN_ TBL;
DECLARE GLOBAL TEMPORARY TABLE SESSION.RETURN_ TBL ( 3
POSITION INTEGER NOT NULL,
EMPNO CHAR(6) NOT NULL,
FIRSTNME CHAR(20) NOT NULL,
LASTNAME CHAR(20) NOT NULL,
SALARY DECIMAL(13,2) NOT NULL);

FOR_LOOP: FOR EACH_ROW AS C1 CURSOR FOR
SELECT EMPNO, FIRSTNME, LASTNAME, SALARY
FROM SAMPLEDB01.EMPL OYEE ORDER BY SALARY DESC DO

IF (I > N) AND (EACH_ROW.SALAR Y < LAST_SALARY) THEN
LEAVE FOR_LOOP;
ELSE
SET LAST_SALARY = EACH_ROW.SALARY ;
END IF;

INSERT INTO SESSION.RETURN_ TBL
VALUES ( I, EACH_ROW.EMPNO, EACH_ROW.FIRSTN ME,
EACH_ROW.LASTNA ME, EACH_ROW.SALARY );
SET I = I + 1;
END FOR;

RETURN
SELECT POSITION, EMPNO, FIRSTNME, LASTNAME, SALARY
FROM SESSION.RETURN_ TBL;
END;

But I can't create it in DB2 Express edition.
In other versions of DB2 this work proper?
I need some like it.

Thanks

Mar 15 '06 #7
th******@gmail. com wrote:
Stolze,

The real procedure I need build is more complex. Envolve many
computations and verifications. I sent an example of my necessity.

I see this sample in the ibm public help:

CREATE FUNCTION RANK(N INTEGER)
RETURNS TABLE(
POSITION INTEGER,
EMPNO CHAR(6),
FIRSTNME CHAR(20),
LASTNAME CHAR(20),
SALARY DECIMAL(13,2)
)
LANGUAGE SQL
DISALLOW PARALLEL
MODIFIES SQL DATA
NOT FENCED
BEGIN
DECLARE LAST_SALARY DEC(13,2) DEFAULT 0;
DECLARE I INTEGER DEFAULT 1;
DECLARE STMT VARCHAR(255);
DECLARE TABLE_ALREADY_E XISTS CONDITION FOR '42710'; 1
DECLARE CONTINUE HANDLER FOR TABLE_ALREADY_E XISTS 2
DELETE FROM SESSION.RETURN_ TBL;
DECLARE GLOBAL TEMPORARY TABLE SESSION.RETURN_ TBL ( 3
POSITION INTEGER NOT NULL,
EMPNO CHAR(6) NOT NULL,
FIRSTNME CHAR(20) NOT NULL,
LASTNAME CHAR(20) NOT NULL,
SALARY DECIMAL(13,2) NOT NULL);

FOR_LOOP: FOR EACH_ROW AS C1 CURSOR FOR
SELECT EMPNO, FIRSTNME, LASTNAME, SALARY
FROM SAMPLEDB01.EMPL OYEE ORDER BY SALARY DESC DO

IF (I > N) AND (EACH_ROW.SALAR Y < LAST_SALARY) THEN
LEAVE FOR_LOOP;
ELSE
SET LAST_SALARY = EACH_ROW.SALARY ;
END IF;

INSERT INTO SESSION.RETURN_ TBL
VALUES ( I, EACH_ROW.EMPNO, EACH_ROW.FIRSTN ME,
EACH_ROW.LASTNA ME, EACH_ROW.SALARY );
SET I = I + 1;
END FOR;

RETURN
SELECT POSITION, EMPNO, FIRSTNME, LASTNAME, SALARY
FROM SESSION.RETURN_ TBL;
END;

But I can't create it in DB2 Express edition.
In other versions of DB2 this work proper?
I need some like it.

This for sure won't work on any DB2 for LUW.
And AFAIK DB2 for LUW is the only platform that allows MODIFIES SQL DATA
in table functions to begin with.
Would you mind posting a link to the page where you find tis example?

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
Mar 16 '06 #8
I found it in this redbook:
http://www.redbooks.ibm.com/abstracts/sg246503.html
PDF Page: 442

Thanks

Mar 16 '06 #9
th******@gmail. com wrote:
I found it in this redbook:
http://www.redbooks.ibm.com/abstracts/sg246503.html
PDF Page: 442

Thanks

Interesting. Appears DB2 for iSeries is ahead here.

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
Mar 16 '06 #10

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

Similar topics

12
2986
by: prashna | last post by:
Hi all, I am new to C and I am working on some simple programs.I am getting the following warning which I am not able to find what it is, please let me know what is causing this warning.Also What happens if I ignore this warning? inttoascii.c:38: warning: control reaches end of non-void function
1
3918
by: murphy | last post by:
Hi, I've been seeing two symptoms with my asp.net site that have started recently after a long period of smooth running. As others on our team make changes to referenced dll's I find that I get the following errors from time to time. Apparently the following procedure alleviates the problems: -Reboot (clears locks on following...
3
3332
by: murphy | last post by:
Hi, I've been seeing two symptoms with my asp.net site that have started recently after a long period of smooth running. As others on our team make changes to referenced dll's I find that I get the following errors from time to time. Apparently the following procedure alleviates the problems:
4
2733
by: Peter Ritchie | last post by:
Does anyone know how to suppress a specific warning for a line or block of code in C#? C++ has a nice facility to disable a warning for a block of code with #pragma for warnings that are incorrect or don't apply. For example, the following code generates an CS0628 because CS0628 makes an incorrect assumption that "protected" applies only to...
4
1563
by: brian | last post by:
i broke down where i think the problems areas would be. any help would be greatly appreciated. where the file is called <script type="text/javascript" src="k.js"> </script> the beginning of my form, test.cgi doesnt exsist but i think it should run the function before the action. Is this correct???
26
25564
by: tnowles00 | last post by:
Hi friend, what is the use of function pointer in c language and where it is useful? tell with simple example...? plz help me.
23
3628
by: Jess | last post by:
Hello, I understand the default-initialization happens if we don't initialize an object explicitly. I think for an object of a class type, the value is determined by the constructor, and for the built-in types, the value is usually garbage. Is this right? However, I'm a bit confused about value-initialization, when does it happen, and...
10
3709
by: _mario.lat | last post by:
hallo, what does it means "the function is not thread-safe"? thak you in advance, Mario.
92
6129
by: Heinrich Pumpernickel | last post by:
what does this warning mean ? #include <stdio.h> int main() { long l = 100; printf("l is %li\n", l * 10L);
0
7502
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
0
7434
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language...
1
7457
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
0
6026
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
1
5360
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes...
0
5078
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert...
0
3491
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in...
1
1921
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
1
1045
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

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.