Author: shawkins Date: 2009-09-23 11:39:01 -0400 (Wed, 23 Sep 2009) New Revision: 1465 Modified: trunk/connectors/connector-jdbc/src/main/java/org/teiid/connector/jdbc/oracle/OracleSQLTranslator.java trunk/connectors/connector-jdbc/src/test/java/org/teiid/connector/jdbc/oracle/TestOracleConvertModifier.java trunk/connectors/connector-jdbc/src/test/java/org/teiid/connector/jdbc/oracle/TestOracleTranslator.java Log: TEIID-852 fix to account for concat null in timestamp to time conversion. Modified: trunk/connectors/connector-jdbc/src/main/java/org/teiid/connector/jdbc/oracle/OracleSQLTranslator.java =================================================================== --- trunk/connectors/connector-jdbc/src/main/java/org/teiid/connector/jdbc/oracle/OracleSQLTranslator.java 2009-09-23 14:35:41 UTC (rev 1464) +++ trunk/connectors/connector-jdbc/src/main/java/org/teiid/connector/jdbc/oracle/OracleSQLTranslator.java 2009-09-23 15:39:01 UTC (rev 1465) @@ -119,7 +119,7 @@ convertModifier.addConvert(FunctionModifier.TIMESTAMP, FunctionModifier.TIME, new FunctionModifier() { @Override public List<?> translate(IFunction function) { - return Arrays.asList("to_date('1970-01-01 ' || to_char(",function.getParameters().get(0),", 'HH24:MI:SS'), 'YYYY-MM-DD HH24:MI:SS')"); //$NON-NLS-1$ //$NON-NLS-2$ + return Arrays.asList("case when ", function.getParameters().get(0), " is null then null else to_date('1970-01-01 ' || to_char(",function.getParameters().get(0),", 'HH24:MI:SS'), 'YYYY-MM-DD HH24:MI:SS') end"); //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$ } }); convertModifier.addConvert(FunctionModifier.TIMESTAMP, FunctionModifier.DATE, new FunctionModifier() { Modified: trunk/connectors/connector-jdbc/src/test/java/org/teiid/connector/jdbc/oracle/TestOracleConvertModifier.java =================================================================== --- trunk/connectors/connector-jdbc/src/test/java/org/teiid/connector/jdbc/oracle/TestOracleConvertModifier.java 2009-09-23 14:35:41 UTC (rev 1464) +++ trunk/connectors/connector-jdbc/src/test/java/org/teiid/connector/jdbc/oracle/TestOracleConvertModifier.java 2009-09-23 15:39:01 UTC (rev 1465) @@ -511,7 +511,7 @@ @Test public void testTimestampToTime() throws Exception { Timestamp ts = TimestampUtil.createTimestamp(103, 10, 1, 12, 5, 2, 0); - helpTest(LANG_FACTORY.createLiteral(ts, Timestamp.class), "time", "to_date('1970-01-01 ' || to_char({ts'2003-11-01 12:05:02.0'}, 'HH24:MI:SS'), 'YYYY-MM-DD HH24:MI:SS')"); //$NON-NLS-1$ //$NON-NLS-2$ + helpTest(LANG_FACTORY.createLiteral(ts, Timestamp.class), "time", "case when {ts'2003-11-01 12:05:02.0'} is null then null else to_date('1970-01-01 ' || to_char({ts'2003-11-01 12:05:02.0'}, 'HH24:MI:SS'), 'YYYY-MM-DD HH24:MI:SS') end"); //$NON-NLS-1$ //$NON-NLS-2$ } } Modified: trunk/connectors/connector-jdbc/src/test/java/org/teiid/connector/jdbc/oracle/TestOracleTranslator.java =================================================================== --- trunk/connectors/connector-jdbc/src/test/java/org/teiid/connector/jdbc/oracle/TestOracleTranslator.java 2009-09-23 14:35:41 UTC (rev 1464) +++ trunk/connectors/connector-jdbc/src/test/java/org/teiid/connector/jdbc/oracle/TestOracleTranslator.java 2009-09-23 15:39:01 UTC (rev 1465) @@ -150,14 +150,6 @@ input, output, TRANSLATOR); } - @Test public void testConversion5() throws Exception { - String input = "SELECT convert(convert(TIMESTAMPVALUE, time), string) FROM BQT1.SMALLA"; //$NON-NLS-1$ - String output = "SELECT to_char(to_date('1970-01-01 ' || to_char(SmallA.TimestampValue, 'HH24:MI:SS'), 'YYYY-MM-DD HH24:MI:SS'), 'HH24:MI:SS') FROM SmallA"; //$NON-NLS-1$ - - MetadataFactory.helpTestVisitor(MetadataFactory.BQT_VDB, - input, output, - TRANSLATOR); - } @Test public void testConversion6() throws Exception { String input = "SELECT convert(convert(TIMEVALUE, timestamp), string) FROM BQT1.SMALLA"; //$NON-NLS-1$ String output = "SELECT to_char(cast(SmallA.TimeValue AS timestamp), 'YYYY-MM-DD HH24:MI:SS.FF') FROM SmallA"; //$NON-NLS-1$