2 Replies Latest reply on Feb 22, 2011 2:12 PM by philmodjunk

    NUMERIC SQL COMMANDS THAT DON'T WORK

    MikeDever

      Summary

      NUMERIC SQL COMMANDS THAT DON'T WORK

      Product

      FileMaker Pro

      Version

      11.0v3

      Operating system version

      10.6

      Description of the issue

      From your documentation 'FileMaker ODBC and JDBC Guide'  these numeric functions should work.  Most do, this is a list of those that don't.


      SELECT B'1001'  from dual

      com.filemaker.jdbc.FMSQLException: [FileMaker][FileMaker JDBC] FQL0045/(1:7): The value B'1001' is not a valid binary string.


      SELECT LEN('ASDF')  from dual

      com.filemaker.jdbc.FMSQLException: [FileMaker][FileMaker JDBC] FQL0030/(1:7): "LEN" is an invalid function.


      SELECT NUMVAL( '1234567890A' )  from dual

      com.filemaker.jdbc.FMSQLException: [FileMaker][FileMaker JDBC]  (502): Field failed numeric value validation test

      - DOCUMENTATION says this should return: 0



      SELECT PI from dual

      com.filemaker.jdbc.FMSQLException: [FileMaker][FileMaker JDBC] FQL0007/(1:7): The column named "PI" does not exist in any table in the column reference's scope.


      SELECT POW( 9, 3 ) FROM dual

      com.filemaker.jdbc.FMSQLException: [FileMaker][FileMaker JDBC] FQL0030/(1:7): "POW" is an invalid function.



      SELECT VAL('123') FROM dual

      com.filemaker.jdbc.FMSQLException: [FileMaker][FileMaker JDBC] FQL0030/(1:7): "VAL" is an invalid function.

      Steps to reproduce the problem

      It's helpful to create a table with just one Column and one Row.
      A table that mimic's Oracle's Dual table.

      Create a table named Dual
      - 1 field named:   DUMMY
      - type TEXT

      Options:
      Validate: only during data entry
      - Not Empty
      - Unique Value
      - Member of value list:   DualList  'X'
      - Maximum number of characters: 1

      -- Or You might use this sql to create

      CREATE TABLE DUAL
      (
           DUMMY          TEXT   DEFAULT VALUE 'X'
      )