coalescetests.java

derby database source code.good for you.

			PreparedStatement ps;
    try {
			s.executeUpdate("drop table tF");
    } catch(Exception ex) {}
			s.executeUpdate("create table tF (dateCol date, charCol char(10), varcharCol varchar(50))");
			s.executeUpdate("insert into tF values(null, null, null)");
			s.executeUpdate("insert into tF values(date('1992-01-02'), '1992-01-03', '1992-01-04')");

			System.out.println("TestF - focus on date datatypes");
			System.out.println("TestF1a - coalesce(dateCol,dateCol)");
			dumpRSwithScale(s.executeQuery("select coalesce(dateCol,dateCol) from tF"));

			System.out.println("TestF1b - value(dateCol,dateCol)");
			dumpRSwithScale(s.executeQuery("select value(dateCol,dateCol) from tF"));

			System.out.println("TestF2a - coalesce(dateCol,charCol)");
			dumpRSwithScale(s.executeQuery("select coalesce(dateCol,charCol) from tF"));

			System.out.println("TestF2b - value(dateCol,charCol)");
			dumpRSwithScale(s.executeQuery("select value(dateCol,charCol) from tF"));

			System.out.println("TestF3a - coalesce(charCol,dateCol)");
			dumpRSwithScale(s.executeQuery("select coalesce(charCol,dateCol) from tF"));

			System.out.println("TestF3b - value(charCol,dateCol)");
			dumpRSwithScale(s.executeQuery("select value(charCol,dateCol) from tF"));

			System.out.println("TestF4a - coalesce(dateCol,varcharCol)");
			dumpRSwithScale(s.executeQuery("select coalesce(dateCol,charCol) from tF"));

			System.out.println("TestF4b - value(dateCol,varcharCol)");
			dumpRSwithScale(s.executeQuery("select value(dateCol,charCol) from tF"));

			System.out.println("TestF5a - coalesce(varcharCol,dateCol)");
			dumpRSwithScale(s.executeQuery("select coalesce(charCol,dateCol) from tF"));

			System.out.println("TestF5b - value(varcharCol,dateCol)");
			dumpRSwithScale(s.executeQuery("select value(charCol,dateCol) from tF"));

			System.out.println("TestF - Try invalid string representation of date into chars and varchars and then use them in coalesce function with date datatype");
			s.executeUpdate("insert into tF values(date('1992-01-01'), 'I am char', 'I am varchar')");

			try {
				System.out.println("TestF6a - coalesce(charCol,dateCol) will fail because one row has invalid string representation of date in the char column");
				dumpRSwithScale(s.executeQuery("select coalesce(charCol,dateCol) from tF"));
				System.out.println("TestF6a - should have failed");
			} catch (SQLException e) {
				if (e.getSQLState().equals("22007"))
					System.out.println("expected exception " + e.getMessage());
				else
					dumpSQLExceptions(e);
			}

			try {
				System.out.println("TestF6b - value(charCol,dateCol) will fail because one row has invalid string representation of date in the char column");
				dumpRSwithScale(s.executeQuery("select value(charCol,dateCol) from tF"));
				System.out.println("TestF6b - should have failed");
			} catch (SQLException e) {
				if (e.getSQLState().equals("22007"))
					System.out.println("expected exception " + e.getMessage());
				else
					dumpSQLExceptions(e);
			}

			try {
				System.out.println("TestF7a - coalesce(varcharCol,dateCol) will fail because one row has invalid string representation of date in the varchar column");
				dumpRSwithScale(s.executeQuery("select coalesce(charCol,dateCol) from tF"));
				System.out.println("TestF7a - should have failed");
			} catch (SQLException e) {
				if (e.getSQLState().equals("22007"))
					System.out.println("expected exception " + e.getMessage());
				else
					dumpSQLExceptions(e);
			}

			try {
				System.out.println("TestF7b - value(varcharCol,dateCol) will fail because one row has invalid string representation of date in the varchar column");
				dumpRSwithScale(s.executeQuery("select value(charCol,dateCol) from tF"));
				System.out.println("TestF7b - should have failed");
			} catch (SQLException e) {
				if (e.getSQLState().equals("22007"))
					System.out.println("expected exception " + e.getMessage());
				else
					dumpSQLExceptions(e);
			}

			s.executeUpdate("drop table tF");
		} catch (SQLException sqle) {
			org.apache.derby.tools.JDBCDisplayUtil.ShowSQLException(System.out, sqle);
			sqle.printStackTrace(System.out);
		}
	}

	public static void testTimeStampCoalesce( Connection conn) throws Throwable
	{
    try {
			Statement s = conn.createStatement();
			PreparedStatement ps;
    try {
			s.executeUpdate("drop table tH");
    } catch(Exception ex) {}
			s.executeUpdate("create table tH (timestampCol timestamp, charCol char(19), varcharCol varchar(50))");
			s.executeUpdate("insert into tH values(null, null, null)");
			s.executeUpdate("insert into tH values(timestamp('1992-01-01 12:30:30'), '1992-01-01 12:30:31', '1992-01-01 12:30:32')");

			System.out.println("TestH - focus on timestamp datatypes");
			System.out.println("TestH1a - coalesce(timestampCol,timestampCol)");
			dumpRSwithScale(s.executeQuery("select coalesce(timestampCol,timestampCol) from tH"));

			System.out.println("TestH1b - value(timestampCol,timestampCol)");
			dumpRSwithScale(s.executeQuery("select value(timestampCol,timestampCol) from tH"));

			System.out.println("TestH2a - coalesce(timestampCol,charCol)");
			dumpRSwithScale(s.executeQuery("select coalesce(timestampCol,charCol) from tH"));

			System.out.println("TestH2b - value(timestampCol,charCol)");
			dumpRSwithScale(s.executeQuery("select value(timestampCol,charCol) from tH"));

			System.out.println("TestH3a - coalesce(charCol,timestampCol)");
			dumpRSwithScale(s.executeQuery("select coalesce(charCol,timestampCol) from tH"));

			System.out.println("TestH3b - value(charCol,timestampCol)");
			dumpRSwithScale(s.executeQuery("select value(charCol,timestampCol) from tH"));

			System.out.println("TestH4a - coalesce(timestampCol,varcharCol)");
			dumpRSwithScale(s.executeQuery("select coalesce(timestampCol,charCol) from tH"));

			System.out.println("TestH4b - value(timestampCol,varcharCol)");
			dumpRSwithScale(s.executeQuery("select value(timestampCol,charCol) from tH"));

			System.out.println("TestH5a - coalesce(varcharCol,timestampCol)");
			dumpRSwithScale(s.executeQuery("select coalesce(charCol,timestampCol) from tH"));

			System.out.println("TestH5b - value(varcharCol,timestampCol)");
			dumpRSwithScale(s.executeQuery("select value(charCol,timestampCol) from tH"));

			System.out.println("TestH - Try invalid string representation of timestamp into chars and varchars and then use them in coalesce function with timestamp datatype");
			s.executeUpdate("insert into tH values(timestamp('1992-01-01 12:30:33'), 'I am char', 'I am varchar')");

			try {
				System.out.println("TestH6a - coalesce(charCol,timestampCol) will fail because one row has invalid string representation of timestamp in the char column");
				dumpRSwithScale(s.executeQuery("select coalesce(charCol,timestampCol) from tH"));
				System.out.println("TestH6a - should have failed");
			} catch (SQLException e) {
				if (e.getSQLState().equals("22007"))
					System.out.println("expected exception " + e.getMessage());
				else
					dumpSQLExceptions(e);
			}

			try {
				System.out.println("TestH6b - value(charCol,timestampCol) will fail because one row has invalid string representation of timestamp in the char column");
				dumpRSwithScale(s.executeQuery("select value(charCol,timestampCol) from tH"));
				System.out.println("TestH6b - should have failed");
			} catch (SQLException e) {
				if (e.getSQLState().equals("22007"))
					System.out.println("expected exception " + e.getMessage());
				else
					dumpSQLExceptions(e);
			}

			try {
				System.out.println("TestH7a - coalesce(varcharCol,timestampCol) will fail because one row has invalid string representation of timestamp in the varchar column");
				dumpRSwithScale(s.executeQuery("select coalesce(charCol,timestampCol) from tH"));
				System.out.println("TestH7a - should have failed");
			} catch (SQLException e) {
				if (e.getSQLState().equals("22007"))
					System.out.println("expected exception " + e.getMessage());
				else
					dumpSQLExceptions(e);
			}

			try {
				System.out.println("TestH7b - value(varcharCol,timestampCol) will fail because one row has invalid string representation of timestamp in the varchar column");
				dumpRSwithScale(s.executeQuery("select value(charCol,timestampCol) from tH"));
				System.out.println("TestH7b - should have failed");
			} catch (SQLException e) {
				if (e.getSQLState().equals("22007"))
					System.out.println("expected exception " + e.getMessage());
				else
					dumpSQLExceptions(e);
			}

			s.executeUpdate("drop table tH");
		} catch (SQLException sqle) {
			org.apache.derby.tools.JDBCDisplayUtil.ShowSQLException(System.out, sqle);
			sqle.printStackTrace(System.out);
		}
	}

	public static void testTimeCoalesce( Connection conn) throws Throwable
	{
    try {
			Statement s = conn.createStatement();
			PreparedStatement ps;
    try {
			s.executeUpdate("drop table tG");
    } catch(Exception ex) {}
			s.executeUpdate("create table tG (timeCol time, charCol char(10), varcharCol varchar(50))");
			s.executeUpdate("insert into tG values(null, null, null)");
			s.executeUpdate("insert into tG values(time('12:30:30'), '12:30:31', '12:30:32')");

			System.out.println("TestG - focus on time datatypes");
			System.out.println("TestG1a - coalesce(timeCol,timeCol)");
			dumpRSwithScale(s.executeQuery("select coalesce(timeCol,timeCol) from tG"));

			System.out.println("TestG1b - value(timeCol,timeCol)");
			dumpRSwithScale(s.executeQuery("select value(timeCol,timeCol) from tG"));

			System.out.println("TestG2a - coalesce(timeCol,charCol)");
			dumpRSwithScale(s.executeQuery("select coalesce(timeCol,charCol) from tG"));

			System.out.println("TestG2b - value(timeCol,charCol)");
			dumpRSwithScale(s.executeQuery("select value(timeCol,charCol) from tG"));

			System.out.println("TestG3a - coalesce(charCol,timeCol)");
			dumpRSwithScale(s.executeQuery("select coalesce(charCol,timeCol) from tG"));

			System.out.println("TestG3b - value(charCol,timeCol)");
			dumpRSwithScale(s.executeQuery("select value(charCol,timeCol) from tG"));

			System.out.println("TestG4a - coalesce(timeCol,varcharCol)");
			dumpRSwithScale(s.executeQuery("select coalesce(timeCol,charCol) from tG"));

			System.out.println("TestG4b - value(timeCol,varcharCol)");
			dumpRSwithScale(s.executeQuery("select value(timeCol,charCol) from tG"));

			System.out.println("TestG5a - coalesce(varcharCol,timeCol)");
			dumpRSwithScale(s.executeQuery("select coalesce(charCol,timeCol) from tG"));

			System.out.println("TestG5b - value(varcharCol,timeCol)");
			dumpRSwithScale(s.executeQuery("select value(charCol,timeCol) from tG"));

			System.out.println("TestG - Try invalid string representation of time into chars and varchars and then use them in coalesce function with time datatype");
			s.executeUpdate("insert into tG values(time('12:30:33'), 'I am char', 'I am varchar')");

			try {
				System.out.println("TestG6a - coalesce(charCol,timeCol) will fail because one row has invalid string representation of time in the char column");
				dumpRSwithScale(s.executeQuery("select coalesce(charCol,timeCol) from tG"));
				System.out.println("TestG6a - should have failed");
			} catch (SQLException e) {
				if (e.getSQLState().equals("22007"))
					System.out.println("expected exception " + e.getMessage());
				else
					dumpSQLExceptions(e);
			}

			try {
				System.out.println("TestG6b - value(charCol,timeCol) will fail because one row has invalid string representation of time in the char column");
				dumpRSwithScale(s.executeQuery("select value(charCol,timeCol) from tG"));
				System.out.println("TestG6b - should have failed");
			} catch (SQLException e) {
				if (e.getSQLState().equals("22007"))
					System.out.println("expected exception " + e.getMessage());
				else
					dumpSQLExceptions(e);
			}

			try {
				System.out.println("TestG7a - coalesce(varcharCol,timeCol) will fail because one row has invalid string representation of time in the varchar column");
				dumpRSwithScale(s.executeQuery("select coalesce(charCol,timeCol) from tG"));
				System.out.println("TestG7a - should have failed");
			} catch (SQLException e) {
				if (e.getSQLState().equals("22007"))
					System.out.println("expected exception " + e.getMessage());
				else
					dumpSQLExceptions(e);
			}