× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



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 thread ...

Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

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.