Hi All,
I am migrating a database from Firebird/Interbase to DB2 and have having issues with the stored procedures/functions.
I have a number of functions that loop over a FOR loop, each pass it performs some logic on the row and inserts the output data into a temp table. The only point of the insert is so that I can select the data in a RETURN statement at the end of the UDF.
In Firebird we could put these results directly into variables and then "SUSPEND" to return the table of results (which I then use in a selects, joins etc.). There was no need to use a temp table to store the data.
I am trying to not have to create an extra 100 static temp tables, and then have issues with locking and old data cleanups. Since the RETURN has to be the last thing in the UDF - I cant delete the temp data from the table in the UDF which means I have to write nightly cleanup scripts - feels like a bit of a dirty hack.
Here is the procedure in Firebird... -
CREATE OR ALTER PROCEDURE get_active_ay_classes ( academicYearId INTEGER )
-
RETURNS (CLASS_ID INTEGER,
-
CLASS_NAME VARCHAR(200)
-
)
-
AS
-
DECLARE variable classId integer;
-
DECLARE variable linkedClassId integer;
-
DECLARE variable className varchar(200);
-
DECLARE variable linkedClassName varchar(200);
-
DECLARE variable linkedClassName2 varchar(200);
-
begin
-
-
/*
-
Select only the classes that have students in them
-
*/
-
FOR
-
SELECT distinct(c.class_id),
-
COALESCE (c.class ||' ('|| COALESCE( co.preferred_name, co.firstname) ||' '|| co.surname||')', c.class) as class
-
FROM class c
-
INNER JOIN class_enrollment ce ON ce.class_id = c.class_id
-
LEFT JOIN class_teacher ct ON c.class_id = ct.class_id
-
LEFT JOIN teacher t ON ct.teacher_id = t.teacher_id
-
LEFT JOIN contact co ON t.contact_id = co.contact_id
-
WHERE c.academic_year_id = :academicYearId AND ct.is_primary = 1
-
INTO
-
:classId, :className
-
DO
-
BEGIN
-
/* now find the linked classes to this class as well */
-
FOR
-
SELECT distinct (c2.class_id),
-
COALESCE (c2.class ||' ('|| COALESCE( co2.preferred_name, co2.firstname) ||' '|| co2.surname||')', c2.class) as class
-
FROM class c2
-
INNER JOIN class_link cl2 ON c2.class_id = cl2.class_id2
-
LEFT JOIN class_teacher ct2 ON cl2.class_id2 = ct2.class_id
-
LEFT JOIN teacher t2 ON ct2.teacher_id = t2.teacher_id
-
LEFT JOIN contact co2 ON t2.contact_id = co2.contact_id
-
WHERE cl2.class_id1 = :classId
-
INTO
-
:linkedClassId, :linkedClassName
-
DO
-
BEGIN
-
class_id = :linkedClassId;
-
class_name = :linkedClassName;
-
suspend;
-
END
-
class_name = :className;
-
class_id = :classId;
-
suspend;
-
END
-
end !!
-
And here is the same procedure written as a DB2 table function... -
CREATE FUNCTION GET_ACTIVE_AY_CLASSES( ACADEMICYEARID INTEGER )
-
RETURNS TABLE (
-
CLASS_ID INTEGER,
-
CLASS_NAME VARCHAR(200)
-
)
-
NO EXTERNAL ACTION
-
MODIFIES SQL DATA
-
LANGUAGE SQL
-
------------------------------------------------------------------------
-
-- SQL UDF (Table)
-
------------------------------------------------------------------------
-
F1: BEGIN ATOMIC
-
--Variable declaration
-
declare classId INTEGER;
-
declare linkedClassId INTEGER;
-
declare className VARCHAR(200);
-
declare linkedClassName VARCHAR(200);
-
declare linkedClassName2 VARCHAR(200);
-
declare CLASS_ID INTEGER;
-
declare CLASS_NAME VARCHAR(200);
-
declare v_TempTableInstance INTEGER;
-
-
--Now we determine a unique number for the instance
-
set v_TempTableInstance = (select cast((rand()*1000000) as integer) from sysibm.sysdummy1);
-
DELETE from TMP_GETACTIVEAYCLASSES where INST_NUM = v_TempTableInstance;
-
-
-- now find the linked classes to this class as well
-
FOR v_row1 AS
-
SELECT distinct(C.CLASS_ID) FORLOOP1_CLASSID
-
,COALESCE(C.CLASS || ' (' || COALESCE(CO.PREFERRED_NAME,CO.FIRSTNAME) || ' ' || CO.SURNAME || ')',C.CLASS) FORLOOP1_CLASS
-
FROM CLASS C
-
INNER JOIN CLASS_ENROLLMENT CE ON CE.CLASS_ID = C.CLASS_ID
-
LEFT JOIN CLASS_TEACHER CT ON C.CLASS_ID= CT.CLASS_ID
-
LEFT JOIN TEACHER T ON CT.TEACHER_ID = T.TEACHER_ID
-
LEFT JOIN CONTACT CO ON T.CONTACT_ID = CO.CONTACT_ID
-
WHERE C.ACADEMIC_YEAR_ID = ACADEMICYEARID AND CT.IS_PRIMARY = 1
-
DO
-
set classId = v_row1.FORLOOP1_CLASSID;
-
set className = v_row1.FORLOOP1_CLASS;
-
FOR v_row2 AS
-
SELECT distinct(C2.CLASS_ID) FORLOOP2_CLASSID
-
,COALESCE(C2.CLASS || ' (' || COALESCE(CO2.PREFERRED_NAME,CO2.FIRSTNAME) || ' ' || CO2.SURNAME || ')',C2.CLASS) FORLOOP2_CLASS
-
FROM CLASS C2
-
INNER JOIN CLASS_LINK CL2 ON C2.CLASS_ID = CL2.CLASS_ID2
-
LEFT JOIN CLASS_TEACHER CT2 ON CL2.CLASS_ID2= CT2.CLASS_ID
-
LEFT JOIN TEACHER T2 ON CT2.TEACHER_ID = T2.TEACHER_ID
-
LEFT JOIN CONTACT CO2 ON T2.CONTACT_ID = CO2.CONTACT_ID
-
WHERE CL2.CLASS_ID1 = classId
-
DO
-
set linkedClassId = v_row2.FORLOOP2_CLASSID;
-
set linkedClassName = v_row2.FORLOOP2_CLASS;
-
SET CLASS_ID = linkedClassId;
-
SET CLASS_NAME = linkedClassName;
-
INSERT INTO TMP_GETACTIVEAYCLASSES VALUES(v_TempTableInstance,CLASS_ID,CLASS_NAME);
-
END FOR;
-
SET CLASS_NAME = className;
-
SET CLASS_ID = classId;
-
INSERT INTO TMP_GETACTIVEAYCLASSES VALUES(v_TempTableInstance,CLASS_ID,CLASS_NAME);
-
END FOR;
-
-
RETURN SELECT CLASS_ID, CLASS_NAME FROM TMP_GETACTIVEAYCLASSES where INST_NUM = v_TempTableInstance;
-
END
-
I am looking for some help to re-write the above procedure so that the temp table is not necessary (as in Firebird).
Thanks everyone for your help, this one has me stumped!
D.
0 3105 Sign in to post your reply or Sign up for a free account.
Similar topics |
by: Todd Shillam |
last post by:
Here's my script--need help with output to text file (must be missing
something here like closing the file or something). Any help would be
greatly appreciated here. Lastly, I'm using Visual Basic .NET 2003.
Source...
Dim vFileName As String
vFileName = Format(Now(), "MM - dd - yyyy") & " - " & vName
Dim oFile As System.IO.File
Dim oWrite As System.IO.StreamWriter
oWrite = oFile.CreateText("C:\" & vFileName & ".txt")
|
by: jstaggs39 |
last post by:
I have a form that requires a start date and an end date as input for
the parameters then runs the form which open queries which are designed
to populate certain tables. As it stands now, i can only run the form
one month at a time, so i would enter the first of the month, say
01/01/2002 and the end of the month 01/31/2002 and it would run the
form with january data. If i enter a range of dates from say 01/01/2002
to 06/30/2002, it will...
|
by: Tim::.. |
last post by:
Can someone please help... I'm kind of new to asp.net and VS.net and need to
know how to get the value of a variable in the following funtion into a sub
on another aspx page!
Can someone please help!
Thanks
...CODE..
Public Function GetOfficeName() As String
|
by: Ron St-Pierre |
last post by:
Whenever I run certain functions, such as the example below, the output
is either displayed in the terminal or emailed to be by cron, depending
on how I run it. Is there any way I can re-write the function, set some
psql parameters, etc, to ensure that the results aren't displayed? I've
checked some of the postgresql.conf logging properties and running psql
with quiet = true, but all to no avail. Anyone have any suggestions,
hints, ?
...
|
by: Joe Van Meer |
last post by:
Hi all,
I have an app that currently runs through 3 seperate stored procedures each
returning a count of records. What I would like to do is combine these
calls into one call, however I am having an issue getting the output
parameters' values after execution. Here is a snipit of code that calls one
for simplicity's sake: I think I am close, i was under the impression I
could use the datareader to get at it, but is is always returning ...
| |
by: Chris H |
last post by:
bassically i am buiding a simple shopping cart type feature and am using
sessions to store the cart contents and then when teh user checks out and
submits the order it will email me the cart contents.. however right now im
having a problem with it sending the proper data.. Insead of inserting each
item in the cart in teh msg o fthe email it only inserts one item... Below
are the two main functions i am using to accomplish this...I have also...
|
by: jza |
last post by:
Hello all,
I am fairly new to c, coming from a Java background. I am working on a mathematical program and I have a function that needs to return a 2-d array. After some web searching, I have determined that in c, it is better to just pass the array in the function. Since arrays are passed by reference, I expected for the updated array to be passed back with the correct values filled in, however, only the first row of the array is returned...
|
by: SM |
last post by:
Hello,
I have an array that holds images path of cd covers. The array looks
like this:
$cd = array(
589=>'sylver.jpg',
782=>'bigone.jpg',
158=>'dime.jpg'
);
|
by: Thekid |
last post by:
I need help writing a loop for this.
image = ImageGrab.grab((0,0,800,800))
box = (100,100,400,400)
im = image.crop(box)
im1 = im.save("screen.jpg")
# at this point I need to rotate the image 90 degrees and crop it again, but do this four times-
# so I'd like a loop here instead of this:
im2 = im1.rotate(90)
im3 = im2.crop(box)
|
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 synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
| |
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
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 most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
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 instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
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 into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
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 the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
| |
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
| |