Hi.
attached is a patch for support Oracle's NVL2 function.
litailang
--
You received this message because you are subscribed to the Google Groups "H2
Database" group.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/h2-database?hl=en.
### Eclipse Workspace Patch 1.0
#P h2
Index: src/main/org/h2/expression/Function.java
===================================================================
--- src/main/org/h2/expression/Function.java (revision 3589)
+++ src/main/org/h2/expression/Function.java (working copy)
@@ -95,7 +95,7 @@
CASE = 206, NEXTVAL = 207, CURRVAL = 208, ARRAY_GET = 209, CSVREAD
= 210, CSVWRITE = 211,
MEMORY_FREE = 212, MEMORY_USED = 213, LOCK_MODE = 214, SCHEMA =
215, SESSION_ID = 216, ARRAY_LENGTH = 217,
LINK_SCHEMA = 218, GREATEST = 219, LEAST = 220, CANCEL_SESSION =
221, SET = 222, TABLE = 223, TABLE_DISTINCT = 224,
- FILE_READ = 225, TRANSACTION_ID = 226, TRUNCATE_VALUE = 227;
+ FILE_READ = 225, TRANSACTION_ID = 226, TRUNCATE_VALUE = 227, NVL2
= 228;
private static final int VAR_ARGS = -1;
private static final long PRECISION_UNKNOWN = -1;
@@ -300,6 +300,7 @@
addFunctionWithNull("TRUNCATE_VALUE", TRUNCATE_VALUE, 3, Value.NULL);
addFunctionWithNull("COALESCE", COALESCE, VAR_ARGS, Value.NULL);
addFunctionWithNull("NVL", COALESCE, VAR_ARGS, Value.NULL);
+ addFunctionWithNull("NVL2", NVL2, 3, Value.NULL);
addFunctionWithNull("NULLIF", NULLIF, 2, Value.NULL);
addFunctionWithNull("CASE", CASE, VAR_ARGS, Value.NULL);
addFunctionNotDeterministic("NEXTVAL", NEXTVAL, VAR_ARGS, Value.LONG);
@@ -752,6 +753,18 @@
result = v.convertTo(dataType);
break;
}
+ case NVL2: {
+ Expression expr;
+ int type = argList[1].getType();
+ if (v0 == ValueNull.INSTANCE) {
+ expr = argList[2];
+ } else {
+ expr = argList[1];
+ }
+ Value v = expr.getValue(session);
+ result = v.convertTo(type);
+ break;
+ }
case COALESCE: {
result = v0;
for (int i = 0; i < argList.length; i++) {
@@ -1533,6 +1546,9 @@
}
public int getType() {
+ if(this.info.type == NVL2){
+ return args[1].getType();
+ }
return dataType;
}
Index: src/test/org/h2/test/db/TestFunctions.java
===================================================================
--- src/test/org/h2/test/db/TestFunctions.java (revision 3589)
+++ src/test/org/h2/test/db/TestFunctions.java (working copy)
@@ -25,6 +25,7 @@
import java.util.UUID;
import org.h2.api.AggregateFunction;
+import org.h2.jdbc.JdbcSQLException;
import org.h2.test.TestBase;
import org.h2.tools.SimpleResultSet;
import org.h2.util.IOUtils;
@@ -66,10 +67,60 @@
testFunctions();
testFileRead();
testValue();
+ testNvl2();
deleteDb("functions");
IOUtils.deleteRecursive(TEMP_DIR, true);
}
+ private void testNvl2() throws SQLException {
+ Connection conn = getConnection("functions");
+ Statement stat = conn.createStatement();
+
+ String createSQL = "CREATE TABLE testNvl2 (id BIGINT,txt1 varchar,txt2
varchar,num number(9,0));";
+ stat.execute(createSQL);
+ stat.execute("insert into testNvl2(id,txt1,txt2,num) values
(1,'test1','test2',null)");
+ stat.execute("insert into testNvl2(id,txt1,txt2,num) values
(2,null,'test4',null)");
+ stat.execute("insert into testNvl2(id,txt1,txt2,num) values
(3,'test5',null,null)");
+ stat.execute("insert into testNvl2(id,txt1,txt2,num) values
(4,null,null,null)");
+ stat.execute("insert into testNvl2(id,txt1,txt2,num) values
(5,'2',null,1)");
+ stat.execute("insert into testNvl2(id,txt1,txt2,num) values
(6,'2',null,null)");
+ stat.execute("insert into testNvl2(id,txt1,txt2,num) values
(7,'test2',null,null)");
+
+ String query = "SELECT NVL2(txt1, txt1, txt2),txt1 FROM testNvl2 order
by id asc";
+ ResultSet rs = stat.executeQuery(query);
+ rs.next();
+ String actual = rs.getString(1);
+ assertEquals("test1", actual);
+ rs.next();
+ actual = rs.getString(1);
+ assertEquals("test4", actual);
+ rs.next();
+ actual = rs.getString(1);
+ assertEquals("test5", actual);
+ rs.next();
+ actual = rs.getString(1);
+ assertEquals(null, actual);
+ assertEquals(rs.getMetaData().getColumnType(2),
rs.getMetaData().getColumnType(1));
+ rs.close();
+
+ rs = stat.executeQuery("SELECT NVL2(num, num, txt1),num FROM testNvl2
where id in (5,6) order by id asc");
+ rs.next();
+ assertEquals(rs.getMetaData().getColumnType(2),
rs.getMetaData().getColumnType(1));
+
+ try {
+ rs = stat.executeQuery("SELECT NVL2(num, num, txt1),num FROM
testNvl2 where id = 7 order by id asc");
+ } catch (JdbcSQLException e) {
+ Throwable t = e.getCause();
+ if (t instanceof NumberFormatException) {
+ //
+ } else {
+ throw e;
+ }
+ }
+
+ conn.close();
+ }
+
private void testValue() throws SQLException {
Connection conn = getConnection("functions");
Statement stat = conn.createStatement();
