Implementing similar oracle decode function

[vkboss]

Hi,

I would like to implement the decode function in H2 similar to decode
function in oracle.
Analysing the expression "decode( expression , search , result [,
search , result]... [, default] )",
my sugestion of this implementation is a method that has two params,
like
public static final String decode(String expression, String ...
params) or
public static final String decode(String expression, String []
params).
if the size of params is odd, there is a default value, else the
default value is null.
However, when i create the alias and try to call it, it generates an
error.
Here is the snapshot of my code.

public class H2Functions {
public static final String decode1(String expression, String[]
params) {
return "decode1";
}

public static final String decode2(String expression, String...
params) {
return "decode2";
}
}

CREATE ALIAS DECODE1 FOR "H2Functions.decode1(java.lang.String,
java.lang.String[])";
CREATE ALIAS DECODE2 FOR "H2Functions.decode2(java.lang.String,
java.lang.String[])";

Here the results:

call decode1('expression', 'search ');
Error: Feature not supported [50100-72]
SQLState: HYC00
ErrorCode: 50100

call decode1('expression', 'search ', 'result');
Error: Syntax error in SQL statement CALL DECODE1('expression',
'search ',[*] 'result') ; expected ); SQL statement:
call decode1('expression', 'search ', 'result') [42001-72]
SQLState: 42001
ErrorCode: 42001

call decode1('expression', ['search ', 'result']);
Error: Syntax error in SQL statement CALL DECODE1('expression',
[[*]'search ', 'result']) ; expected NOT, EXISTS, SELECT, FROM; SQL
statement:
call decode1('expression', ['search ', 'result']) [42001-72]
SQLState: 42001
ErrorCode: 42001

Same errors occur in decode2.
Anyone can help me ?
Regards

[Thomas Mueller]

Hi,

I think DECODE is one of those functions that is hard to implement
using just Java. It is probably better to replace DECODE in the parser
with the CASE WHEN construct that is already supported - see also
http://www.h2database.com/html/grammar.html#casewhen

I will add a feature request to support DECODE, and another feature
request to support a variable number of parameters for Java functions.
I don't want to use arrays because H2 also supports ARRAY as a base
data type. Varargs as in Java 1.5 would be an option however, but then
you can't support it in Java 1.4.

As a workaround, could you use the CASE WHEN construct?

Regards,
Thomas

[vkboss]

Hi Thomas,

In the system that we are working with, we have to support both H2 and
Oracle executing the same SQL. I just changed the original SQL to use
"CASE WHEN" and both worked perfectly.

Thanks,

On Jul 1, 1:49 am, "Thomas Mueller" <thomas.tom.muel...@gmail.com>
wrote:
> Hi,
>
> I thinkDECODEis one of those functions that is hard to implement
> using just Java. It is probably better to replaceDECODEin the parser
> with the CASE WHEN construct that is already supported - see alsohttp://www.h2database.com/html/grammar.html#casewhen
>
> I will add a feature request to supportDECODE, and another feature

> request to support a variable number of parameters for Java functions.
> I don't want to use arrays because H2 also supports ARRAY as a base
> data type. Varargs as in Java 1.5 would be an option however, but then
> you can't support it in Java 1.4.
>
> As a workaround, could you use the CASE WHEN construct?
>
> Regards,
> Thomas
>
>
>
> On Thu, Jun 26, 2008 at 12:43 PM, vkboss <vkb...@gmail.com> wrote:
>
> > Hi,
>
> > I would like to implement thedecodefunction in H2 similar todecode
> > function in oracle.
> > Analysing the expression "decode( expression , search , result [,
> > search , result]... [, default] )",
> > my sugestion of this implementation is a method that has two params,
> > like

> > public static final Stringdecode(String expression, String ...
> > params) or
> > public static final Stringdecode(String expression, String []

> > Regards- Hide quoted text -
>
> - Show quoted text -