[teiid-commits] teiid SVN: r3166 - in branches/7.4.x/connectors/translator-jdbc/src: main/java/org/teiid/translator/jdbc/intersyscache and 2 other directories.

Author: rareddy Date: 2011-05-12 10:19:43 -0400 (Thu, 12 May 2011) New Revision: 3166 Modified: branches/7.4.x/connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/ingres/IngresExecutionFactory.java branches/7.4.x/connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/intersyscache/InterSystemsCacheExecutionFactory.java branches/7.4.x/connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/teradata/TeradataExecutionFactory.java branches/7.4.x/connectors/translator-jdbc/src/test/java/org/teiid/translator/jdbc/teradata/TestTeradataTranslator.java Log: TEIID-1495: few more changes to pushdown correct queries to Teradata. Also fixed other translators with errors in defining them to support all pushdown functions. Modified: branches/7.4.x/connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/ingres/IngresExecutionFactory.java =================================================================== --- branches/7.4.x/connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/ingres/IngresExecutionFactory.java 2011-05-12 03:37:24 UTC (rev 3165) +++ branches/7.4.x/connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/ingres/IngresExecutionFactory.java 2011-05-12 14:19:43 UTC (rev 3166) @@ -67,13 +67,7 @@ convert.addTypeMapping("clob", FunctionModifier.CLOB); //$NON-NLS-1$ convert.addNumericBooleanConversions(); registerFunctionModifier(SourceSystemFunctions.CONVERT, convert); - } - - @Override - public List<String> getSupportedFunctions() { - List<String> supportedFunctions = new ArrayList<String>(); - supportedFunctions.addAll(super.getSupportedFunctions()); - + registerFunctionModifier(SourceSystemFunctions.BITAND, new AliasModifier("bit_and")); //$NON-NLS-1$ registerFunctionModifier(SourceSystemFunctions.BITNOT, new AliasModifier("bit_not")); //$NON-NLS-1$ registerFunctionModifier(SourceSystemFunctions.BITOR, new AliasModifier("bit_or")); //$NON-NLS-1$ @@ -84,16 +78,29 @@ registerFunctionModifier(SourceSystemFunctions.RAND, new AliasModifier("random")); //$NON-NLS-1$ registerFunctionModifier(SourceSystemFunctions.UCASE, new AliasModifier("uppercase")); //$NON-NLS-1$ registerFunctionModifier(SourceSystemFunctions.DAYOFMONTH, new AliasModifier("day")); //$NON-NLS-1$ - registerFunctionModifier(SourceSystemFunctions.LOCATE, new LocateFunctionModifier(getLanguageFactory())); - + registerFunctionModifier(SourceSystemFunctions.LOCATE, new LocateFunctionModifier(getLanguageFactory())); + } + + @Override + public List<String> getSupportedFunctions() { + List<String> supportedFunctions = new ArrayList<String>(); + supportedFunctions.addAll(super.getSupportedFunctions()); + supportedFunctions.add(SourceSystemFunctions.ABS); supportedFunctions.add(SourceSystemFunctions.ATAN); + supportedFunctions.add(SourceSystemFunctions.BITAND); + supportedFunctions.add(SourceSystemFunctions.BITNOT); + supportedFunctions.add(SourceSystemFunctions.BITOR); + supportedFunctions.add(SourceSystemFunctions.BITXOR); supportedFunctions.add(SourceSystemFunctions.CONCAT); supportedFunctions.add(SourceSystemFunctions.COS); supportedFunctions.add(SourceSystemFunctions.CONVERT); + supportedFunctions.add(SourceSystemFunctions.CURTIME); + supportedFunctions.add(SourceSystemFunctions.CURDATE); supportedFunctions.add(SourceSystemFunctions.DAYOFMONTH); supportedFunctions.add(SourceSystemFunctions.EXP); supportedFunctions.add(SourceSystemFunctions.HOUR); + supportedFunctions.add(SourceSystemFunctions.LCASE); supportedFunctions.add(SourceSystemFunctions.LEFT); supportedFunctions.add(SourceSystemFunctions.LPAD); supportedFunctions.add(SourceSystemFunctions.LOCATE); @@ -102,6 +109,7 @@ supportedFunctions.add(SourceSystemFunctions.MINUTE); supportedFunctions.add(SourceSystemFunctions.MONTH); supportedFunctions.add(SourceSystemFunctions.POWER); + supportedFunctions.add(SourceSystemFunctions.RAND); supportedFunctions.add(SourceSystemFunctions.RIGHT); supportedFunctions.add(SourceSystemFunctions.RPAD); supportedFunctions.add(SourceSystemFunctions.SECOND); @@ -109,6 +117,7 @@ supportedFunctions.add(SourceSystemFunctions.SQRT); supportedFunctions.add(SourceSystemFunctions.SUBSTRING); supportedFunctions.add(SourceSystemFunctions.YEAR); + supportedFunctions.add(SourceSystemFunctions.UCASE); return supportedFunctions; } Modified: branches/7.4.x/connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/intersyscache/InterSystemsCacheExecutionFactory.java =================================================================== --- branches/7.4.x/connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/intersyscache/InterSystemsCacheExecutionFactory.java 2011-05-12 03:37:24 UTC (rev 3165) +++ branches/7.4.x/connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/intersyscache/InterSystemsCacheExecutionFactory.java 2011-05-12 14:19:43 UTC (rev 3166) @@ -104,7 +104,7 @@ @Override public List<?> translate(Function function) { if (function.getType() == TypeFacility.RUNTIME_TYPES.INTEGER || function.getType() == TypeFacility.RUNTIME_TYPES.LONG) { - Function result = convert.createConvertFunction(getLanguageFactory(), function, TypeFacility.getDataTypeName(function.getType())); + Function result = ConvertModifier.createConvertFunction(getLanguageFactory(), function, TypeFacility.getDataTypeName(function.getType())); function.setType(TypeFacility.RUNTIME_TYPES.BIG_DECIMAL); return Arrays.asList(result); } @@ -119,19 +119,44 @@ supportedFunctions.addAll(super.getSupportedFunctions()); supportedFunctions.add(SourceSystemFunctions.ABS); + supportedFunctions.add(SourceSystemFunctions.ACOS); + supportedFunctions.add(SourceSystemFunctions.ASIN); + supportedFunctions.add(SourceSystemFunctions.ATAN); supportedFunctions.add(SourceSystemFunctions.ASCII); supportedFunctions.add(SourceSystemFunctions.CEILING); supportedFunctions.add(SourceSystemFunctions.CHAR); supportedFunctions.add(SourceSystemFunctions.COALESCE); + supportedFunctions.add(SourceSystemFunctions.CONCAT); supportedFunctions.add(SourceSystemFunctions.CONVERT); + supportedFunctions.add(SourceSystemFunctions.COS); + supportedFunctions.add(SourceSystemFunctions.COT); + supportedFunctions.add(SourceSystemFunctions.CURDATE); + supportedFunctions.add(SourceSystemFunctions.CURTIME); + supportedFunctions.add(SourceSystemFunctions.DAYNAME); + supportedFunctions.add(SourceSystemFunctions.DAYOFMONTH); + supportedFunctions.add(SourceSystemFunctions.DAYOFWEEK); + supportedFunctions.add(SourceSystemFunctions.DAYOFYEAR); + supportedFunctions.add(SourceSystemFunctions.EXP); supportedFunctions.add(SourceSystemFunctions.FLOOR); + supportedFunctions.add(SourceSystemFunctions.HOUR); supportedFunctions.add(SourceSystemFunctions.IFNULL); supportedFunctions.add(SourceSystemFunctions.LCASE); supportedFunctions.add(SourceSystemFunctions.LENGTH); supportedFunctions.add(SourceSystemFunctions.LPAD); supportedFunctions.add(SourceSystemFunctions.LTRIM); + supportedFunctions.add(SourceSystemFunctions.LOG); + supportedFunctions.add(SourceSystemFunctions.LOG10); + supportedFunctions.add(SourceSystemFunctions.LEFT); + supportedFunctions.add(SourceSystemFunctions.MINUTE); + supportedFunctions.add(SourceSystemFunctions.MONTH); + supportedFunctions.add(SourceSystemFunctions.MONTHNAME); + supportedFunctions.add(SourceSystemFunctions.MOD); supportedFunctions.add(SourceSystemFunctions.NULLIF); + supportedFunctions.add(SourceSystemFunctions.NOW); + supportedFunctions.add(SourceSystemFunctions.PI); supportedFunctions.add(SourceSystemFunctions.POWER); + supportedFunctions.add(SourceSystemFunctions.QUARTER); + supportedFunctions.add(SourceSystemFunctions.RIGHT); supportedFunctions.add(SourceSystemFunctions.REPEAT); supportedFunctions.add(SourceSystemFunctions.REPLACE); supportedFunctions.add(SourceSystemFunctions.ROUND); @@ -139,8 +164,16 @@ supportedFunctions.add(SourceSystemFunctions.RTRIM); supportedFunctions.add(SourceSystemFunctions.SIGN); supportedFunctions.add(SourceSystemFunctions.SUBSTRING); + supportedFunctions.add(SourceSystemFunctions.SIN); + supportedFunctions.add(SourceSystemFunctions.SECOND); + supportedFunctions.add(SourceSystemFunctions.SQRT); + supportedFunctions.add(SourceSystemFunctions.TAN); + supportedFunctions.add(SourceSystemFunctions.TIMESTAMPADD); + supportedFunctions.add(SourceSystemFunctions.TIMESTAMPDIFF); + supportedFunctions.add(SourceSystemFunctions.TRUNCATE); supportedFunctions.add(SourceSystemFunctions.UCASE); supportedFunctions.add(SourceSystemFunctions.XMLCONCAT); + supportedFunctions.add(SourceSystemFunctions.WEEK); return supportedFunctions; } Modified: branches/7.4.x/connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/teradata/TeradataExecutionFactory.java =================================================================== --- branches/7.4.x/connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/teradata/TeradataExecutionFactory.java 2011-05-12 03:37:24 UTC (rev 3165) +++ branches/7.4.x/connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/teradata/TeradataExecutionFactory.java 2011-05-12 14:19:43 UTC (rev 3166) @@ -30,8 +30,10 @@ import java.util.List; import org.teiid.core.types.DataTypeManager; +import org.teiid.language.ColumnReference; import org.teiid.language.Expression; import org.teiid.language.Function; +import org.teiid.language.LanguageFactory; import org.teiid.language.Literal; import org.teiid.metadata.FunctionMethod; import org.teiid.metadata.FunctionParameter; @@ -44,7 +46,6 @@ import org.teiid.translator.jdbc.FunctionModifier; import org.teiid.translator.jdbc.JDBCExecutionFactory; import org.teiid.translator.jdbc.SQLConversionVisitor; -import org.teiid.translator.jdbc.oracle.LeftOrRightFunctionModifier; @@ -80,8 +81,10 @@ convert.addConvert(FunctionModifier.TIMESTAMP, FunctionModifier.TIME, new TimeModifier("TIME")); //$NON-NLS-1$ convert.addConvert(FunctionModifier.TIMESTAMP, FunctionModifier.DATE, new TimeModifier("DATE")); //$NON-NLS-1$ convert.addConvert(FunctionModifier.TIME, FunctionModifier.TIMESTAMP, new TimeModifier("TIMESTAMP")); //$NON-NLS-1$ - convert.addConvert(FunctionModifier.DATE, FunctionModifier.TIMESTAMP, new TimeModifier("TIMESTAMP")); //$NON-NLS-1$ - + convert.addConvert(FunctionModifier.DATE, FunctionModifier.TIMESTAMP, new TimeModifier("TIMESTAMP")); //$NON-NLS-1$ + convert.addConvert(FunctionModifier.TIMESTAMP, FunctionModifier.STRING, new TimeModifier("varchar(100)")); //$NON-NLS-1$ + convert.addConvert(FunctionModifier.TIME, FunctionModifier.STRING, new TimeModifier("varchar(100)")); //$NON-NLS-1$ + convert.addConvert(FunctionModifier.DATE, FunctionModifier.STRING, new TimeModifier("varchar(100)")); //$NON-NLS-1$ convert.addTypeMapping("varchar(4000)", FunctionModifier.STRING); //$NON-NLS-1$ convert.addNumericBooleanConversions(); @@ -90,27 +93,20 @@ registerFunctionModifier(SourceSystemFunctions.RAND, new AliasModifier("random")); //$NON-NLS-1$ registerFunctionModifier(SourceSystemFunctions.LOG, new AliasModifier("LN")); //$NON-NLS-1$ - registerFunctionModifier(SourceSystemFunctions.LCASE, new AliasModifier("LOWER")); //$NON-NLS-1$ - registerFunctionModifier(SourceSystemFunctions.UCASE, new AliasModifier("UPPER")); //$NON-NLS-1$ + registerFunctionModifier(SourceSystemFunctions.LCASE, new UpperOrLowerModifier("LOWER", this.convert)); //$NON-NLS-1$ + registerFunctionModifier(SourceSystemFunctions.UCASE, new UpperOrLowerModifier("UPPER", this.convert)); //$NON-NLS-1$ registerFunctionModifier(SourceSystemFunctions.LENGTH, new AliasModifier("CHARACTER_LENGTH")); //$NON-NLS-1$ registerFunctionModifier(SourceSystemFunctions.CURDATE, new AliasModifier("CURRENT_DATE")); //$NON-NLS-1$ registerFunctionModifier(SourceSystemFunctions.CURTIME, new AliasModifier("CURRENT_TIME")); //$NON-NLS-1$ - registerFunctionModifier(SourceSystemFunctions.SUBSTRING, new AliasModifier("substr"));//$NON-NLS-1$ - registerFunctionModifier(SourceSystemFunctions.YEAR, new ExtractModifier("YEAR")); //$NON-NLS-1$ registerFunctionModifier(SourceSystemFunctions.MONTH, new ExtractModifier("MONTH")); //$NON-NLS-1$ registerFunctionModifier(SourceSystemFunctions.DAYOFMONTH, new ExtractModifier("DAY")); //$NON-NLS-1$ registerFunctionModifier(SourceSystemFunctions.HOUR, new ExtractModifier("HOUR")); //$NON-NLS-1$ registerFunctionModifier(SourceSystemFunctions.MINUTE, new ExtractModifier("MINUTE")); //$NON-NLS-1$ registerFunctionModifier(SourceSystemFunctions.SECOND, new ExtractModifier("SECOND")); //$NON-NLS-1$ - registerFunctionModifier(SourceSystemFunctions.LOCATE, new FunctionModifier() { - @Override - public List<?> translate(Function function) { - return Arrays.asList("position(",function.getParameters().get(0)," in ",function.getParameters().get(1) ,")"); //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$ - } - }); - registerFunctionModifier(SourceSystemFunctions.LEFT, new LeftOrRightFunctionModifier(getLanguageFactory())); - registerFunctionModifier(SourceSystemFunctions.RIGHT, new LeftOrRightFunctionModifier(getLanguageFactory())); + registerFunctionModifier(SourceSystemFunctions.LOCATE, new LocateModifier(this.convert)); + registerFunctionModifier(SourceSystemFunctions.LEFT, new LeftOrRightFunctionModifier(getLanguageFactory(), this.convert)); + registerFunctionModifier(SourceSystemFunctions.RIGHT, new LeftOrRightFunctionModifier(getLanguageFactory(), this.convert)); registerFunctionModifier(SourceSystemFunctions.COT, new FunctionModifier() { @Override public List<?> translate(Function function) { @@ -121,13 +117,13 @@ registerFunctionModifier(SourceSystemFunctions.LTRIM, new FunctionModifier() { @Override public List<?> translate(Function function) { - return Arrays.asList("TRIM(LEADING FROM ", function.getParameters().get(0), ")"); //$NON-NLS-1$ //$NON-NLS-2$ + return Arrays.asList("TRIM(LEADING FROM ", expressionToString(function.getParameters().get(0), convert), ")"); //$NON-NLS-1$ //$NON-NLS-2$ } }); registerFunctionModifier(SourceSystemFunctions.RTRIM, new FunctionModifier() { @Override public List<?> translate(Function function) { - return Arrays.asList("TRIM(TRAILING FROM ", function.getParameters().get(0), ")"); //$NON-NLS-1$ //$NON-NLS-2$ + return Arrays.asList("TRIM(TRAILING FROM ", expressionToString(function.getParameters().get(0), convert), ")"); //$NON-NLS-1$ //$NON-NLS-2$ } }); registerFunctionModifier(SourceSystemFunctions.MOD, new FunctionModifier() { @@ -167,6 +163,7 @@ supportedFunctions.add(SourceSystemFunctions.LOCATE); supportedFunctions.add(SourceSystemFunctions.LOG); supportedFunctions.add(SourceSystemFunctions.LCASE); + supportedFunctions.add("lower");//$NON-NLS-1$ supportedFunctions.add(SourceSystemFunctions.LTRIM); supportedFunctions.add(SourceSystemFunctions.LENGTH); supportedFunctions.add(SourceSystemFunctions.MINUTE); @@ -343,7 +340,55 @@ return false; } - public static class ExtractModifier extends FunctionModifier { + public static class LocateModifier extends FunctionModifier { + ConvertModifier convertModifier; + + public LocateModifier(ConvertModifier convertModifier) { + this.convertModifier = convertModifier; + } + + @Override + public List<?> translate(Function function) { + Expression expr1 = function.getParameters().get(0); + Expression expr2 = function.getParameters().get(1); + ArrayList target = new ArrayList(); + target.add("position("); //$NON-NLS-1$ + target.addAll(expressionToString(expr1, this.convertModifier)); + target.add( " in "); //$NON-NLS-1$ + target.addAll(expressionToString(expr2, this.convertModifier)); + target.add(")"); //$NON-NLS-1$ + return target; + } + } + + private static List<?> expressionToString(Expression expr, ConvertModifier modifier) { + Class tgtType = expr.getType(); + if (tgtType.equals(String.class) && (expr instanceof Literal)) { + return Arrays.asList(expr); + } + else if (tgtType.equals(String.class) && (expr instanceof Function)) { + + Function func = (Function)expr; + if (func.getParameters().get(0) instanceof ColumnReference) { + ColumnReference ref = (ColumnReference)func.getParameters().get(0); + if(Number.class.isAssignableFrom(ref.getType())) { + ArrayList target = new ArrayList(); + target.add("cast("); //$NON-NLS-1$ + target.add(func.getParameters().get(0)); + target.add(" AS varchar(100))"); //$NON-NLS-1$ + } + else { + return modifier.translate(func); + } + } + else { + return modifier.translate(func); + } + } + return Arrays.asList("cast(" , expr, " AS varchar(100))"); //$NON-NLS-1$ //$NON-NLS-2$ + } + + public static class ExtractModifier extends FunctionModifier { private String type; public ExtractModifier(String type) { this.type = type; @@ -369,7 +414,61 @@ } @Override public List<?> translate(Function function) { - return Arrays.asList("cast("+function.getParameters().get(0), " AS "+this.target+")"); //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$ + return Arrays.asList("cast(", function.getParameters().get(0), " AS "+this.target+")"); //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$ } } + + public static class UpperOrLowerModifier extends FunctionModifier { + String funcName; + ConvertModifier convertModifier; + public UpperOrLowerModifier(String name, ConvertModifier converModifier) { + this.funcName = name; + this.convertModifier = converModifier; + } + @Override + public List<?> translate(Function function) { + Expression expr = function.getParameters().get(0); + ArrayList target = new ArrayList(); + target.add(this.funcName); + target.add("("); //$NON-NLS-1$ + target.addAll(expressionToString(expr, this.convertModifier)); + target.add(")"); //$NON-NLS-1$ + return target; + } + } + + public static class LeftOrRightFunctionModifier extends FunctionModifier { + private LanguageFactory langFactory; + ConvertModifier convertModifier; + + public LeftOrRightFunctionModifier(LanguageFactory langFactory, ConvertModifier converModifier) { + this.langFactory = langFactory; + this.convertModifier = converModifier; + } + + @Override + public List<?> translate(Function function) { + List<Expression> args = function.getParameters(); + ArrayList target = new ArrayList(); + if (function.getName().equalsIgnoreCase("left")) { //$NON-NLS-1$ + //substr(string, 1, length) + target.add("substr("); //$NON-NLS-1$ + target.addAll(expressionToString(args.get(0), this.convertModifier)); + target.add(langFactory.createLiteral(Integer.valueOf(1), TypeFacility.RUNTIME_TYPES.INTEGER)); + target.add(args.get(1)); + target.add(")"); //$NON-NLS-1$ + } else if (function.getName().equalsIgnoreCase("right")) { //$NON-NLS-1$ + //substr(case_size, character_length(case_size) -4) + target.add("substr("); //$NON-NLS-1$ + target.addAll(expressionToString(args.get(0), this.convertModifier)); + + target.add(",(character_length("); //$NON-NLS-1$ + target.addAll(expressionToString(args.get(0), this.convertModifier)); + target.add(")-"); //$NON-NLS-1$ + target.add(args.get(1)); + target.add("))"); //$NON-NLS-1$ + } + return target; + } + } } Modified: branches/7.4.x/connectors/translator-jdbc/src/test/java/org/teiid/translator/jdbc/teradata/TestTeradataTranslator.java =================================================================== --- branches/7.4.x/connectors/translator-jdbc/src/test/java/org/teiid/translator/jdbc/teradata/TestTeradataTranslator.java 2011-05-12 03:37:24 UTC (rev 3165) +++ branches/7.4.x/connectors/translator-jdbc/src/test/java/org/teiid/translator/jdbc/teradata/TestTeradataTranslator.java 2011-05-12 14:19:43 UTC (rev 3166) @@ -76,9 +76,28 @@ } @Test public void testTimestampToTime() throws Exception { - helpTest(LANG_FACTORY.createLiteral(new Timestamp(1304604994220L), Timestamp.class), "time", "cast({ts '2011-05-05 09:16:34.22'} AS TIME)"); + helpTest(LANG_FACTORY.createLiteral(new Timestamp(1304604994220L), Timestamp.class), "time", "cast(cast('2011-05-05 09:16:34.22' AS TIMESTAMP(6)) AS TIME)"); } + @Test public void testIntegerToString() throws Exception { + String input = "SELECT lcase(bigdecimalvalue) FROM BQT1.SMALLA"; + String output = "SELECT LOWER(cast(SmallA.BigDecimalValue AS varchar(100))) FROM SmallA"; + TranslationHelper.helpTestVisitor(TranslationHelper.BQT_VDB, input, output, TRANSLATOR); + } + + @Test public void testDateToString() throws Exception { + String input = "SELECT intkey, UPPER(timevalue) AS UPPER FROM BQT1.SmallA ORDER BY intkey"; + String output = "SELECT SmallA.IntKey, UPPER(cast(SmallA.TimeValue AS varchar(100))) AS UPPER FROM SmallA ORDER BY SmallA.IntKey"; + TranslationHelper.helpTestVisitor(TranslationHelper.BQT_VDB, input, output, TRANSLATOR); + } + + @Test public void testLocate() throws Exception { + String input = "SELECT INTKEY, BIGDECIMALVALUE FROM BQT1.SmallA WHERE LOCATE('-', BIGDECIMALVALUE) = 1 ORDER BY intkey"; + String output = "SELECT SmallA.IntKey, SmallA.BigDecimalValue FROM SmallA WHERE position('-' in cast(SmallA.BigDecimalValue AS varchar(100))) = 1 ORDER BY SmallA.IntKey"; + TranslationHelper.helpTestVisitor(TranslationHelper.BQT_VDB, input, output, TRANSLATOR); + } + + @Test public void testByteToString() throws Exception { helpTest(LANG_FACTORY.createLiteral(new Byte((byte)1), Byte.class), "string", "1"); } @@ -158,7 +177,7 @@ @Test public void testRightFunction() throws Exception { String input = "SELECT INTKEY, FLOATNUM FROM BQT1.SmallA WHERE right(FLOATNUM, 2) <> 0 ORDER BY INTKEY"; - String out = "SELECT SmallA.IntKey, SmallA.FloatNum FROM SmallA WHERE SUBSTR(SmallA.FloatNum, (-1 * 2)) <> '0' ORDER BY SmallA.IntKey"; + String out = "SELECT SmallA.IntKey, SmallA.FloatNum FROM SmallA WHERE substr(cast(SmallA.FloatNum AS varchar(100)),(character_length(cast(SmallA.FloatNum AS varchar(100)))-2)) <> '0' ORDER BY SmallA.IntKey"; TranslationHelper.helpTestVisitor(TranslationHelper.BQT_VDB, null, input, out, TRANSLATOR); } }