From derby-user-return-12375-apmail-db-derby-user-archive=db.apache.org@db.apache.org Tue Mar 09 15:48:39 2010 Return-Path: Delivered-To: apmail-db-derby-user-archive@www.apache.org Received: (qmail 90737 invoked from network); 9 Mar 2010 15:48:38 -0000 Received: from unknown (HELO mail.apache.org) (140.211.11.3) by 140.211.11.9 with SMTP; 9 Mar 2010 15:48:38 -0000 Received: (qmail 79932 invoked by uid 500); 9 Mar 2010 15:48:11 -0000 Delivered-To: apmail-db-derby-user-archive@db.apache.org Received: (qmail 79764 invoked by uid 500); 9 Mar 2010 15:48:10 -0000 Mailing-List: contact derby-user-help@db.apache.org; run by ezmlm Precedence: bulk list-help: list-unsubscribe: List-Post: List-Id: Reply-To: "Derby Discussion" Delivered-To: mailing list derby-user@db.apache.org Received: (qmail 79757 invoked by uid 99); 9 Mar 2010 15:48:10 -0000 Received: from nike.apache.org (HELO nike.apache.org) (192.87.106.230) by apache.org (qpsmtpd/0.29) with ESMTP; Tue, 09 Mar 2010 15:48:10 +0000 X-ASF-Spam-Status: No, hits=-2.3 required=10.0 tests=RCVD_IN_DNSWL_MED,SPF_PASS X-Spam-Check-By: apache.org Received-SPF: pass (nike.apache.org: local policy) Received: from [192.18.6.24] (HELO gmp-eb-inf-2.sun.com) (192.18.6.24) by apache.org (qpsmtpd/0.29) with ESMTP; Tue, 09 Mar 2010 15:48:02 +0000 Received: from fe-emea-10.sun.com (gmp-eb-lb-1-fe1.eu.sun.com [192.18.6.7] (may be forged)) by gmp-eb-inf-2.sun.com (8.13.7+Sun/8.12.9) with ESMTP id o29FledK001585 for ; Tue, 9 Mar 2010 15:47:40 GMT MIME-version: 1.0 Content-transfer-encoding: 7BIT Content-type: text/plain; CHARSET=US-ASCII Received: from conversion-daemon.fe-emea-10.sun.com by fe-emea-10.sun.com (Sun Java(tm) System Messaging Server 7u2-7.04 64bit (built Jul 2 2009)) id <0KZ000100TH50K00@fe-emea-10.sun.com> for derby-user@db.apache.org; Tue, 09 Mar 2010 15:47:34 +0000 (GMT) Received: from localhost ([unknown] [129.159.112.134]) by fe-emea-10.sun.com (Sun Java(tm) System Messaging Server 7u2-7.04 64bit (built Jul 2 2009)) with ESMTPSA id <0KZ0008AGUIN9L10@fe-emea-10.sun.com> for derby-user@db.apache.org; Tue, 09 Mar 2010 15:47:13 +0000 (GMT) Date: Tue, 09 Mar 2010 16:47:11 +0100 From: Knut Anders Hatlen Subject: Re: NVL function implementation In-reply-to: <27837153.post@talk.nabble.com> Sender: Knut.Hatlen@Sun.COM To: Derby Discussion Message-id: Organization: Sun Microsystems References: <27837153.post@talk.nabble.com> User-Agent: Gnus/5.13 (Gnus v5.13) Emacs/23.1 (usg-unix-v) X-Virus-Checked: Checked by ClamAV on apache.org Hardie82 writes: > Hi, > > I want to implement the NVL-function for my derby-db to use sql-statement > once written for an informix-db. Therefor I began to write a function class > with methods like the following code: > > public static String nvl(String arg1, String arg2) > { > if(arg1 == null) > return arg2; > return arg1; > } > > I also create a function in sql: > > CREATE FUNCTION NVL(arg1 VARCHAR(100), arg2 VARCCHAR(100)) returns > VARCHAR(100) language java > external name 'mypackage.testdb.DerbyCustomFunctions.nvl' parameter style > java no sql; > > That works fine for statements with varchar-argument and length 100. But > what about other data-types like INTEGER or DATE? Have I to implement > methods for all data-type combination or is there a better way? Something > like a data-type converter for derby? I thought to get informations for > implementation from the nullif-function but can't find it in the derby-code. You could use the COALESCE function defined in the SQL standard. It does more or less the same thing as NVL. See here: http://db.apache.org/derby/docs/dev/ref/rreffunccoalesce.html -- Knut Anders