AnsweredAssumed Answered

NUMERIC SQL COMMANDS THAT DON'T WORK

Question asked by MikeDever on Feb 15, 2011
Latest reply on Feb 22, 2011 by philmodjunk

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'
)

Outcomes