Re: user defined function help

[Charles Wilt <charles.wilt@xxxxxxxxx>]

No it won't....Db2 doesn't work that way (MS SQL Server is another story ;)
)

Actually the example in ACS is exactly what the OP wants to do

--  category:  Routine (Function or Procedure) Statements
--  description:  Create or Replace Function Language SQL
CREATE OR REPLACE FUNCTION function2(parameter1 INTEGER)
    RETURNS INTEGER
    LANGUAGE SQL
    BEGIN
        DECLARE variable1 DECIMAL(5, 2);
        SELECT c1
            INTO variable1
            FROM table1
            WHERE column1 = parameter1;
        RETURN variable1;
    END;

Just modify it with the actual names and parms needed.

Charles

On Fri, Mar 8, 2019 at 8:46 AM Tyler, Matt <matt.tyler@xxxxxxxxxxxxxx>
wrote:

> Open up or download and open up ACS product.  It has a plugin Run SQL
> Scripts (RSS).  This plugin has an examples set with basic create functions
> listed.  Choose the one intended to be for SQL only code.
>
> You  will have two input parms that you will replace with your ?'s.
> The last line should be
> Return SELECT max( date(CHTS) ) FROM colhst WHERE CHCLMNO = P1 and CHCOLL#
> = P2
>
> -Matt
>
> -----Original Message-----
> From: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxxxxxxxx] On Behalf
> Of tim
> Sent: Friday, March 8, 2019 6:41 AM
> To: midrange-l@xxxxxxxxxxxxxxxxxx
> Subject: user defined function help
>
> I have a query i would like to make a built in function. the syntax is:
>
> SELECT max( date(CHTS) ) FROM colhst WHERE CHCLMNO = ? and CHCOLL# = ?
>
> --
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
> list To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx To subscribe,
> unsubscribe, or change list options,
> visit:
> https://urldefense.proofpoint.com/v2/url?u=https-3A__lists.midrange.com_mailman_listinfo_midrange-2Dl&d=DwICAg&c=2S-2xx8Cum_thMfWs-kOOHQTwolPvSZ4PFLhr1wDDGs&r=wgq2KO1Tl8HswJht2RKpmz7qvL2YDU_M-VhnRH6r43I&m=KqLM_OrvjIb5KlZ_R_N3qx_K32KweyK6zHbyHGpicx4&s=r4-BksuNdleNm--k-kSktsqIu4NgvprPmUf6Zdt4vPY&e=
> or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
> Before posting, please take a moment to review the archives at
> https://urldefense.proofpoint.com/v2/url?u=https-3A__archive.midrange.com_midrange-2Dl&d=DwICAg&c=2S-2xx8Cum_thMfWs-kOOHQTwolPvSZ4PFLhr1wDDGs&r=wgq2KO1Tl8HswJht2RKpmz7qvL2YDU_M-VhnRH6r43I&m=KqLM_OrvjIb5KlZ_R_N3qx_K32KweyK6zHbyHGpicx4&s=JNV-os2UEcp0fakhqh2ZgKtAwmoWyektnk3_GgHty7g&e=
> .
>
> Please contact support@xxxxxxxxxxxx for any subscription related
> questions.
>
> Help support midrange.com by shopping at amazon.com with our affiliate
> link:
> https://urldefense.proofpoint.com/v2/url?u=https-3A__amazon.midrange.com&d=DwICAg&c=2S-2xx8Cum_thMfWs-kOOHQTwolPvSZ4PFLhr1wDDGs&r=wgq2KO1Tl8HswJht2RKpmz7qvL2YDU_M-VhnRH6r43I&m=KqLM_OrvjIb5KlZ_R_N3qx_K32KweyK6zHbyHGpicx4&s=6VYa8KmuGf9Jd5dryU30oe6LbNCyrXNjrq8Ur7iq26Y&e=
> --
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
> To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: https://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at https://archive.midrange.com/midrange-l.
>
> Please contact support@xxxxxxxxxxxx for any subscription related
> questions.
>
> Help support midrange.com by shopping at amazon.com with our affiliate
> link: https://amazon.midrange.com