Mailing-List: contact derby-user-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: "Derby Discussion" <derby-user@db.apache.org>
Message-ID: <45250EC5.5010808@smartbro.net>
Date: Thu, 05 Oct 2006 15:55:17 +0200
From: jon INFANTE <joninfante@smartbro.net>
Reply-To: joninfante@smartbro.net
User-Agent: Thunderbird 1.5.0.7 (Windows/20060909)
MIME-Version: 1.0
To: derby-user@db.apache.org
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Subject: HELP on creating SQL function

Hi good day,

Im new derby and new in creating a SQL function in derby, I need a 
function that will return a string value:
here is the SQL of the function, Im trying to work on, I do encounter an 
error when I try to compile this
(Syntax error  :  MISSING ELEMENT)  what Im missing here ???

CREATE FUNCTION LOG_STATUS(ATTRIBUTE_ID INTEGER)
RETURNS VARCHAR(25)
LANGUAGE JAVA   
PARAMETER STYLE JAVA
READS SQL DATA
RETURNS NULL ON NULL INPUT
RETURN
     SELECT
CASE 
WHEN log.logvalue > attribute.maxlevel THEN 'Failed' 
WHEN (log.logvalue IS NULL) OR (attribute.maxlevel IS NULL)  THEN 'N/A'
ELSE 'passed' END   
FROM log log,attribute attribute WHERE log.refattribute = attribute.id 
AND log.refattribute = LOG_STATUS.ATTRIBUTEID
EXTERNAL NAME 'myfunction.logStatus'

Hoping for your prompt reply.. Thanks in advance..

Jon