473,320 Members | 1,881 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.

Inline UDF returning a computed string

Hi,

I am new in DB2 8.1 below code works/logic works fine in Oracle, pls
find below the inline UDF , the intent of this UDF is to create a
string for each child and then return the computed string , following
query would be fired and it should give result in the stated format

Query
~~~~~~~~~~~~~

SELECT EQUIP_ID , SCH.RANGE(EQUIP_ID) Description
FROM EQUIPMENT


Description
~~~~~~~~~~~~
1 to 100 Decibles, 1 to 50 N/m
on compiling this function it gives me following error

[DB2 SQL error: SQLCODE: -104, SQLSTATE: 42601, SQLERRMC: WITH;DECLARE
CUR CURSOR;<SQL_variable_condition_declaration>
Message: An unexpected token "WITH" was found following "DECLARE CUR
CURSOR". Expected tokens may include:
"<SQL_variable_condition_declaration>"
CREATE FUNCTION SCH.RANGE( P_EQUIPID INTEGER ) RETURNS
VARCHAR(1000)
LANGUAGE SQL

BEGIN ATOMIC

DECLARE return_str VARCHAR(1000) DEFAULT ' ';
DECLARE tmp VARCHAR(1000) DEFAULT ' ';

DECLARE CUR CURSOR FOR
SELECT CHAR(EM.LOWERLIMIT) || ' to ' ||
CHAR(EM.UPPERLIMIT) || CD.DESCRIPTION
FROM SCH.EQUIPMETRIC EM, SCH.CODES CD
WHERE EM.UOMID = CD.ID
AND EM.EQUIPID = P_EQUIPID
FOR READ ONLY;

DECLARE EXIT HANDLER FOR NOT FOUND

SET return_str = ' ';

BEGIN
OPEN CUR;
FETCH FROM CUR INTO tmp;

WHILE(SQLSTATE = '00000') DO

SET return_str = return_str + ', ' + tmp;
FETCH FROM CUR INTO tmp;

END WHILE;

CLOSE CUR;
END;

RETURN return_str;
END


Pls help in resolving this issue , I have googled but couldnt get any
solution

Thanks
Deven
Mar 13 '08 #1
1 2094
de************@gmail.com wrote:
Hi,

I am new in DB2 8.1 below code works/logic works fine in Oracle, pls
find below the inline UDF , the intent of this UDF is to create a
string for each child and then return the computed string , following
query would be fired and it should give result in the stated format

Query
~~~~~~~~~~~~~

SELECT EQUIP_ID , SCH.RANGE(EQUIP_ID) Description
FROM EQUIPMENT


Description
~~~~~~~~~~~~
1 to 100 Decibles, 1 to 50 N/m
on compiling this function it gives me following error

[DB2 SQL error: SQLCODE: -104, SQLSTATE: 42601, SQLERRMC: WITH;DECLARE
CUR CURSOR;<SQL_variable_condition_declaration>
Message: An unexpected token "WITH" was found following "DECLARE CUR
CURSOR". Expected tokens may include:
"<SQL_variable_condition_declaration>"
CREATE FUNCTION SCH.RANGE( P_EQUIPID INTEGER ) RETURNS
VARCHAR(1000)
LANGUAGE SQL

BEGIN ATOMIC

DECLARE return_str VARCHAR(1000) DEFAULT ' ';
DECLARE tmp VARCHAR(1000) DEFAULT ' ';

DECLARE CUR CURSOR FOR
SELECT CHAR(EM.LOWERLIMIT) || ' to ' ||
This text obviously doesn't match the error message, nonetheless...
inline SQL PL does not support either cursors or handlers.
You have three choices:
* Trivially rewrite the function using a FOR-loop.
That will also eliminate the need for an exit handler.
* Toss the whole logic as it is not needed.
Google for "SQL on Fire".
In the slides you will find away to aggregate strings in
SQL usingXMLAGG()
* In principle any SQL PL too complex for inlining can be placed in
a procedure. The function can then CALL the procedure to do the
heavy lifting.
That's just for future use. Would be overkill here.

Cheers
Serge

PS: I'd choose the first options since it is closest to what you came up
with on your own and it works well.
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
Mar 13 '08 #2

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

Similar topics

1
by: S Austin | last post by:
Discovered recently (duh) that putting inline code in .h files (e.g. in class definitions) is not a good idea when building DLLs and the applications that use those DLLs. The reason being, of...
8
by: yb | last post by:
Hi, Does an inline element become block element once it is positioned absolutely. I couldn't find this explicity mentioned in the spec. (css2)
3
by: junky_fellow | last post by:
What is an inline function ? When should we declare a particular function as inline ? If a function is declared to be inline, does that mean that the entire code for that function is subtituted...
1
by: Dave | last post by:
I am relativly new to visual basic, so this may be a no- brainer. I am attempting to use a computed column in a VB dataset defined as followe: ' 'dcCost ' Me.dcCost.ColumnName = "Cost"
7
by: Aamir Mahmood | last post by:
Hi All I have DataTable object. Is there a way that I can know which fields (columns) in the table are computed. Apparantly the DataTable.Columns returns all columns both computed and other....
1
by: DumRat | last post by:
Well, the results arouse curiosity. Check out for yourselves. http://www.thescripts.com/forum/threadnav617160-2-10.html
1
by: DevenBasak | last post by:
Hi, I am new in DB2 8.1 below code works/logic works fine in Oracle, pls find below the inline UDF , the intent of this UDF is to create a string for each child and then return the computed string...
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
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...
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...
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: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
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.