coalescetests.out

derby database source code.good for you.

	 -----------------------------------------------------
expected exception because char value does not match a time/timestamp format The syntax of the string representation of a datetime value is incorrect.
SELECT COALESCE(TIMESTAMPCOL,CHARCOL,VARCHARCOL,TIMESTAMPCOL) from AllDataTypesTable
	 COL1(datatype : TIMESTAMP, precision : 26, scale : 6)
	 -----------------------------------------------------
expected exception because char value does not match a time/timestamp format The syntax of the string representation of a datetime value is incorrect.
SELECT COALESCE(BLOBCOL,BLOBCOL) from AllDataTypesTable
	 COL1(datatype : BLOB, precision : 1024, scale : 0)
	 --------------------------------------------------
	{null}
	{null}
	{null}
	{null}
Start testing all datatypes combinations in COALESCE/VALUE function
SELECT COALESCE(SMALLINTCOL,SMALLINTCOL) from AllDataTypesTable
 Coalesc/Value with operands SMALLINT , SMALLINT will have result data type of SMALLINT
	 COL1(datatype : SMALLINT, precision : 5, scale : 0)
	 ---------------------------------------------------
	{null}
	{0}
	{1}
	{2}
SELECT VALUE(SMALLINTCOL,SMALLINTCOL) from AllDataTypesTable
 Coalesc/Value with operands SMALLINT , SMALLINT will have result data type of SMALLINT
	 COL1(datatype : SMALLINT, precision : 5, scale : 0)
	 ---------------------------------------------------
	{null}
	{0}
	{1}
	{2}
SELECT COALESCE(SMALLINTCOL,INTEGERCOL) from AllDataTypesTable
 Coalesc/Value with operands SMALLINT , INTEGER will have result data type of INTEGER
	 COL1(datatype : INTEGER, precision : 10, scale : 0)
	 ---------------------------------------------------
	{null}
	{0}
	{1}
	{2}
SELECT VALUE(SMALLINTCOL,INTEGERCOL) from AllDataTypesTable
 Coalesc/Value with operands SMALLINT , INTEGER will have result data type of INTEGER
	 COL1(datatype : INTEGER, precision : 10, scale : 0)
	 ---------------------------------------------------
	{null}
	{0}
	{1}
	{2}
SELECT COALESCE(SMALLINTCOL,BIGINTCOL) from AllDataTypesTable
 Coalesc/Value with operands SMALLINT , BIGINT will have result data type of BIGINT
	 COL1(datatype : BIGINT, precision : 19, scale : 0)
	 --------------------------------------------------
	{null}
	{0}
	{1}
	{2}
SELECT VALUE(SMALLINTCOL,BIGINTCOL) from AllDataTypesTable
 Coalesc/Value with operands SMALLINT , BIGINT will have result data type of BIGINT
	 COL1(datatype : BIGINT, precision : 19, scale : 0)
	 --------------------------------------------------
	{null}
	{0}
	{1}
	{2}
SELECT COALESCE(SMALLINTCOL,DECIMALCOL) from AllDataTypesTable
 Coalesc/Value with operands SMALLINT , DECIMAL(10,5) will have result data type of DECIMAL
	 COL1(datatype : DECIMAL, precision : 10, scale : 5)
	 ---------------------------------------------------
	{null}
	{0.00000}
	{1.00000}
	{2.00000}
SELECT VALUE(SMALLINTCOL,DECIMALCOL) from AllDataTypesTable
 Coalesc/Value with operands SMALLINT , DECIMAL(10,5) will have result data type of DECIMAL
	 COL1(datatype : DECIMAL, precision : 10, scale : 5)
	 ---------------------------------------------------
	{null}
	{0.00000}
	{1.00000}
	{2.00000}
SELECT COALESCE(SMALLINTCOL,REALCOL) from AllDataTypesTable
 Coalesc/Value with operands SMALLINT , REAL will have result data type of DOUBLE
	 COL1(datatype : DOUBLE, precision : 15, scale : 0)
	 --------------------------------------------------
	{null}
	{0.0}
	{1.0}
	{2.0}
