|
Sorry, I mean its returning Null. "Luqman" <pearlsoft@xxxxxxxxxxxx> wrote in message news:cprg3p$3jd$1@xxxxxxxxxxxxxxxx > Hi, > > I have created following function but its not returning null. Please advise > what am I missing. > > Best Regards, > Luqman > > > CALL FUNCTION2(5000,0,10,1999,2000) > > > CREATE FUNCTION function2 > (principal decimal(12,2), > rate decimal(5,2), > startyear int, > endyear int) > RETURNS TABLE( > DEP_YEAR INT, > Begin_WDV DECIMAL(12,2), > Rate DECIMAL(6,3), > Dep_Amount DECIMAL(12,2), > End_WDV DECIMAL(12,2)) > LANGUAGE SQL MODIFIES SQL DATA > NOT DETERMINISTIC > DISALLOW PARALLEL > BEGIN > DECLARE GLOBAL TEMPORARY TABLE MyFile > (DEP_YEAR INT, > Begin_WDV DECIMAL(12,2), > Rate DECIMAL(6,3), > Dep_Amount DECIMAL(12,2), > End_WDV DECIMAL(12,2)); > INSERT INTO MyFile VALUES(1234,5000,0.10,12000,13000); > RETURN SELECT * FROM MyFile; > END; > > > > > "HauserSSS" <Hauser@xxxxxxxxxxxxxxx> wrote in > message news:HFEAIBMAHGFKNPHBOMFIIEJBCDAA.Hauser-YeaMbNrzpuROM1jbQLfANq1cXZ9k6wlg@pu blic.gmane.org > Hi, > > you don't have to create a file outside the UDTF. > But you have to have a table to return a table. > > You either can create a table in any library you want (inside your UDTF) > by using the CREATE TABLE statement or > create a temporary file using the DECLARE GLOBAL TEMPORARY TABLE statement. > Any allowed SQL statement can be executed between BEGIN and END, > so you can create your file, insert rows or what ever you want. > > CREATE FUNCTION function2 > (principal decimal(12,2), > rate decimal(5,2), > startyear int, > endyear int) > RETURNS TABLE( > DEP_YEAR INT NOT NULL, > Begin_WDV DECIMAL(12,2) NOT NULL, > Rate DECIMAL(6,3) NOT NULL, > Dep_Amount DECIMAL(12,2) NOT NULL, > End_WDV DECIMAL(12,2) NOT NULL) > LANGUAGE SQL > BEGIN > DECLARE GLOBAL TEMPORARY TABLE MyFile > (DEP_YEAR INT NOT NULL, > Begin_WDV DECIMAL(12,2) NOT NULL, > Rate DECIMAL(6,3) NOT NULL, > Dep_Amount DECIMAL(12,2) NOT NULL, > End_WDV DECIMAL(12,2) NOT NULL) ; > INSERT INTO QTEMP / MyFile ....; > RETURN ( SELECT * FROM MyFile ) ; > > END; > > For more information look at the redbook: > Stored Procdures, Triggers and User Defined Functions > http://www.redbooks.ibm.com/abstracts/sg246503.html?Open > > Birgitta > > -----Ursprüngliche Nachricht----- > Von: rpg400-l-bounces@xxxxxxxxxxxx > [mailto:rpg400-l-bounces@xxxxxxxxxxxx]Im Auftrag > von Luqman > Gesendet: Dienstag, 14. Dezember 2004 13:46 > An: rpg400-l@xxxxxxxxxxxx > Betreff: Re: DB2 Function to return more than 01 value ? > > > Do you mean I have to create a table by name of 'MyFile' and then create > this function ? > > Please note, I don't want to create any table outside the function, just > want to create a table in RETURNS TABLE MyFile clause, insert records in it, > select the records for output report, and close the cursor. Is it possible > in DB2 ? > > This is what I am already doing in Sql Server. > > > > I hope you understand what exactly I want. Any idea please ? > > Best Regards, > > Luqman > > > "HauserSSS" <Hauser@xxxxxxxxxxxxxxx> wrote in > message > news:HFEAIBMAHGFKNPHBOMFIOEIJCDAA.Hauser-YeaMbNrzpuROM1jbQLfANq1cXZ9k6wlg@pu > blic.gmane.org > Hi, > > why don't you insert first your rows and than execute the select statement? > Like in any other UDF you can execute multiple statements. > Cursors can be defined and loops performed. > All executable statements must be embedded into the compound statements > BEGIN and END. > > But the RETURN-Statement must contain a Select-Statement to return multiple > values. > > Example: > CREATE FUNCTION MySchema/GetAdress > (ParAdrNo Integer) > RETURNS TABLE > (FirstName CHARACTER( 15 ) , > Name CHARACTER( 15 ) , > ZipCode DECIMAL (9, 0) , > City CHARACTER( 15 )) > LANGUAGE SQL > NOT DETERMINISTIC > CONTAINS SQL DATA > CALLED ON NULL INPUT > NO EXTERNAL ACTION > DISALLOW PARALLEL > > BEGIN > FOR vl AS c1 CURSOR FOR > SELECT * FROM MyFile2 > DO ...; > INSERT INTO MySchema/MyFile ...; > Other SQL-Statements > End For; > > RETURN Select * > from MyFile > Where MyAdrNo = ParAdrNo ; > END > > Birgitta > > -----Ursprüngliche Nachricht----- > Von: rpg400-l-bounces@xxxxxxxxxxxx > [mailto:rpg400-l-bounces@xxxxxxxxxxxx]Im Auftrag > von Luqman > Gesendet: Montag, 13. Dezember 2004 09:54 > An: rpg400-l@xxxxxxxxxxxx > Betreff: Re: DB2 Function to return more than 01 value ? > > > Hi, > > I need to insert values in this temporary table using While Loop before > returning the values because I have to make various calculations of > Depreciation on Assets, is there any way that I can insert values in this > Stored Procedure Defined Table. > > Best Regards, > > Luqman > > > > "HauserSSS" <Hauser@xxxxxxxxxxxxxxx> wrote in > message > news:HFEAIBMAHGFKNPHBOMFIKEIGCDAA.Hauser-YeaMbNrzpuROM1jbQLfANq1cXZ9k6wlg@pu > blic.gmane.org > Hello, > > User Defined Table Functions (UDTFs) are available for DB2 UDB for iSeries > from Release V5R2M0 on. > Just have a look at the following redbook: > Stored Procedures, Triggers and User Defined Functions > http://www.redbooks.ibm.com/abstracts/sg246503.html?Open > > If you want to return more than one value, > you don't have to specify a table name and don't need to insert values into > this table. > A simple select would be enough. > > Example: > CREATE FUNCTION MySchema/GetAdress > (ParAdrNo Integer) > RETURNS TABLE > (FirstName CHARACTER( 15 ) , > Name CHARACTER( 15 ) , > ZipCode DECIMAL (9, 0) , > City CHARACTER( 15 )) > LANGUAGE SQL > NOT DETERMINISTIC > CONTAINS SQL DATA > CALLED ON NULL INPUT > NO EXTERNAL ACTION > DISALLOW PARALLEL > > RETURN Select * > from MyFile > Where MyAdrNo = ParAdrNo ; > > > > -----Ursprüngliche Nachricht----- > Von: rpg400-l-bounces@xxxxxxxxxxxx > [mailto:rpg400-l-bounces@xxxxxxxxxxxx]Im Auftrag > von Luqman > Gesendet: Sonntag, 12. Dezember 2004 22:44 > An: rpg400-l@xxxxxxxxxxxx > Betreff: DB2 Function to return more than 01 value ? > > > The following function in Sql Server 2000 returns more than 01 value in the > shape of table. > but the RETURNS TABLE CLAUSE not working in DB2 Function, is it or similar > supported in DB2 ? > > CREATE FUNCTION function2 (principal decimal(12,2),rate > decimal(5,2),startyear int,endyear int) > RETURNS Depreciation TABLE( > DEP_YEAR INT NOT NULL, > Begin_WDV DECIMAL(12,2) NOT NULL, > Rate DECIMAL(6,3) NOT NULL, > Dep_Amount DECIMAL(12,2) NOT NULL, > End_WDV DECIMAL(12,2) NOT NULL) > LANGUAGE SQL > BEGIN > DECLARE variable1 DECIMAL(5,2); > Insert into Depreciation Select * from myTable; > RETURN > END; > > > > > Best Regards, > > Luqman > > > > > > -- > This is the RPG programming on the AS400 / iSeries (RPG400-L) mailing list > To post a message email: RPG400-L@xxxxxxxxxxxx > To subscribe, unsubscribe, or change list options, > visit: http://lists.midrange.com/mailman/listinfo/rpg400-l > or email: RPG400-L-request@xxxxxxxxxxxx > Before posting, please take a moment to review the archives > at http://archive.midrange.com/rpg400-l. > > > > > -- > This is the RPG programming on the AS400 / iSeries (RPG400-L) mailing list > To post a message email: RPG400-L@xxxxxxxxxxxx > To subscribe, unsubscribe, or change list options, > visit: http://lists.midrange.com/mailman/listinfo/rpg400-l > or email: RPG400-L-request@xxxxxxxxxxxx > Before posting, please take a moment to review the archives > at http://archive.midrange.com/rpg400-l. > > > > > -- > This is the RPG programming on the AS400 / iSeries (RPG400-L) mailing list > To post a message email: RPG400-L@xxxxxxxxxxxx > To subscribe, unsubscribe, or change list options, > visit: http://lists.midrange.com/mailman/listinfo/rpg400-l > or email: RPG400-L-request@xxxxxxxxxxxx > Before posting, please take a moment to review the archives > at http://archive.midrange.com/rpg400-l. > > > > > -- > This is the RPG programming on the AS400 / iSeries (RPG400-L) mailing list > To post a message email: RPG400-L@xxxxxxxxxxxx > To subscribe, unsubscribe, or change list options, > visit: http://lists.midrange.com/mailman/listinfo/rpg400-l > or email: RPG400-L-request@xxxxxxxxxxxx > Before posting, please take a moment to review the archives > at http://archive.midrange.com/rpg400-l. > > > > > -- > This is the RPG programming on the AS400 / iSeries (RPG400-L) mailing list > To post a message email: RPG400-L@xxxxxxxxxxxx > To subscribe, unsubscribe, or change list options, > visit: http://lists.midrange.com/mailman/listinfo/rpg400-l > or email: RPG400-L-request@xxxxxxxxxxxx > Before posting, please take a moment to review the archives > at http://archive.midrange.com/rpg400-l. > > > > > -- > This is the RPG programming on the AS400 / iSeries (RPG400-L) mailing list > To post a message email: RPG400-L@xxxxxxxxxxxx > To subscribe, unsubscribe, or change list options, > visit: http://lists.midrange.com/mailman/listinfo/rpg400-l > or email: RPG400-L-request@xxxxxxxxxxxx > Before posting, please take a moment to review the archives > at http://archive.midrange.com/rpg400-l. > > > > > > -- > This is the RPG programming on the AS400 / iSeries (RPG400-L) mailing list > To post a message email: RPG400-L@xxxxxxxxxxxx > To subscribe, unsubscribe, or change list options, > visit: http://lists.midrange.com/mailman/listinfo/rpg400-l > or email: RPG400-L-request@xxxxxxxxxxxx > Before posting, please take a moment to review the archives > at http://archive.midrange.com/rpg400-l. > >
As an Amazon Associate we earn from qualifying purchases.
This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].
Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.
midrange.com
