db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From jon INFANTE <joninfa...@smartbro.net>
Subject Re: HELP on creating SQL function
Date Thu, 05 Oct 2006 16:23:10 GMT
Robert,

thank you for the prompt reply to my inquiry, Im soo confused when you 
said that only Java functions are supported by derby, does it mean
that I cannot use the derby function on SQL statement??, but when I 
compiled the given example on the link you specified, it does compiled 
and I can use
the function  ( I did run  it by  issuing  a SQL statement like "SELECT 
TO_DEGREES(10.2) FROM...." on the SQL window of  a third party DB tool)


Jon

>Jon,
>
>According to the Derby Reference Manual only Java functions are 
>supported by Derby: 

>http://db.apache.org/derby/docs/dev/ref/rrefcreatefunctionstatement.html
>
>Regards,
>Robert

jon INFANTE wrote:
> 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
>
>
>
>


Mime
View raw message