Subject Re: [firebird-support] Re: Why a certain expression in 'where' throws an error?
Author Paul Vinkenoog
Hello kokok_kokok,

> ISC ERROR CODE:335544378
> ISC ERROR MESSAGE: feature is not supported
> In the other hand, now I see that the problem is the BETWEEN. If I use
> SELECT * FROM Orders
> CAST(OrderDate + CAST((LfAvgDays+0.5) AS INTEGER) AS DATE) >= '2009/1/1'
> AND CAST(OrderDate + CAST((LfAvgDays+0.5) AS INTEGER) AS DATE) <= '2010/1/1'
> then it works fine. The problem is solved but I am curious about why BETWEEN does not work here.

OK, and you use Dialect 3.

Now, this is very weird. I've taken your 'BETWEEN' version and replaced the column names with constants:

select 1 from rdb$database
where cast(current_date - 400 + cast((10.23+0.5) as integer) as date)
between '2009/1/1' and '2010/1/1'

(Like I said earlier, you don't need all those casts, but I wanted to stay as close as possible to your SQL.)

Can you run the above query and see what happens? I ran it in 1.5 and 2.5 and it should return 1. If you have changed the system time, it may also return an empty dataset, but it should _not_ raise an error.

If this code gives you the same error as before, we can break it up to see what causes it. If not, it must have something to do with your columns. Unless maybe you're using a client tool that does a bit of parsing on its own and changes your query before passing it to the server?

I know you have a working query now, but the problem hasn't really been solved. You'd better find out what's going on here.

Paul Vinkenoog