Firebird's SQL's Substring function not working

Question

I created a view on a machine using the substring function from Firebird, and it worked. When I copied the database to a different machine, the view was broken. This is the way I used it:

SELECT SUBSTRING(field FROM 5 FOR 15) FROM table;

And this is the output on the machine that does not accept the function:

token unknown:  FROM

Both computers have this configuration:

• IB Expert version 2.5.0.42 to run the queries and deal with the database.
• Firebird version 1.5 as server to database.
• BDE Administration version 5.01 installed, with Interbase 4.0 drivers.

Any ideas about why it's behaving differently on these machines?

Answer 1

Different engine versions?

Have you tried naming that expression in the result?

SELECT SUBSTRING(field FROM 5 FOR 15) AS x FROM table;

Answer 2

I tried it, with the same results. In fact, there's no need to use the AS clause, because when I create a view, I define the name of the columns before the SELECT statement:

CREATE VIEW viewName (fieldNameOnView) 
AS SELECT SUBSTRING(field FROM 5 FOR 15) FROM table;

Anyway, IB Expert stops parsing the SQL before reaching the AS clause.

Answer 3

1) Make sure FB engine is 1.5 and there's no Interbase server running on this seme box on the port you expected fb 1.5.

2) Make sure you don't have any UDF called 'substring' registered inside this DB so that FB is especting different params.

Answer 4

Thanks for the answer, Anderson, but I can't try it, because I quit my old job... :o)

But, of course, I'll save your answer for a future need.