HELP on creating SQL function

[jon INFANTE <jo...@smartbro.net>]

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

 

Re: HELP on creating SQL function

[Robert Enyedi <ro...@intland.com>]

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