SELECT VALUE(SMALLINTCOL,REALCOL) from AllDataTypesTable
 Coalesc/Value with operands SMALLINT , REAL will have result data type of DOUBLE
	 COL1(datatype : DOUBLE, precision : 15, scale : 0)
	 --------------------------------------------------
	{null}
	{0.0}
	{1.0}
	{2.0}
SELECT COALESCE(SMALLINTCOL,DOUBLECOL) from AllDataTypesTable
 Coalesc/Value with operands SMALLINT , DOUBLE will have result data type of DOUBLE
	 COL1(datatype : DOUBLE, precision : 15, scale : 0)
	 --------------------------------------------------
	{null}
	{0.0}
	{1.0}
	{2.0}
SELECT VALUE(SMALLINTCOL,DOUBLECOL) from AllDataTypesTable
 Coalesc/Value with operands SMALLINT , DOUBLE will have result data type of DOUBLE
	 COL1(datatype : DOUBLE, precision : 15, scale : 0)
	 --------------------------------------------------
	{null}
	{0.0}
	{1.0}
	{2.0}
SELECT COALESCE(SMALLINTCOL,CHARCOL) from AllDataTypesTable
 Operands SMALLINT , CHAR(60) are incompatible for Coalesce/Value function
expected exception The data type, length or value of arguments 'SMALLINT' and 'CHAR' is incompatible.
SELECT VALUE(SMALLINTCOL,CHARCOL) from AllDataTypesTable
 Operands SMALLINT , CHAR(60) are incompatible for Coalesce/Value function
expected exception The data type, length or value of arguments 'SMALLINT' and 'CHAR' is incompatible.
SELECT COALESCE(SMALLINTCOL,VARCHARCOL) from AllDataTypesTable
 Operands SMALLINT , VARCHAR(60) are incompatible for Coalesce/Value function
expected exception The data type, length or value of arguments 'SMALLINT' and 'VARCHAR' is incompatible.
SELECT VALUE(SMALLINTCOL,VARCHARCOL) from AllDataTypesTable
 Operands SMALLINT , VARCHAR(60) are incompatible for Coalesce/Value function
expected exception The data type, length or value of arguments 'SMALLINT' and 'VARCHAR' is incompatible.
SELECT COALESCE(SMALLINTCOL,LONGVARCHARCOL) from AllDataTypesTable
 Operands SMALLINT , LONG VARCHAR are incompatible for Coalesce/Value function
expected exception The data type, length or value of arguments 'SMALLINT' and 'LONG VARCHAR' is incompatible.
SELECT VALUE(SMALLINTCOL,LONGVARCHARCOL) from AllDataTypesTable
 Operands SMALLINT , LONG VARCHAR are incompatible for Coalesce/Value function
expected exception The data type, length or value of arguments 'SMALLINT' and 'LONG VARCHAR' is incompatible.
SELECT COALESCE(SMALLINTCOL,CHARFORBITCOL) from AllDataTypesTable
 Operands SMALLINT , CHAR(60) FOR BIT DATA are incompatible for Coalesce/Value function
expected exception The data type, length or value of arguments 'SMALLINT' and 'CHAR () FOR BIT DATA' is incompatible.
SELECT VALUE(SMALLINTCOL,CHARFORBITCOL) from AllDataTypesTable
 Operands SMALLINT , CHAR(60) FOR BIT DATA are incompatible for Coalesce/Value function
expected exception The data type, length or value of arguments 'SMALLINT' and 'CHAR () FOR BIT DATA' is incompatible.
SELECT COALESCE(SMALLINTCOL,VARCHARFORBITCOL) from AllDataTypesTable
 Operands SMALLINT , VARCHAR(60) FOR BIT DATA are incompatible for Coalesce/Value function
