ij> -- -- Licensed to the Apache Software Foundation (ASF) under one or more -- contributor license agreements. See the NOTICE file distributed with -- this work for additional information regarding copyright ownership. -- The ASF licenses this file to You under the Apache License, Version 2.0 -- (the "License"); you may not use this file except in compliance with -- the License. You may obtain a copy of the License at -- -- http://www.apache.org/licenses/LICENSE-2.0 -- -- Unless required by applicable law or agreed to in writing, software -- distributed under the License is distributed on an "AS IS" BASIS, -- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. -- See the License for the specific language governing permissions and -- limitations under the License. -- -- Test various functions create table alltypes( id int not null primary key, smallIntCol smallint, intCol int, bigIntCol bigint, floatCol float, float1Col float(1), float26Col float(26), realCol real, doubleCol double, decimalCol decimal, decimal10Col decimal(10), decimal11Col decimal(11), numeric10d2Col numeric(10,2), charCol char, char32Col char(32), charForBitCol char(16) for bit data, varcharCol varchar(64), varcharForBitCol varchar(64) for bit data, longVarcharCol long varchar, blobCol blob(10k), clobCol clob(10k), dateCol date, timeCol time, timestampCol timestamp); 0 rows inserted/updated/deleted ij> insert into allTypes(id) values(1),(2); 2 rows inserted/updated/deleted ij> update allTypes set smallIntCol = 2 where id = 1; 1 row inserted/updated/deleted ij> update allTypes set intCol = 2 where id = 1; 1 row inserted/updated/deleted ij> update allTypes set bigIntCol = 3 where id = 1; 1 row inserted/updated/deleted ij> update allTypes set floatCol = 4.1 where id = 1; 1 row inserted/updated/deleted ij> update allTypes set float1Col = 5 where id = 1; 1 row inserted/updated/deleted ij> update allTypes set float26Col = 6.1234567890123456 where id = 1; 1 row inserted/updated/deleted ij> update allTypes set realCol = 7.2 where id = 1; 1 row inserted/updated/deleted ij> update allTypes set doubleCol = 8.2 where id = 1; 1 row inserted/updated/deleted ij> update allTypes set decimalCol = 9 where id = 1; 1 row inserted/updated/deleted ij> update allTypes set decimal10Col = 1234 where id = 1; 1 row inserted/updated/deleted ij> update allTypes set decimal11Col = 1234 where id = 1; 1 row inserted/updated/deleted ij> update allTypes set numeric10d2Col = 11.12 where id = 1; 1 row inserted/updated/deleted ij> update allTypes set charCol = 'a' where id = 1; 1 row inserted/updated/deleted ij> update allTypes set char32Col = 'abc' where id = 1; 1 row inserted/updated/deleted ij> update allTypes set charForBitCol = X'ABCD' where id = 1; 1 row inserted/updated/deleted ij> update allTypes set varcharCol = 'abcde' where id = 1; 1 row inserted/updated/deleted ij> update allTypes set varcharForBitCol = X'ABCDEF' where id = 1; 1 row inserted/updated/deleted ij> update allTypes set longVarcharCol = 'abcdefg' where id = 1; 1 row inserted/updated/deleted ij> update allTypes set blobCol = cast( X'0031' as blob(10k)) where id = 1; 1 row inserted/updated/deleted ij> update allTypes set clobCol = 'clob data' where id = 1; 1 row inserted/updated/deleted ij> update allTypes set dateCol = date( '2004-3-13') where id = 1; 1 row inserted/updated/deleted ij> update allTypes set timeCol = time( '16:07:21') where id = 1; 1 row inserted/updated/deleted ij> update allTypes set timestampCol = timestamp( '2004-3-14 17:08:22.123456') where id = 1; 1 row inserted/updated/deleted ij> select id, length(smallIntCol) from allTypes order by id; ID |2 ----------------------- 1 |2 2 |NULL ij> select id, length(intCol) from allTypes order by id; ID |2 ----------------------- 1 |4 2 |NULL ij> select id, length(bigIntCol) from allTypes order by id; ID |2 ----------------------- 1 |8 2 |NULL ij> select id, length(floatCol) from allTypes order by id; ID |2 ----------------------- 1 |8 2 |NULL ij> select id, length(float1Col) from allTypes order by id; ID |2 ----------------------- 1 |4 2 |NULL ij> select id, length(float26Col) from allTypes order by id; ID |2 ----------------------- 1 |8 2 |NULL ij> select id, length(realCol) from allTypes order by id; ID |2 ----------------------- 1 |4 2 |NULL ij> select id, length(doubleCol) from allTypes order by id; ID |2 ----------------------- 1 |8 2 |NULL ij> select id, length(decimalCol) from allTypes order by id; ID |2 ----------------------- 1 |3 2 |NULL ij> select id, length(decimal10Col) from allTypes order by id; ID |2 ----------------------- 1 |6 2 |NULL ij> select id, length(decimal11Col) from allTypes order by id; ID |2 ----------------------- 1 |6 2 |NULL ij> select id, length(numeric10d2Col) from allTypes order by id; ID |2 ----------------------- 1 |6 2 |NULL ij> select id, length(charCol) from allTypes order by id; ID |2 ----------------------- 1 |1 2 |NULL ij> select id, length(char32Col) from allTypes order by id; ID |2 ----------------------- 1 |32 2 |NULL ij> select id, length(charForBitCol) from allTypes order by id; ID |2 ----------------------- 1 |16 2 |NULL ij> select id, length(varcharCol) from allTypes order by id; ID |2 ----------------------- 1 |5 2 |NULL ij> select id, length(varcharForBitCol) from allTypes order by id; ID |2 ----------------------- 1 |3 2 |NULL ij> select id, length(longVarcharCol) from allTypes order by id; ID |2 ----------------------- 1 |7 2 |NULL ij> select id, length(blobCol) from allTypes order by id; ID |2 ----------------------- 1 |2 2 |NULL ij> select id, length(clobCol) from allTypes order by id; ID |2 ----------------------- 1 |9 2 |NULL ij> select id, length(dateCol) from allTypes order by id; ID |2 ----------------------- 1 |4 2 |NULL ij> select id, length(timeCol) from allTypes order by id; ID |2 ----------------------- 1 |3 2 |NULL ij> select id, length(timestampCol) from allTypes order by id; ID |2 ----------------------- 1 |10 2 |NULL ij> -- try length of constants values( length( 1), length( 720176), length( 12345678901)); 1 |2 |3 ----------------------------------- 4 |4 |8 ij> values( length( 2.2E-1)); 1 ----------- 8 ij> values( length( 1.), length( 12.3), length( 123.4), length( 123.45)); 1 |2 |3 |4 ----------------------------------------------- 1 |2 |3 |3 ij> values( length( '1'), length( '12')); 1 |2 ----------------------- 1 |2 ij> values( length( X'00'), length( X'FF'), length( X'FFFF')); 1 |2 |3 ----------------------------------- 1 |1 |2 ij> values( length( date('0001-1-1')), length( time('0:00:00')), length( timestamp( '0001-1-1 0:00:00'))); 1 |2 |3 ----------------------------------- 4 |3 |10 ij> -- try a length in the where clause select id from allTypes where length(smallIntCol) > 5 order by id; ID ----------- ij> select id from allTypes where length(intCol) > 5 order by id; ID ----------- ij> select id from allTypes where length(bigIntCol) > 5 order by id; ID ----------- 1 ij> select id from allTypes where length(floatCol) > 5 order by id; ID ----------- 1 ij> select id from allTypes where length(float1Col) > 5 order by id; ID ----------- ij> select id from allTypes where length(float26Col) > 5 order by id; ID ----------- 1 ij> select id from allTypes where length(realCol) > 5 order by id; ID ----------- ij> select id from allTypes where length(doubleCol) > 5 order by id; ID ----------- 1 ij> select id from allTypes where length(decimalCol) > 5 order by id; ID ----------- ij> select id from allTypes where length(decimal10Col) > 5 order by id; ID ----------- 1 ij> select id from allTypes where length(decimal11Col) > 5 order by id; ID ----------- 1 ij> select id from allTypes where length(numeric10d2Col) > 5 order by id; ID ----------- 1 ij> select id from allTypes where length(charCol) > 5 order by id; ID ----------- ij> select id from allTypes where length(char32Col) > 5 order by id; ID ----------- 1 ij> select id from allTypes where length(charForBitCol) > 5 order by id; ID ----------- 1 ij> select id from allTypes where length(varcharCol) > 5 order by id; ID ----------- ij> select id from allTypes where length(varcharForBitCol) > 5 order by id; ID ----------- ij> select id from allTypes where length(longVarcharCol) > 5 order by id; ID ----------- 1 ij> select id from allTypes where length(blobCol) > 5 order by id; ID ----------- ij> select id from allTypes where length(clobCol) > 5 order by id; ID ----------- 1 ij> select id from allTypes where length(dateCol) > 5 order by id; ID ----------- ij> select id from allTypes where length(timeCol) > 5 order by id; ID ----------- ij> select id from allTypes where length(timestampCol) > 5 order by id; ID ----------- 1 ij> -- try an expression select id, length( charCol || 'abc') from allTypes order by id; ID |2 ----------------------- 1 |4 2 |NULL ij> -- bug 5761 & 5627 -- JDBC escape length function has the following behavior -- LENGTH (RTRIM (xxxx)) values {FN LENGTH('xxxx ')}; 1 ----------- 4 ij> values {FN LENGTH(' xxxx ')}; 1 ----------- 5 ij> values {FN LENGTH(' xxxx ')}; 1 ----------- 6 ij> values {FN LENGTH(' xxxx ')}; 1 ----------- 7 ij> CREATE FUNCTION SV_RNNI(P1 INT) RETURNS VARCHAR(10) RETURNS NULL ON NULL INPUT EXTERNAL NAME 'java.lang.String.valueOf' LANGUAGE JAVA PARAMETER STYLE JAVA; 0 rows inserted/updated/deleted ij> CREATE FUNCTION SV_CNI(P1 INT) RETURNS VARCHAR(10) CALLED ON NULL INPUT EXTERNAL NAME 'java.lang.String.valueOf' LANGUAGE JAVA PARAMETER STYLE JAVA; 0 rows inserted/updated/deleted ij> CREATE FUNCTION SV_DEF(P1 INT) RETURNS VARCHAR(10) EXTERNAL NAME 'java.lang.String.valueOf' LANGUAGE JAVA PARAMETER STYLE JAVA; 0 rows inserted/updated/deleted ij> CREATE FUNCTION MAX_RNNI(P1 INT, P2 INT) RETURNS INT RETURNS NULL ON NULL INPUT EXTERNAL NAME 'java.lang.Math.max' LANGUAGE JAVA PARAMETER STYLE JAVA; 0 rows inserted/updated/deleted ij> CREATE FUNCTION MAX_CNI(P1 INT, P2 INT) RETURNS INT CALLED ON NULL INPUT EXTERNAL NAME 'java.lang.Math.max' LANGUAGE JAVA PARAMETER STYLE JAVA; 0 rows inserted/updated/deleted ij> CREATE FUNCTION MAX_DEF(P1 INT, P2 INT) RETURNS INT EXTERNAL NAME 'java.lang.Math.max' LANGUAGE JAVA PARAMETER STYLE JAVA; 0 rows inserted/updated/deleted ij> VALUES SV_RNNI(3); 1 -------------------------------------------------------------------------------------------------------------------------------- 3 ij> VALUES SV_CNI(4); 1 -------------------------------------------------------------------------------------------------------------------------------- 4 ij> VALUES SV_DEF(5); 1 -------------------------------------------------------------------------------------------------------------------------------- 5 ij> create table SV_TAB(I INT); 0 rows inserted/updated/deleted ij> insert into SV_TAB values(null); 1 row inserted/updated/deleted ij> insert into SV_TAB values(7); 1 row inserted/updated/deleted ij> select SV_RNNI(I) from SV_TAB where I = 7; 1 -------------------------------------------------------------------------------------------------------------------------------- 7 ij> select SV_CNI(I) from SV_TAB where I = 7; 1 -------------------------------------------------------------------------------------------------------------------------------- 7 ij> select SV_DEF(I) from SV_TAB where I = 7; 1 -------------------------------------------------------------------------------------------------------------------------------- 7 ij> select SV_RNNI(I) from SV_TAB where I IS NULL; 1 -------------------------------------------------------------------------------------------------------------------------------- NULL ij> select SV_CNI(I) from SV_TAB where I IS NULL; 1 -------------------------------------------------------------------------------------------------------------------------------- ERROR 39004: A NULL value cannot be passed to a method which takes a parameter of primitive type 'int'. ij> select SV_DEF(I) from SV_TAB where I IS NULL; 1 -------------------------------------------------------------------------------------------------------------------------------- ERROR 39004: A NULL value cannot be passed to a method which takes a parameter of primitive type 'int'. ij> VALUES MAX_RNNI(67, 12); 1 ----------- 67 ij> VALUES MAX_RNNI(-3, -98); 1 ----------- -3 ij> VALUES MAX_CNI(5, 3); 1 ----------- 5 ij> VALUES MAX_DEF(99, -45); 1 ----------- 99 ij> select MAX_RNNI(5, I) from SV_TAB where I = 7; 1 ----------- 7 ij> select MAX_CNI(6, I) from SV_TAB where I = 7; 1 ----------- 7 ij> select MAX_DEF(2, I) from SV_TAB where I = 7; 1 ----------- 7 ij> select MAX_RNNI(I, 34) from SV_TAB where I = 7; 1 ----------- 34 ij> select MAX_CNI(I, 24) from SV_TAB where I = 7; 1 ----------- 24 ij> select MAX_DEF(I, 14) from SV_TAB where I = 7; 1 ----------- 14 ij> select MAX_RNNI(5, I) from SV_TAB where I IS NULL; 1 ----------- NULL ij> select MAX_CNI(6, I) from SV_TAB where I IS NULL; 1 ----------- ERROR 39004: A NULL value cannot be passed to a method which takes a parameter of primitive type 'int'. ij> select MAX_DEF(2, I) from SV_TAB where I IS NULL; 1 ----------- ERROR 39004: A NULL value cannot be passed to a method which takes a parameter of primitive type 'int'. ij> select MAX_RNNI(I, 34) from SV_TAB where I IS NULL; 1 ----------- NULL ij> select MAX_CNI(I, 24) from SV_TAB where I IS NULL; 1 ----------- ERROR 39004: A NULL value cannot be passed to a method which takes a parameter of primitive type 'int'. ij> select MAX_DEF(I, 14) from SV_TAB where I IS NULL; 1 ----------- ERROR 39004: A NULL value cannot be passed to a method which takes a parameter of primitive type 'int'. ij> CREATE FUNCTION COUNT_ROWS(P1 VARCHAR(128), P2 VARCHAR(128)) RETURNS INT READS SQL DATA EXTERNAL NAME 'org.apache.derbyTesting.functionTests.util.ProcedureTest.countRows' LANGUAGE JAVA PARAMETER STYLE JAVA; 0 rows inserted/updated/deleted ij> CREATE FUNCTION FN_ABS(P1 INT) RETURNS INT NO SQL RETURNS NULL ON NULL INPUT EXTERNAL NAME 'java.lang.Math.abs' LANGUAGE JAVA PARAMETER STYLE JAVA; 0 rows inserted/updated/deleted ij> select FN_ABS(i) FROM SV_TAB; 1 ----------- NULL 7 ij> select COUNT_ROWS(CURRENT SCHEMA, 'SV_TAB') from SV_TAB; 1 ----------- 2 2 ij> select FN_ABS(i), COUNT_ROWS(CURRENT SCHEMA, 'SV_TAB') from SV_TAB; 1 |2 ----------------------- NULL |2 7 |2 ij> DROP FUNCTION SV_RNNI; 0 rows inserted/updated/deleted ij> DROP FUNCTION SV_CNI; 0 rows inserted/updated/deleted ij> DROP FUNCTION SV_DEF; 0 rows inserted/updated/deleted ij> DROP FUNCTION MAX_RNNI; 0 rows inserted/updated/deleted ij> DROP FUNCTION MAX_CNI; 0 rows inserted/updated/deleted ij> DROP FUNCTION MAX_DEF; 0 rows inserted/updated/deleted ij> DROP FUNCTION FN_ABS; 0 rows inserted/updated/deleted ij> DROP FUNCTION COUNT_ROWS; 0 rows inserted/updated/deleted ij> DROP TABLE SV_TAB; 0 rows inserted/updated/deleted ij> -- check MODIFIES SQL DATA not allowed with FUNCTION CREATE FUNCTION COUNT_ROWS(P1 VARCHAR(128), P2 VARCHAR(128)) RETURNS INT MODIFIES SQL DATA EXTERNAL NAME 'org.apache.derbyTesting.functionTests.util.ProcedureTest.countRows' LANGUAGE JAVA PARAMETER STYLE JAVA; ERROR 42X01: Syntax error: MODIFIES SQL DATA. ij> CREATE FUNCTION SIGNATURE_BUG_DERBY_258_D(P_VAL INT, P_RADIX INT) RETURNS VARCHAR(20) LANGUAGE JAVA PARAMETER STYLE JAVA NO SQL EXTERNAL NAME 'java.lang.Integer.toString(int, int)'; 0 rows inserted/updated/deleted ij> CREATE FUNCTION SIGNATURE_BUG_DERBY_258_NS(P_VAL INT, P_RADIX INT) RETURNS VARCHAR(20) LANGUAGE JAVA PARAMETER STYLE JAVA NO SQL EXTERNAL NAME 'java.lang.Integer.toString'; 0 rows inserted/updated/deleted ij> CREATE FUNCTION SIGNATURE_BUG_DERBY_258_E() RETURNS VARCHAR(20) LANGUAGE JAVA PARAMETER STYLE JAVA NO SQL EXTERNAL NAME 'java.lang.Integer.toXXString()'; 0 rows inserted/updated/deleted ij> -- these are ok VALUES SIGNATURE_BUG_DERBY_258_NS(2356, 16); 1 -------------------------------------------------------------------------------------------------------------------------------- 934 ij> VALUES SIGNATURE_BUG_DERBY_258_NS(2356, 10); 1 -------------------------------------------------------------------------------------------------------------------------------- 2356 ij> VALUES SIGNATURE_BUG_DERBY_258_NS(2356, 2); 1 -------------------------------------------------------------------------------------------------------------------------------- 100100110100 ij> -- Must resolve as above VALUES SIGNATURE_BUG_DERBY_258_D(2356, 16); 1 -------------------------------------------------------------------------------------------------------------------------------- 934 ij> -- no method to resolve to (with specified signature) VALUES SIGNATURE_BUG_DERBY_258_E(); ERROR 42X50: No method was found that matched the method call java.lang.Integer.toXXString(), tried all combinations of object and primitive types and any possible type conversion for any parameters the method call may have. The method might exist but it is not public and/or static, or the parameter types are not method invocation convertible. ij> DROP FUNCTION SIGNATURE_BUG_DERBY_258_D; 0 rows inserted/updated/deleted ij> DROP FUNCTION SIGNATURE_BUG_DERBY_258_E; 0 rows inserted/updated/deleted ij> DROP FUNCTION SIGNATURE_BUG_DERBY_258_NS; 0 rows inserted/updated/deleted ij> -- Test for DERBY-479 the commented out VALUES clauses -- below cause linkage errors loading the generated class.; CREATE FUNCTION RN_COS(A DOUBLE) RETURNS DOUBLE EXTERNAL NAME 'java.lang.Math.cos(double)' RETURNS NULL ON NULL INPUT LANGUAGE JAVA PARAMETER STYLE JAVA; 0 rows inserted/updated/deleted ij> CREATE FUNCTION RN_RADIANS(A DOUBLE) RETURNS DOUBLE EXTERNAL NAME 'java.lang.Math.toRadians(double)' RETURNS NULL ON NULL INPUT LANGUAGE JAVA PARAMETER STYLE JAVA; 0 rows inserted/updated/deleted ij> CREATE FUNCTION CALL_COS(A DOUBLE) RETURNS DOUBLE EXTERNAL NAME 'java.lang.Math.cos(double)' CALLED ON NULL INPUT LANGUAGE JAVA PARAMETER STYLE JAVA; 0 rows inserted/updated/deleted ij> CREATE FUNCTION CALL_RADIANS(A DOUBLE) RETURNS DOUBLE EXTERNAL NAME 'java.lang.Math.toRadians(double)' CALLED ON NULL INPUT LANGUAGE JAVA PARAMETER STYLE JAVA; 0 rows inserted/updated/deleted ij> -- Test cases for DERBY-479 VALUES CAST( RN_COS(RN_RADIANS(null)) AS DECIMAL(3,2)); 1 ----- NULL ij> VALUES CAST( RN_COS(RN_RADIANS(90.0)) AS DECIMAL(3,2)); 1 ----- 0.00 ij> VALUES CAST( CALL_COS(CALL_RADIANS(90.0)) AS DECIMAL(3,2)); 1 ----- 0.00 ij> VALUES CAST( CALL_COS(CALL_RADIANS(null)) AS DECIMAL(3,2)); 1 ----- ERROR 39004: A NULL value cannot be passed to a method which takes a parameter of primitive type 'double'. ij> VALUES CAST( CALL_COS(RN_RADIANS(null)) AS DECIMAL(3,2)); 1 ----- ERROR 39004: A NULL value cannot be passed to a method which takes a parameter of primitive type 'double'. ij> VALUES CAST( CALL_COS(RN_RADIANS(90.0)) AS DECIMAL(3,2)); 1 ----- 0.00 ij> VALUES CAST( RN_COS(CALL_RADIANS(90.0)) AS DECIMAL(3,2)); 1 ----- 0.00 ij> VALUES CAST( RN_COS(CALL_RADIANS(null)) AS DECIMAL(3,2)); 1 ----- ERROR 39004: A NULL value cannot be passed to a method which takes a parameter of primitive type 'double'. ij> DROP FUNCTION RN_COS; 0 rows inserted/updated/deleted ij> DROP FUNCTION RN_RADIANS; 0 rows inserted/updated/deleted ij> DROP FUNCTION CALL_COS; 0 rows inserted/updated/deleted ij> DROP FUNCTION CALL_RADIANS; 0 rows inserted/updated/deleted ij> -- SYSFUN functions (unqualifed functions are automatically resolved -- to the in-memory SYSFUN functions if the function does not exist -- in the current schema. -- SYSFUN math functions create table SYSFUN_MATH_TEST (d double); 0 rows inserted/updated/deleted ij> insert into SYSFUN_MATH_TEST values null; 1 row inserted/updated/deleted ij> insert into SYSFUN_MATH_TEST values 0.67; 1 row inserted/updated/deleted ij> insert into SYSFUN_MATH_TEST values 1.34; 1 row inserted/updated/deleted ij> -- cast result to DECIMAL to reduce possible diffs -- with different vms and double values.; VALUES PI(); 1 ---------------------- 3.141592653589793 ij> select cast (ACOS(d) as DECIMAL(6,3)) AS ACOS FROM SYSFUN_MATH_TEST; ACOS -------- NULL 0.836 ERROR 22003: The resulting value is outside the range for the data type DOUBLE. ij> select cast (ASIN(d) as DECIMAL(6,3)) AS ASIN FROM SYSFUN_MATH_TEST; ASIN -------- NULL 0.734 ERROR 22003: The resulting value is outside the range for the data type DOUBLE. ij> select cast (ATAN(d) as DECIMAL(6,3)) AS ATAN FROM SYSFUN_MATH_TEST; ATAN -------- NULL 0.590 0.929 ij> select cast (COS(d) as DECIMAL(6,3)) AS COS FROM SYSFUN_MATH_TEST; COS -------- NULL 0.783 0.228 ij> select cast (SIN(d) as DECIMAL(6,3)) AS SIN FROM SYSFUN_MATH_TEST; SIN -------- NULL 0.620 0.973 ij> select cast (TAN(d) as DECIMAL(6,3)) AS TAN FROM SYSFUN_MATH_TEST; TAN -------- NULL 0.792 4.255 ij> select cast (DEGREES(d) as DECIMAL(6,3)) AS DEGREES FROM SYSFUN_MATH_TEST; DEGREES -------- NULL 38.388 76.776 ij> select cast (RADIANS(d) as DECIMAL(6,3)) AS RADIANS FROM SYSFUN_MATH_TEST; RADIANS -------- NULL 0.011 0.023 ij> select cast (LN(d) as DECIMAL(6,3)) AS LN, cast (LOG(d) as DECIMAL(6,3)) AS LOG FROM SYSFUN_MATH_TEST; LN |LOG ----------------- NULL |NULL -0.400 |-0.400 0.292 |0.292 ij> select cast (EXP(d) as DECIMAL(6,3)) AS EXP FROM SYSFUN_MATH_TEST; EXP -------- NULL 1.954 3.819 ij> select cast (LOG10(d) as DECIMAL(6,3)) AS LOG10 FROM SYSFUN_MATH_TEST; LOG10 -------- NULL -0.173 0.127 ij> select cast (CEIL(d) as DECIMAL(6,3)) AS CEIL FROM SYSFUN_MATH_TEST; CEIL -------- NULL 1.000 2.000 ij> select cast (CEILING(d) as DECIMAL(6,3)) AS CEILING FROM SYSFUN_MATH_TEST; CEILING -------- NULL 1.000 2.000 ij> select cast (FLOOR(d) as DECIMAL(6,3)) AS FLOOR FROM SYSFUN_MATH_TEST; FLOOR -------- NULL 0.000 1.000 ij> select cast (SYSFUN.ACOS(d) as DECIMAL(6,3)) AS SYSFUN_ACOS FROM SYSFUN_MATH_TEST; SYSFUN_& -------- NULL 0.836 ERROR 22003: The resulting value is outside the range for the data type DOUBLE. ij> CREATE VIEW VSMT AS SELECT SIN(d) sd, PI() pi FROM SYSFUN_MATH_TEST; 0 rows inserted/updated/deleted ij> select cast (sd as DECIMAL(6,3)), cast (pi as DECIMAL(6,3)) from VSMT; 1 |2 ----------------- NULL |3.141 0.620 |3.141 0.973 |3.141 ij> drop view VSMT; 0 rows inserted/updated/deleted ij> drop table SYSFUN_MATH_TEST; 0 rows inserted/updated/deleted ij> drop function SYSFUN.ACOS; ERROR 42X62: 'DROP FUNCTION' is not allowed in the 'SYSFUN' schema. ij>