coalescetests.out

derby database source code.good for you.

	{2.0}
SELECT COALESCE(DOUBLECOL,SMALLINTCOL,INTEGERCOL,BIGINTCOL) from AllDataTypesTable
	 COL1(datatype : DOUBLE, precision : 15, scale : 0)
	 --------------------------------------------------
	{null}
	{5.5}
	{5.55}
	{2.0}
SELECT COALESCE(DOUBLECOL,SMALLINTCOL,INTEGERCOL,BIGINTCOL,DECIMALCOL) from AllDataTypesTable
	 COL1(datatype : DOUBLE, precision : 15, scale : 0)
	 --------------------------------------------------
	{null}
	{5.5}
	{5.55}
	{2.0}
SELECT COALESCE(DOUBLECOL,SMALLINTCOL,INTEGERCOL,BIGINTCOL,DECIMALCOL,REALCOL) from AllDataTypesTable
	 COL1(datatype : DOUBLE, precision : 15, scale : 0)
	 --------------------------------------------------
	{null}
	{5.5}
	{5.55}
	{2.0}
SELECT COALESCE(DOUBLECOL,SMALLINTCOL,INTEGERCOL,BIGINTCOL,DECIMALCOL,REALCOL,DOUBLECOL) from AllDataTypesTable
	 COL1(datatype : DOUBLE, precision : 15, scale : 0)
	 --------------------------------------------------
	{null}
	{5.5}
	{5.55}
	{2.0}
SELECT COALESCE(CHARCOL,CHARCOL) from AllDataTypesTable
	 COL1(datatype : CHAR, precision : 60, scale : 0)
	 ------------------------------------------------
	{null}
	{1992-01-06                                                  }
	{1992-01-16                                                  }
	{null}
SELECT COALESCE(CHARCOL,CHARCOL,VARCHARCOL) from AllDataTypesTable
	 COL1(datatype : VARCHAR, precision : 60, scale : 0)
	 ---------------------------------------------------
	{null}
	{1992-01-06                                                  }
	{1992-01-16                                                  }
	{15:30:20}
SELECT COALESCE(CHARCOL,CHARCOL,VARCHARCOL,LONGVARCHARCOL) from AllDataTypesTable
	 COL1(datatype : LONG VARCHAR, precision : 32700, scale : 0)
	 -----------------------------------------------------------
	{null}
	{1992-01-06                                                  }
	{1992-01-16                                                  }
	{15:30:20}
SELECT COALESCE(CHARCOL,CHARCOL,VARCHARCOL,LONGVARCHARCOL,CLOBCOL) from AllDataTypesTable
	 COL1(datatype : CLOB, precision : 32700, scale : 0)
	 ---------------------------------------------------
	{null}
	{1992-01-06                                                  }
	{1992-01-16                                                  }
	{15:30:20}
SELECT COALESCE(CHARCOL,CHARCOL,VARCHARCOL,LONGVARCHARCOL,CLOBCOL,DATECOL) from AllDataTypesTable
expected exception because mixing CLOB and DATA/TIME/TIMESTAMP arugments The data type, length or value of arguments 'LONG VARCHAR' and 'DATE' is incompatible.
SELECT COALESCE(CHARCOL,CHARCOL,VARCHARCOL,LONGVARCHARCOL,CLOBCOL,DATECOL,TIMECOL) from AllDataTypesTable
expected exception because mixing CLOB and DATA/TIME/TIMESTAMP arugments The data type, length or value of arguments 'LONG VARCHAR' and 'DATE' is incompatible.
SELECT COALESCE(CHARCOL,CHARCOL,VARCHARCOL,LONGVARCHARCOL,CLOBCOL,DATECOL,TIMECOL,TIMESTAMPCOL) from AllDataTypesTable
expected exception because mixing CLOB and DATA/TIME/TIMESTAMP arugments The data type, length or value of arguments 'LONG VARCHAR' and 'DATE' is incompatible.
SELECT COALESCE(VARCHARCOL,CHARCOL) from AllDataTypesTable
	 COL1(datatype : VARCHAR, precision : 60, scale : 0)
	 ---------------------------------------------------
	{null}
	{1992-01-07}
	{1992-01-17}
	{15:30:20}
SELECT COALESCE(VARCHARCOL,CHARCOL,VARCHARCOL) from AllDataTypesTable
	 COL1(datatype : VARCHAR, precision : 60, scale : 0)
	 ---------------------------------------------------
	{null}
	{1992-01-07}
	{1992-01-17}
	{15:30:20}
SELECT COALESCE(VARCHARCOL,CHARCOL,VARCHARCOL,LONGVARCHARCOL) from AllDataTypesTable
	 COL1(datatype : LONG VARCHAR, precision : 32700, scale : 0)
	 -----------------------------------------------------------
	{null}
	{1992-01-07}
	{1992-01-17}
	{15:30:20}