expected exception The data type, length or value of arguments 'SMALLINT' and 'VARCHAR () FOR BIT DATA' is incompatible.
SELECT VALUE(SMALLINTCOL,VARCHARFORBITCOL) from AllDataTypesTable
 Operands SMALLINT , VARCHAR(60) FOR BIT DATA are incompatible for Coalesce/Value function
expected exception The data type, length or value of arguments 'SMALLINT' and 'VARCHAR () FOR BIT DATA' is incompatible.
SELECT COALESCE(SMALLINTCOL,LVARCHARFORBITCOL) from AllDataTypesTable
 Operands SMALLINT , LONG VARCHAR FOR BIT DATA are incompatible for Coalesce/Value function
expected exception The data type, length or value of arguments 'SMALLINT' and 'LONG VARCHAR FOR BIT DATA' is incompatible.
SELECT VALUE(SMALLINTCOL,LVARCHARFORBITCOL) from AllDataTypesTable
 Operands SMALLINT , LONG VARCHAR FOR BIT DATA are incompatible for Coalesce/Value function
expected exception The data type, length or value of arguments 'SMALLINT' and 'LONG VARCHAR FOR BIT DATA' is incompatible.
SELECT COALESCE(SMALLINTCOL,CLOBCOL) from AllDataTypesTable
 Operands SMALLINT , CLOB(1k) are incompatible for Coalesce/Value function
expected exception The data type, length or value of arguments 'SMALLINT' and 'CLOB' is incompatible.
SELECT VALUE(SMALLINTCOL,CLOBCOL) from AllDataTypesTable
 Operands SMALLINT , CLOB(1k) are incompatible for Coalesce/Value function
expected exception The data type, length or value of arguments 'SMALLINT' and 'CLOB' is incompatible.
SELECT COALESCE(SMALLINTCOL,DATECOL) from AllDataTypesTable
 Operands SMALLINT , DATE are incompatible for Coalesce/Value function
expected exception The data type, length or value of arguments 'SMALLINT' and 'DATE' is incompatible.
SELECT VALUE(SMALLINTCOL,DATECOL) from AllDataTypesTable
 Operands SMALLINT , DATE are incompatible for Coalesce/Value function
expected exception The data type, length or value of arguments 'SMALLINT' and 'DATE' is incompatible.
SELECT COALESCE(SMALLINTCOL,TIMECOL) from AllDataTypesTable
 Operands SMALLINT , TIME are incompatible for Coalesce/Value function
expected exception The data type, length or value of arguments 'SMALLINT' and 'TIME' is incompatible.
SELECT VALUE(SMALLINTCOL,TIMECOL) from AllDataTypesTable
 Operands SMALLINT , TIME are incompatible for Coalesce/Value function
expected exception The data type, length or value of arguments 'SMALLINT' and 'TIME' is incompatible.
SELECT COALESCE(SMALLINTCOL,TIMESTAMPCOL) from AllDataTypesTable
 Operands SMALLINT , TIMESTAMP are incompatible for Coalesce/Value function
expected exception The data type, length or value of arguments 'SMALLINT' and 'TIMESTAMP' is incompatible.
SELECT VALUE(SMALLINTCOL,TIMESTAMPCOL) from AllDataTypesTable
 Operands SMALLINT , TIMESTAMP are incompatible for Coalesce/Value function
expected exception The data type, length or value of arguments 'SMALLINT' and 'TIMESTAMP' is incompatible.
SELECT COALESCE(SMALLINTCOL,BLOBCOL) from AllDataTypesTable
 Operands SMALLINT , BLOB(1k) are incompatible for Coalesce/Value function
expected exception The data type, length or value of arguments 'SMALLINT' and 'BLOB' is incompatible.
SELECT VALUE(SMALLINTCOL,BLOBCOL) from AllDataTypesTable
 Operands SMALLINT , BLOB(1k) are incompatible for Coalesce/Value function
expected exception The data type, length or value of arguments 'SMALLINT' and 'BLOB' is incompatible.
SELECT COALESCE(INTEGERCOL,SMALLINTCOL) from AllDataTypesTable
 Coalesc/Value with operands INTEGER , SMALLINT will have result data type of INTEGER
	 COL1(datatype : INTEGER, precision : 10, scale : 0)
	 ---------------------------------------------------
	{null}
	{11}
	{111}
	{2}
SELECT VALUE(INTEGERCOL,SMALLINTCOL) from AllDataTypesTable
 Coalesc/Value with operands INTEGER , SMALLINT will have result data type of INTEGER
	 COL1(datatype : INTEGER, precision : 10, scale : 0)
	 ---------------------------------------------------
	{null}
	{11}
	{111}
	{2}
SELECT COALESCE(INTEGERCOL,INTEGERCOL) from AllDataTypesTable
 Coalesc/Value with operands INTEGER , INTEGER will have result data type of INTEGER
	 COL1(datatype : INTEGER, precision : 10, scale : 0)
	 ---------------------------------------------------
	{null}
	{11}
	{111}
	{null}
SELECT VALUE(INTEGERCOL,INTEGERCOL) from AllDataTypesTable
 Coalesc/Value with operands INTEGER , INTEGER will have result data type of INTEGER
	 COL1(datatype : INTEGER, precision : 10, scale : 0)
	 ---------------------------------------------------
	{null}
	{11}
	{111}
	{null}
SELECT COALESCE(INTEGERCOL,BIGINTCOL) from AllDataTypesTable
 Coalesc/Value with operands INTEGER , BIGINT will have result data type of BIGINT
	 COL1(datatype : BIGINT, precision : 19, scale : 0)
	 --------------------------------------------------
	{null}
	{11}
	{111}
	{3333}
SELECT VALUE(INTEGERCOL,BIGINTCOL) from AllDataTypesTable
 Coalesc/Value with operands INTEGER , BIGINT will have result data type of BIGINT
	 COL1(datatype : BIGINT, precision : 19, scale : 0)
	 --------------------------------------------------
	{null}
	{11}
	{111}
	{3333}
SELECT COALESCE(INTEGERCOL,DECIMALCOL) from AllDataTypesTable
 Coalesc/Value with operands INTEGER , DECIMAL(10,5) will have result data type of DECIMAL
	 COL1(datatype : DECIMAL, precision : 15, scale : 5)
	 ---------------------------------------------------
	{null}
	{11.00000}
	{111.00000}
	{null}
SELECT VALUE(INTEGERCOL,DECIMALCOL) from AllDataTypesTable
 Coalesc/Value with operands INTEGER , DECIMAL(10,5) will have result data type of DECIMAL
	 COL1(datatype : DECIMAL, precision : 15, scale : 5)
	 ---------------------------------------------------
	{null}
	{11.00000}
	{111.00000}
	{null}
SELECT COALESCE(INTEGERCOL,REALCOL) from AllDataTypesTable
 Coalesc/Value with operands INTEGER , REAL will have result data type of DOUBLE
	 COL1(datatype : DOUBLE, precision : 15, scale : 0)
	 --------------------------------------------------
	{null}
	{11.0}
	{111.0}
	{4.443999767303467}
SELECT VALUE(INTEGERCOL,REALCOL) from AllDataTypesTable
 Coalesc/Value with operands INTEGER , REAL will have result data type of DOUBLE
	 COL1(datatype : DOUBLE, precision : 15, scale : 0)
	 --------------------------------------------------
	{null}
	{11.0}
	{111.0}
	{4.443999767303467}
SELECT COALESCE(INTEGERCOL,DOUBLECOL) from AllDataTypesTable
 Coalesc/Value with operands INTEGER , DOUBLE will have result data type of DOUBLE
	 COL1(datatype : DOUBLE, precision : 15, scale : 0)
	 --------------------------------------------------
	{null}