SELECT COALESCE(VARCHARCOL,CHARCOL,VARCHARCOL,LONGVARCHARCOL,CLOBCOL) from AllDataTypesTable
	 COL1(datatype : CLOB, precision : 32700, scale : 0)
	 ---------------------------------------------------
	{null}
	{1992-01-07}
	{1992-01-17}
	{15:30:20}
SELECT COALESCE(VARCHARCOL,CHARCOL,VARCHARCOL,LONGVARCHARCOL,CLOBCOL,DATECOL) from AllDataTypesTable
expected exception because mixing CLOB and DATA/TIME/TIMESTAMP arugments The data type, length or value of arguments 'LONG VARCHAR' and 'DATE' is incompatible.
SELECT COALESCE(VARCHARCOL,CHARCOL,VARCHARCOL,LONGVARCHARCOL,CLOBCOL,DATECOL,TIMECOL) from AllDataTypesTable
expected exception because mixing CLOB and DATA/TIME/TIMESTAMP arugments The data type, length or value of arguments 'LONG VARCHAR' and 'DATE' is incompatible.
SELECT COALESCE(VARCHARCOL,CHARCOL,VARCHARCOL,LONGVARCHARCOL,CLOBCOL,DATECOL,TIMECOL,TIMESTAMPCOL) from AllDataTypesTable
expected exception because mixing CLOB and DATA/TIME/TIMESTAMP arugments The data type, length or value of arguments 'LONG VARCHAR' and 'DATE' is incompatible.
SELECT COALESCE(LONGVARCHARCOL,CHARCOL) from AllDataTypesTable
	 COL1(datatype : LONG VARCHAR, precision : 32700, scale : 0)
	 -----------------------------------------------------------
	{null}
	{1992-01-08}
	{1992-01-18}
	{xxxxxxFILTERED-TIMESTAMPxxxxx
SELECT COALESCE(LONGVARCHARCOL,CHARCOL,VARCHARCOL) from AllDataTypesTable
	 COL1(datatype : LONG VARCHAR, precision : 32700, scale : 0)
	 -----------------------------------------------------------
	{null}
	{1992-01-08}
	{1992-01-18}
	{xxxxxxFILTERED-TIMESTAMPxxxxx
SELECT COALESCE(LONGVARCHARCOL,CHARCOL,VARCHARCOL,LONGVARCHARCOL) from AllDataTypesTable
	 COL1(datatype : LONG VARCHAR, precision : 32700, scale : 0)
	 -----------------------------------------------------------
	{null}
	{1992-01-08}
	{1992-01-18}
	{xxxxxxFILTERED-TIMESTAMPxxxxx
SELECT COALESCE(LONGVARCHARCOL,CHARCOL,VARCHARCOL,LONGVARCHARCOL,CLOBCOL) from AllDataTypesTable
	 COL1(datatype : CLOB, precision : 32700, scale : 0)
	 ---------------------------------------------------
	{null}
	{1992-01-08}
	{1992-01-18}
	{xxxxxxFILTERED-TIMESTAMPxxxxx
SELECT COALESCE(CHARFORBITCOL,CHARFORBITCOL) from AllDataTypesTable
	 COL1(datatype : CHAR () FOR BIT DATA, precision : 60, scale : 0)
	 ----------------------------------------------------------------
	{null}
	{10aa20202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020}
	{null}
	{10aaaa202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020}
SELECT COALESCE(CHARFORBITCOL,CHARFORBITCOL,VARCHARFORBITCOL) from AllDataTypesTable
	 COL1(datatype : VARCHAR () FOR BIT DATA, precision : 60, scale : 0)
	 -------------------------------------------------------------------
	{null}
	{10aa20202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020}
	{null}
	{10aaaa202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020}
SELECT COALESCE(CHARFORBITCOL,CHARFORBITCOL,VARCHARFORBITCOL,LVARCHARFORBITCOL) from AllDataTypesTable
	 COL1(datatype : LONG VARCHAR FOR BIT DATA, precision : 32700, scale : 0)
	 ------------------------------------------------------------------------
	{null}
	{10aa20202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020}
	{null}
	{10aaaa202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020202020}
SELECT COALESCE(VARCHARFORBITCOL,CHARFORBITCOL) from AllDataTypesTable
	 COL1(datatype : VARCHAR () FOR BIT DATA, precision : 60, scale : 0)
	 -------------------------------------------------------------------
	{null}
	{10bb}
	{null}
	{10bbbb}
SELECT COALESCE(VARCHARFORBITCOL,CHARFORBITCOL,VARCHARFORBITCOL) from AllDataTypesTable
	 COL1(datatype : VARCHAR () FOR BIT DATA, precision : 60, scale : 0)
	 -------------------------------------------------------------------
	{null}
	{10bb}
	{null}
	{10bbbb}
SELECT COALESCE(VARCHARFORBITCOL,CHARFORBITCOL,VARCHARFORBITCOL,LVARCHARFORBITCOL) from AllDataTypesTable
	 COL1(datatype : LONG VARCHAR FOR BIT DATA, precision : 32700, scale : 0)
	 ------------------------------------------------------------------------
	{null}
	{10bb}
	{null}
	{10bbbb}
SELECT COALESCE(LVARCHARFORBITCOL,CHARFORBITCOL) from AllDataTypesTable
	 COL1(datatype : LONG VARCHAR FOR BIT DATA, precision : 32700, scale : 0)
	 ------------------------------------------------------------------------
	{null}
	{10cc}
	{null}
	{10cccc}
SELECT COALESCE(LVARCHARFORBITCOL,CHARFORBITCOL,VARCHARFORBITCOL) from AllDataTypesTable
	 COL1(datatype : LONG VARCHAR FOR BIT DATA, precision : 32700, scale : 0)
	 ------------------------------------------------------------------------
	{null}
	{10cc}
	{null}
	{10cccc}
SELECT COALESCE(LVARCHARFORBITCOL,CHARFORBITCOL,VARCHARFORBITCOL,LVARCHARFORBITCOL) from AllDataTypesTable
	 COL1(datatype : LONG VARCHAR FOR BIT DATA, precision : 32700, scale : 0)
	 ------------------------------------------------------------------------
	{null}
	{10cc}
	{null}
	{10cccc}
SELECT COALESCE(CLOBCOL,CHARCOL) from AllDataTypesTable
	 COL1(datatype : CLOB, precision : 1024, scale : 0)
	 --------------------------------------------------
	{null}
	{13}
	{14}
	{null}
SELECT COALESCE(CLOBCOL,CHARCOL,VARCHARCOL) from AllDataTypesTable
	 COL1(datatype : CLOB, precision : 1024, scale : 0)
	 --------------------------------------------------
	{null}
	{13}
	{14}
	{15:30:20}
SELECT COALESCE(CLOBCOL,CHARCOL,VARCHARCOL,LONGVARCHARCOL) from AllDataTypesTable
	 COL1(datatype : CLOB, precision : 32700, scale : 0)
	 ---------------------------------------------------
	{null}
	{13}
	{14}
	{15:30:20}
SELECT COALESCE(CLOBCOL,CHARCOL,VARCHARCOL,LONGVARCHARCOL,CLOBCOL) from AllDataTypesTable
	 COL1(datatype : CLOB, precision : 32700, scale : 0)
	 ---------------------------------------------------
	{null}
	{13}
	{14}
	{15:30:20}
SELECT COALESCE(DATECOL,CHARCOL) from AllDataTypesTable
	 COL1(datatype : DATE, precision : 10, scale : 0)
	 ------------------------------------------------
	{null}
	{2000-01-01}
	{2000-01-01}
	{null}
SELECT COALESCE(DATECOL,CHARCOL,VARCHARCOL) from AllDataTypesTable
	 COL1(datatype : DATE, precision : 10, scale : 0)
	 ------------------------------------------------
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(DATECOL,CHARCOL,VARCHARCOL,DATECOL) from AllDataTypesTable
	 COL1(datatype : DATE, precision : 10, scale : 0)
	 ------------------------------------------------
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(TIMECOL,CHARCOL) from AllDataTypesTable
	 COL1(datatype : TIME, precision : 8, scale : 0)
	 -----------------------------------------------
	{null}
	{15:30:20}
	{15:30:20}
	{null}
SELECT COALESCE(TIMECOL,CHARCOL,VARCHARCOL) from AllDataTypesTable
	 COL1(datatype : TIME, precision : 8, scale : 0)
	 -----------------------------------------------
	{null}
	{15:30:20}
	{15:30:20}
	{15:30:20}
SELECT COALESCE(TIMECOL,CHARCOL,VARCHARCOL,TIMECOL) from AllDataTypesTable
	 COL1(datatype : TIME, precision : 8, scale : 0)
	 -----------------------------------------------
	{null}
	{15:30:20}
	{15:30:20}
	{15:30:20}
SELECT COALESCE(TIMESTAMPCOL,CHARCOL) from AllDataTypesTable
	 COL1(datatype : TIMESTAMP, precision : 26, scale : 6)
	 -----------------------------------------------------
	{null}
	{xxxxxxFILTERED-TIMESTAMPxxxxx}
	{xxxxxxFILTERED-TIMESTAMPxxxxx}
	{null}
SELECT COALESCE(TIMESTAMPCOL,CHARCOL,VARCHARCOL) from AllDataTypesTable
	 COL1(datatype : TIMESTAMP, precision : 26, scale : 6)